lookup

P

paulh

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
 
M

Max

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(D6),Sheet2!A:B,2,0))
 
M

Melissa

Sorry, there should be quotations around Sheet2, i.e.
=vlookup(D6,'Sheet2'!$A$1:$B$3,2,false)
 
M

Max

If you're "Trimming" the lookup
shouldn't you do the same for the match ?
Click to expand...

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))
 
B

Biff

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
 
Top