Vlookup & exact match

M

Munchkin

In my worksheet/form a user enters a policy number in B5 to search for
details.
B6 holds the vlookup formula which displays their info.

But if a non-existing policy is entered in B5, B6 shows the next closest
match. How do I force an exact match & give some sort of error message when
an invalid # has been entered ? Thanks!

=VLOOKUP(B5,List2,2)
 
G

Glenn

Munchkin said:
In my worksheet/form a user enters a policy number in B5 to search for
details.
B6 holds the vlookup formula which displays their info.

But if a non-existing policy is entered in B5, B6 shows the next closest
match. How do I force an exact match & give some sort of error message when
an invalid # has been entered ? Thanks!

=VLOOKUP(B5,List2,2)

Check the help file under VLOOKUP.


Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Range_lookup is a logical value that specifies whether you want VLOOKUP to find
an exact match or an approximate match. If TRUE or omitted, an approximate match
is returned. In other words, if an exact match is not found, the next largest
value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an
exact match. If one is not found, the error value #N/A is returned.
 
M

Munchkin

OK - I got the "False" part, which is great. But I still want some kind of
error message to pop up to tell the user that the policy number they entered
was not found. "False" automatically enters the "#N/A" error message. Any
ideas?
 
S

Sheikh Saadi

Munchkin,

try using this,

=IF(ISERROR(VLOOKUP(C8,A8:A12,1,FALSE))=TRUE,"No
Match",VLOOKUP(C8,A8:A12,1,FALSE))


Value list is from A8 to A12 and you are matching values in C8.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top