Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exact Match in Vlookup
I am using this formula in a vlookup:
=VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2) Hoever I need an exact match, sometimes it returns the close match. Any ideas. Thanks in advance Mark |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exact Match in Vlookup
Try this
=VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2,FALSE) Mike "Mark Allen" wrote: I am using this formula in a vlookup: =VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2) Hoever I need an exact match, sometimes it returns the close match. Any ideas. Thanks in advance Mark |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exact Match in Vlookup
You need to use a fourth argument FALSE in your formula.
=VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2, false) This is what Help has to say: range_lookup Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match: a.. If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. Important If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value. For more information, see Sort data. If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted. b.. If the -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mark Allen" wrote in message ... I am using this formula in a vlookup: =VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2) Hoever I need an exact match, sometimes it returns the close match. Any ideas. Thanks in advance Mark |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exact Match in Vlookup
you need a 0 or false at the end
=VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2,FALSE) -- -John Please rate when your question is answered to help us and others know what is helpful. "Mark Allen" wrote: I am using this formula in a vlookup: =VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2) Hoever I need an exact match, sometimes it returns the close match. Any ideas. Thanks in advance Mark |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exact Match in Vlookup
Mike that worked great but now how do I get it to return a blank cell instead
of #N/A ?? Regards Mark "Mike H" wrote: Try this =VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2,FALSE) Mike "Mark Allen" wrote: I am using this formula in a vlookup: =VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2) Hoever I need an exact match, sometimes it returns the close match. Any ideas. Thanks in advance Mark |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exact Match in Vlookup
Hi,
test for it with an if statement =IF(ISNA(VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2,FALSE)),"",VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2,FALSE)) Miek "Mark Allen" wrote: Mike that worked great but now how do I get it to return a blank cell instead of #N/A ?? Regards Mark "Mike H" wrote: Try this =VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2,FALSE) Mike "Mark Allen" wrote: I am using this formula in a vlookup: =VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2) Hoever I need an exact match, sometimes it returns the close match. Any ideas. Thanks in advance Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup exact match | Excel Discussion (Misc queries) | |||
Vlookup with an 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 |