INDEX>MATCH>MATCH

[Guest]

I need to lookup an intersection within the table array located in Sheet4!
B2:N54. The values are located in Sheet!3 A16:A20 and Sheet3! B16:B20. I'm
attempting to Match from headings in Sheet4! A1:O1 and A1:A54. So I've tried
the following:
=index(SHEET4!$B$2:$N$54,MATCH(SHEET3!B16,SHEET4!$A$1:$O$1,0),MATCH(SHEET3!A16,SHEET4!$A$1:$A$54,0)).

I've enered this into Sheet3!C16:C20. The formula results range from #N/A,
#REF too just plain incorrect, one answer is acctually correct, however this
could just be a fluke. First am I using the correct formula? Second is there
anyway to see what cell the result is pointing to?

Thanks in advance!!

 

[Biff]

Hi!

It looks like you have the Matches reversed. The first Match is for the ROW
and the second Match is for the COLUMN.

Swap those around and see if that works.

Biff

 

[Guest]

and your array of a1 to o1 is not quite the same as b2.n2

 

[daddylonglegs]

..or you could try

=VLOOKUP(SHEET3!A16,SHEET4!$A$2:$N$54,MATCH(SHEET3!B16,SHEET4!$A$1:$N$1,0),0