Formula help - possible vlookup/hlookup combination with IF or oth

[adam&ellie]

HI,

I'm trying to work out a formula that will bring back data from a table as
below

1 2 3 4 5
60 20 10 6 40 5
61 1 25 70 45 12
62 7 70 20 52 10

I need a formula that would compare the x & y axis of the table when given
the values and report back the associated cross value. I was thinking along
the lines of a vlookup and h lookup in combination with an IF statement
somehow. Or is there a specific formula within excel?

Many thanks

 

[Pete_UK]

With your example table in cells A1:F4, and using A10 for the row
values (60 to 62) and B10 for the column values (1 to 5), put this
formula in C10:

=INDEX(B2:F4,MATCH(A10,A2:A4,0),MATCH(B10,B1:F1,0))

Just change the values in A10 and/or B10 to get the appropriate value
in C10.

Hope this helps.

Pete

 

[Jarek Kujawa]

presuming yr data is in A1:F4

put e.g. 61 in A8 and 3 in B8

try:

=OFFSET($A$1;MATCH(A8,$A$2:$A$4,),MATCH(B8,$B$1:$F$1,))

the result should be 70

HIH

 

[Arvi Laanemets]

Hi

=INDEX($B$2:$F$3,MATCH(X1,$A$2:$A$3,0),MATCH(Y1,$B$1:$F$1,0))

, where X1 and Y1 contain search values for row- and column headers.