Home |
Search |
Today's Posts |
#1
|
|||
|
|||
lookup
sheet 1
type in to sheet1 d6 (15gr) refer to sheet2 and result in f8 sheet1 to be 3.5 or type in 10gr into d6 sheet 1 and result in f8 sheet 1 to be 2.7 Sheet2 A B 10GR 2.7 15GR 3.5 20GR 5.6 |
#2
|
|||
|
|||
In F8:
vlookup(D6,Sheet2!$A$1:$b$3,2,false) "paulh" wrote: sheet 1 type in to sheet1 d6 (15gr) refer to sheet2 and result in f8 sheet1 to be 3.5 or type in 10gr into d6 sheet 1 and result in f8 sheet 1 to be 2.7 Sheet2 A B 10GR 2.7 15GR 3.5 20GR 5.6 |
#3
|
|||
|
|||
Sheet2 houses the reference table in cols A & B as indicated, assumed from
row1 In Sheet1, Put in F8: =IF(ISNA(MATCH(D6,Sheet2!A:A,0)),"",VLOOKUP(TRIM(D 6),Sheet2!A:B,2,0)) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "paulh" wrote in message ... sheet 1 type in to sheet1 d6 (15gr) refer to sheet2 and result in f8 sheet1 to be 3.5 or type in 10gr into d6 sheet 1 and result in f8 sheet 1 to be 2.7 Sheet2 A B 10GR 2.7 15GR 3.5 20GR 5.6 |
#4
|
|||
|
|||
Sorry, there should be quotations around Sheet2, i.e.
=vlookup(D6,'Sheet2'!$A$1:$B$3,2,false) "Melissa" wrote: In F8: vlookup(D6,Sheet2!$A$1:$b$3,2,false) "paulh" wrote: sheet 1 type in to sheet1 d6 (15gr) refer to sheet2 and result in f8 sheet1 to be 3.5 or type in 10gr into d6 sheet 1 and result in f8 sheet 1 to be 2.7 Sheet2 A B 10GR 2.7 15GR 3.5 20GR 5.6 |
#5
|
|||
|
|||
If you're "Trimming" the lookup shouldn't you do the same for the match ?
Biff "Max" wrote in message ... Sheet2 houses the reference table in cols A & B as indicated, assumed from row1 In Sheet1, Put in F8: =IF(ISNA(MATCH(D6,Sheet2!A:A,0)),"",VLOOKUP(TRIM(D 6),Sheet2!A:B,2,0)) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "paulh" wrote in message ... sheet 1 type in to sheet1 d6 (15gr) refer to sheet2 and result in f8 sheet1 to be 3.5 or type in 10gr into d6 sheet 1 and result in f8 sheet 1 to be 2.7 Sheet2 A B 10GR 2.7 15GR 3.5 20GR 5.6 |
#6
|
|||
|
|||
"Biff" wrote
If you're "Trimming" the lookup shouldn't you do the same for the match ? Yes, of course, for consistency of treatment. Thanks for the correction. To the OP: In Sheet1, Put instead in F8: =IF(ISNA(MATCH(TRIM(D6),Sheet2!A:A,0)),"",VLOOKUP( TRIM(D6),Sheet2!A:B,2,0)) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#7
|
|||
|
|||
Hi!
the quotes are only needed if the sheet name contains a space: ='Sheet 2'!A1 OR, if you are including the file name and file path: ='C:\mydir\[myfile.xla]sheet2'!A1 Biff "Melissa" wrote in message ... Sorry, there should be quotations around Sheet2, i.e. =vlookup(D6,'Sheet2'!$A$1:$B$3,2,false) "Melissa" wrote: In F8: vlookup(D6,Sheet2!$A$1:$b$3,2,false) "paulh" wrote: sheet 1 type in to sheet1 d6 (15gr) refer to sheet2 and result in f8 sheet1 to be 3.5 or type in 10gr into d6 sheet 1 and result in f8 sheet 1 to be 2.7 Sheet2 A B 10GR 2.7 15GR 3.5 20GR 5.6 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |