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

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

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

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

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

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