Exact Match in Vlookup

[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

 

[Mike H]

Try this

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

Mike

 

[Bernard Liengme]

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

 

[John Bundy]

you need a 0 or false at the end
=VLOOKUP(B4,'[GT Master Quote Tool.xls]Sheet1'!$A$2:$E$10000,2,FALSE)

 

[Mark Allen]

Mike that worked great but now how do I get it to return a blank cell instead
of #N/A ??

Regards

Mark

Try this

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

Mike

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

 

[Mike H]

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

Mike that worked great but now how do I get it to return a blank cell instead
of #N/A ??

Regards

Mark

Try this

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

Mike

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