+ Reply to Thread
Results 1 to 3 of 3

Index/Match Return Next Column to right?

  1. #1
    Registered User
    Join Date
    01-18-2009
    Location
    Baltimore
    MS-Off Ver
    Excel 2010
    Posts
    16

    Index/Match Return Next Column to right?

    I have a list of baseball players and their salaries. The salaries are not listed per calendar year, but rather per season of the contract, ie, the current year would be "year0", next year would be "year1", etc...

    I'm trying to get index/match to find a "Player A, Year 0" and then return the data in the column to the immediate left, to give me "Year 1"

    The problem is, the contract that is being located is located anyone in a range from "players_contract!L8:S1022"

    So far, I can find the year that the player's contract starts, the find the correct year 0 salary, but don't know how to turn that into year 1, year 2, etc...

    Attached is the data. The number that should be located in the cell has been entered in RED.

    Thanks!
    salaries.xlsx

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Index/Match Return Next Column to right?

    Try this non-array solution

    In E7
    =IF(E$6<$C7,"",INDEX(players_contract!$L$8:$S$1022,MATCH($A7,players_contract!$A$8:$A$1022,0),E$6-$C7+1))

    Copy down and across
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    01-18-2009
    Location
    Baltimore
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Index/Match Return Next Column to right?

    perfect. thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to Index and match to return the last value from variable column
    By abshmo5 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-27-2013, 04:59 AM
  2. Return Column Header from Index/Match function
    By del24ie in forum Excel General
    Replies: 2
    Last Post: 01-12-2012, 11:57 AM
  3. Replies: 2
    Last Post: 01-12-2012, 07:02 AM
  4. Index/Match Function to Return column header
    By djmarsh51 in forum Excel General
    Replies: 2
    Last Post: 05-07-2010, 02:10 PM
  5. Replies: 2
    Last Post: 02-27-2010, 11:17 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1