lOOKUP

[Stuart Carnachan]

I WOULD LIKE TO CROSS REFERENCE A TABLE AS BELOW TO PICK OUT THE VALUE
in A3 if i input "Training" in A1 and grade "3" in A2.Conversley if i change
to driver and grade it will pick up the cross reference value


GRADE 1 2 3 4 5 6
Training 60 65 75 80 90
95
Driver 65 75 80 85 95
100

Stuart

 

[Sloth]

=VLOOKUP(A1,C2:I3,A2+1,0)

this assumes the table is in C1:I3 (I put "GRADE" in cell C1), so replace
"C2:I3" with the location of your table.

 

[Stuart Carnachan]

Cheers that works but what is the methodology behind it ?

 

[Sloth]

VLOOKUP requires the column of the table. Your table conveniantly has the
column numbers offset by 1. If you had random numbers, or text, for the
column headings, it would require a different formula.

 

[Stuart Carnachan]

Cheers - it came to me all of a sudden. What would you do if it was random or
text ?

 

[Sloth]

=INDIRECT("R"&MATCH(A2,C1:C3,0)&"C"&MATCH(A1,C1:I1,0)+2,0)
or
=INDEX(C1:I3,MATCH(A1,C1:C3,0),MATCH(A2,C1:I1,0))

This uses a table like this (with GRADE in C1)
GRADE one two three four five six
Training 60 65 75 80 90 95
Driver 65 75 80 85 95 100

"Training" in A1, and "three" in A2 will yield 75.

the MATCH function finds the relative place of a item in a list, and the
INDIRECT function is for accessing a reference as text. INDEX is also
usefull, but for some reason I don't use it as much. Both formulas have the
same end result.