If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|