How do I LOOKUP text values

[Amber C-W]

Hi,

I would like to lookup text values in a worksheet, but is it possible to
lookup a non-exact match with a text value?

E.g Lookup values attached to 'Apple' against value 'Apples'. For a lookup
or match to work, do they have to be exact, or is there an exception rule i
can use that will pick out similar?

Basically, I have 2 tables, one table on shape of fruit, containing a row of
details on 'Apple' and the other table, colur of fruit, containing a row of
details for 'Apples'.

i want to position the two rows onto the one row, because i know that
'Apple' and 'Apples' is the same thing, with the intention of collating all
details about apples onto one row. But the VLOOKUP will only lookup exact
values??

i have about 2000 rows, hence the need to automate this 'matching' process

If this can not be done, does anyone have any better ideas?

Many thanks in advance

 

[Barb Reinhardt]

Well, if the only difference between the cells is an "s" at the end, you
could use something like this

=VLOOKUP(A1&"s",Sheet2!A1:B1,2,FALSE)

Alternatively, you could add a helper column to parse out the first
characters of the word (apples) to match a parsed version of what you are
trying to match.

 

[CLR]

You can look for "apples" and it will return "apple" by having your Lookup
Table alphabetized and using the "TRUE" option.......but it don't work in the
reverse...

Vaya con Dios,
Chuck, CABGx3

 

[Amber C-W]

What about if i have e.g.

A T & T Bloggs and i want to match with AT & Bloggs Ltd or
ADAM co to match with Adam Ltd co.

VLookup i know is very sensitive to spaces and characters. i have a feeling
there is no way around because each row is unique and the inconsistancies
between the match are unique to the instance. So some rows may match better
or closer than others.

tricky one i think, but not convinced that its not possible.

thanks for your help.
Amber C-W

 

[CLR]

You can also use something like this to look up "unknowns".....
ie: match the first 5 characters and then anything less than "z"


=IF(A1="","",VLOOKUP(LEFT(A1,5)&"z",H:H,1,TRUE))


Vaya con Dios,
Chuck, CABGx3