Index- Match, Match.

I am having an issue with getting a index Match, Match formula to work for more than one column. The attachment should explaine the problem much better than I can.


Thanks JIm O

It is just coincident that column H looked like it worked, because Big Ten is your search value and that results in 1 (as being the column number) in your second MATCH() function in the formula.. so it was seemingly ok.

The formula you had is used to find a match in a table that is setup with row headers and column headers and you want to find the intersecting point.

For what you have you could use instead, in H3:

=INDEX($D$3:$D$20,MATCH(1,INDEX(($B$3:$B$20=G3)*($C$3:$C$20=H$2),0),0))

copied down

Then copy cell H3 and paste it to J3 and copy down.

Then copy cell H3 or J3 and paste it to L3 and copy down.

This formula creates an array of 1's and 0's via the INDEX(($B$3:$B$20=G3)*($C$3:$C$20=H$2),0) part... so basically it multiplies a range of TRUE/FALSE results for the 1st argument against another array of TRUE/FALSE results for the 2nd argument... and this results in 1's and 0's, then the MATCH(1.. looks for the first 1 in that resultant array and extracts the corresponding item from D3:D20.