Using INDEX with MATCH to find a specific value

  • I'm trying to find a way to find to use 3 references to return a value using INDEX and MATCH, but whenever I'm using a third reference, Excel gives me the #REF! error, i.e., I want do a formula that finds the start time of an employee. First I'm matching name and day like this:


    =INDEX(Sheet!$B$4:$BU$701,MATCH(Name,Sheet!$B$4:$B$701,0),MATCH(Day,Sheet!$B$1:$BU$1,0))


    (this is using one horizontal and one vertical match, and until this point, everything works fine)


    The problem is when I want to add a third MATCH where: MATCH(Shift Start, Sheet!$B$3:$BU$3,0)


    Taking in consideration the raw data displayed is like this:



    [ATTACH=CONFIG]73985[/ATTACH]



    and I want the range to be dynamic depending if what we want to get is the Shift Start or the Break 2 End on a Monday, but using the same formula. Any thoughts?

  • Re: Using INDEX with MATCH to find a specific value


    You would need to insert a "Helper row" that concatenates the Day and Shift Start, then reference that row in your Column parameter of the INDEX() function.


    e.g.


    [COLOR="#0000FF"]=INDEX(Sheet!$B$4:$BU$701,MATCH(Name,Sheet!$B$4:$B$701,0),MATCH([COLOR="#FF0000"]Day&Shift[/COLOR],Sheet!$B$1:$BU$1,0)) [/COLOR]


    where you would have inserted a new Row 1 and B1:BU1 contains the Day and Shift concatenated

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!