Cross Reference Formula

[mjsam]

I need to find a formula that first looks across the top row to find a number
and then down the first column and results with the intersecting cell.
1 2 3 4 5
a 3 6 7 8 4
b 2 5 7 5 6

Excmple: when i reference 3-b, I need the result to be 7. THis is HLookup
and V lookup, but how do i combine these two formulas? HELP!

 

[Luke M]

You can combine the INDEX function with MATCH functions
=INDEX(B2:F3,MATCH("b",A2:A4,0),MATCH(3,B1:E1,0))

 

[Pete_UK]

You can do this with an INDEX/MATCH combination. Assume your table is
in A1:F3, and that you put 3b in H1, then put this formula in H2:

=INDEX(B2:F3,MATCH(RIGHT(H1),A2:A3,0),MATCH(--LEFT(H1),B1:F1,0))

and it should return 7.

Hope this helps.

Pete