problem with vlookup

[lynn]

Having a strange problem with a Vlookup. My function is
set to find the closest (True) instead of exact. It's
working okay, till halfway through the file. Halfway,
instead of giving me the closest match, it's giving me the
prior match.

Example -

Sheet 1 as a columnA containing Horse, Mouse, Strong Cat,
Strong Dog. ColumnB is a product code. Sheet 2 has a
columnA of the same categories (Horse, Mouse, Strong Cat,
Strong Dog.) I need ColumnB to lookup the product code
from Sheet 1, with the function set to True (there is more
to the categories, so I can't look for an exact match).
Works fine at first. If I then go to Strong Cat in sheet 2
and delete the word CAT, it will give me a product code
for Mouse. It's not matching on the word "strong".

I hope this makes sense. If not, I can send the file to
whomever wants to help me with this. just reply back with
an email address

 

[Bob Phillips]

Lynn,

That is because 'Strong' is less than 'Strong Cat' when ordered, so the
value less than 'Strong Cat' returns less than, that is Mouse.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Peo Sjoblom]

Closest in Vlookup means numeric values, not text..
Are your values numeric and are they sorted in ascending order?

 

[Leo Heuser]

Lynn

It works OK. The nearest value *below*
"Strong" *is* "Mouse", i.e. "Strong Cat"
is "larger" than "Strong".

VLOOKUP doesn't find the *closest* match,
but the largest value, which is smaller than
"Strong".

 

[Niek Otten]

Hi Lynn,

The fourth argument does not really mean "closest". This is what my HELP
(ExcelXP) says:

<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.>

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[lynn]

Thanks for all of your answers. So since I am dealing with
Text and not values, perhaps I should be using a differnt
function ? Suggestions ?

 

[Bob Phillips]

Lynn,

Don't think so, it is working as I would expect, and another function would
work similarly.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

...
...

Example -

Sheet 1 as a columnA containing Horse, Mouse, Strong Cat,
Strong Dog. ColumnB is a product code. Sheet 2 has a
columnA of the same categories (Horse, Mouse, Strong Cat,
Strong Dog.) I need ColumnB to lookup the product code
from Sheet 1, with the function set to True (there is more
to the categories, so I can't look for an exact match).
Works fine at first. If I then go to Strong Cat in sheet 2
and delete the word CAT, it will give me a product code
for Mouse. It's not matching on the word "strong".

...

Others have explained the problem. What you want is the *closest* match, whether
'greater' or 'less' than your search value. If you were using numbers, you'd do
this using

=INDEX(Table,MATCH(MIN(ABS(INDEX(Table,0,1)-SearchValue)),INDEX(Table,0,1),0),
DesiredColumnWithinTable)

Unfortunately, text isn't as easy to work with as numbers. You'd need some sort
of text scoring to generate numeric values for 'closeness' between your search
value and the text values in the first column of your table. You'd then find the
highest text score.

Several text scoring user-defined functions (udfs) have appeared in this
newsgroup in the past. Here's a link to a thread containing one of them as well
as links to articles with others.

http://google.com/[email protected]

However, note that what you want to do requires programming.