Exact Match in Vlookup

  • Thread starter Thread starter Mark Allen
  • Start date Start date
M

Mark Allen

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
 
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
 
you need a 0 or false at the end
=VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2,FALSE)
 
Mike that worked great but now how do I get it to return a blank cell instead
of #N/A ??

Regards

Mark

Mike H said:
Try this

=VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2,FALSE)

Mike

Mark Allen said:
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
Click to expand...
Click to expand...
 
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 said:
Mike that worked great but now how do I get it to return a blank cell instead
of #N/A ??

Regards

Mark

Mike H said:
Try this

=VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2,FALSE)

Mike

Mark Allen said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top