vlookup not precise enough

D

davegb

When using a lookup function, if a value not in the table is entered,
the lookup function "interpolates" and gets the next appropriate
value. I.e., if I have set up my table so that "apple" returns "1" and
"orange" returns "2", and I enter "banana", it will return "2", the
next value in the table. Is there any way, other than VBA, to change
it so it only returns values actually in the lookup column/row?

Thanks in advance!
 
N

Niek Otten

Set the fourth argument (which you probably omitted) to FALSE, like:

=VLOOKUP(A1,B1:C1000,2,FALSE)


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| When using a lookup function, if a value not in the table is entered,
| the lookup function "interpolates" and gets the next appropriate
| value. I.e., if I have set up my table so that "apple" returns "1" and
| "orange" returns "2", and I enter "banana", it will return "2", the
| next value in the table. Is there any way, other than VBA, to change
| it so it only returns values actually in the lookup column/row?
|
| Thanks in advance!
|
 
D

Dave Peterson

=vlookup() has 4 parms. If you specify False or 0 as the last parm, excel will
find the first exact match--if there is no match, you'll see an #N/A error.

=vlookup(a2,sheet2!a:b,2,false)
or
=if(isna(vlookup(a2,sheet2!a:b,2,false)),"No match",
vlookup(a2,sheet2!a:b,2,false))

Debra Dalgleish has lots of notes on =vlookup() here:
http://www.contextures.com/xlFunctions02.html
 
G

Gord Dibben

Use the fourth argument of FALSE so #N/A is returned if not a match.

=VLOOKU(cellref,Table,colindex,FALSE)

To deal with the #N/A error add this

=IF(ISNA(yourformula),"",(yourformula)

e.g.

=IF(ISNA(VLOOKUP(H9,$C$2:$F$35,3,FALSE)),"",VLOOKUP(H9,$C$2:$F$35,3,FALSE))


Gord Dibben MS Excel MVP
 
D

davegb

Set the fourth argument (which you probably omitted) to FALSE, like:

=VLOOKUP(A1,B1:C1000,2,FALSE)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| When using a lookup function, if a value not in the table is entered,
| the lookup function "interpolates" and gets the next appropriate
| value. I.e., if I have set up my table so that "apple" returns "1" and
| "orange" returns "2", and I enter "banana", it will return "2", the
| next value in the table. Is there any way, other than VBA, to change
| it so it only returns values actually in the lookup column/row?
|
| Thanks in advance!
|
Click to expand...

Thanks to everybody. Worked like a charm!
 
Top