vlookup not precise enough

  • Thread starter Thread starter davegb
  • Start date Start date
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!
 
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!
|
 
=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
 
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
 
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!
 
Back
Top