# 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?

## Images

• 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!