vlookup not precise enough

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

davegb - 17 Oct 2007 21:30 GMT

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!

Reply to this Message

Niek Otten - 17 Oct 2007 21:43 GMT

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

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

Signature

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
[quoted text clipped - 4 lines]
|
| Thanks in advance!

Reply to this Message

davegb - 23 Oct 2007 14:49 GMT

> Set the fourth argument (which you probably omitted) to FALSE, like:
>
[quoted text clipped - 14 lines]
> |
> | Thanks in advance!

Thanks to everybody. Worked like a charm!

Reply to this Message

Dave Peterson - 17 Oct 2007 21:48 GMT

=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

> When using a lookup function, if a value not in the table is entered,
> the lookup function "interpolates" and gets the next appropriate
[quoted text clipped - 4 lines]
>
> Thanks in advance!

Signature

Dave Peterson

Reply to this Message

Gord Dibben - 17 Oct 2007 21:55 GMT

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

>When using a lookup function, if a value not in the table is entered,
>the lookup function "interpolates" and gets the next appropriate
[quoted text clipped - 4 lines]
>
>Thanks in advance!

Reply to this Message

Rate this thread: