How do I LOOKUP text values

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

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.

"Amber C-W" <Amber [email protected]> wrote in message
news:[email protected]...
> 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

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" wrote:

> 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

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


"Barb Reinhardt" wrote:

> 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.
>
> "Amber C-W" <Amber [email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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



"CLR" wrote:

> 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" wrote:
>
> > 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