Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lOOKUP
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
lOOKUP
=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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
lOOKUP
Cheers that works but what is the methodology behind it ?
-- Stuart "Sloth" wrote: =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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
lOOKUP
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" wrote: Cheers that works but what is the methodology behind it ? -- Stuart "Sloth" wrote: =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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
lOOKUP
Cheers - it came to me all of a sudden. What would you do if it was random or
text ? -- Stuart "Sloth" wrote: 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" wrote: Cheers that works but what is the methodology behind it ? -- Stuart "Sloth" wrote: =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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
lOOKUP
=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. "Stuart Carnachan" wrote: Cheers - it came to me all of a sudden. What would you do if it was random or text ? -- Stuart "Sloth" wrote: 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" wrote: Cheers that works but what is the methodology behind it ? -- Stuart "Sloth" wrote: =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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP a text string created from IF statement | Excel Discussion (Misc queries) | |||
Another way to lookup data | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |