vlookup - not exact matches

[todd g.]

I have a list of food items from a customer (i.e. green
beens, frozen vegetables, sliced apples)and I want to get
the closest match (because they will not match exactly)on
another sheet and bring back the second column's data,
which is a 5-7 digit number. I have it sort of working,
it's bringing back numbers but they're not a close match
at all. I'm not sure what I'm doing wrong. It will bring
back apples, for coffee, and it's not 1 line off either.
Thanks for any help

 

[Sabre]

Well, may not be quite what you're after, but if you can accept a database
(Cols A and B in this example) with all the possible names (aliases) for
each item, you can use a double-lookup like so ...
=VLOOKUP(VLOOKUP(G1,A:B,2,FALSE),D:E,2,FALSE) - formula in Col H. I have
aliases in Col A and corresponding real names in B. And in D I have real
names with attached 5-7 digit numbers in E.
In Col G I stick the list of food items from customer. Col H returns the
correct 5-7 digit numbers.

I hope this helps.
Graeme

 

[Ken Wright]

Try something along the lines of the following syntax.

Assumes your data to be looked up is in A1, and that you are happy with any word that contains
that word

=VLOOKUP("*"&A1&"*",D1:F20,2,FALSE)