Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup & exact match
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup & exact match
=VLOOKUP(B5,List2,2,FALSE)
-- Kind regards, Niek Otten Microsoft MVP - Excel "Munchkin" wrote in message ... 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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup & exact match
Munchkin wrote:
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,ran ge_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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup & exact match
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? "Munchkin" wrote: 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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup & exact match
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. -- Sheikh Saadi "Munchkin" wrote: 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exact Match in Vlookup | Excel Discussion (Misc queries) | |||
vlookup exact match | Excel Discussion (Misc queries) | |||
VLookup - Not Exact Match | Excel Worksheet Functions | |||
Using Exact() with VLookup or Match | Excel Worksheet Functions | |||
vlookup more than one exact match | Excel Worksheet Functions |