Matching Text

[Guest]

it seems like a simple problem, but I can't find an answer anywhere.

Worksheet One
Column A Column B
Apples Little Apples
Apples Green Apples
Apples Big Apples
Pears Big Pears
Pears Little Pears
Bannanas Green Banannas

Work Sheet Two:
Column A Column B
Little Apples
Green Bannannas
Big Apples
Big Apples
Green Bannans
etc


I need a formula in WorkSheet 2 of Column A to return the appropriate
category based on the text value in Colum B.

Thanks very much for taking a look.

Steve

 

[SteveG]

In worksheet 2 if your values are in B1:B6 and your values to match are
in worksheet1!A1:B6 then in worksheet 2 A1,

=INDEX(Worksheet1!$A$1:$B$6,MATCH(Worksheet2!B1,Worksheet1!$B$1:$B$6,0),1)

Copy down your items to match.

HTH

Steve

 

[Guest]

SteveG, thanks:

Sometimes the formula works, other times I get #N/A errors when I use this
formula. I don't see any mispellings or other mismatches. Do you think it
would help to apply a similar formula just by matching the first few text
characters?

At the end of the formula I've tried changing the 0 to 1, but that doesn't
return the right value.

I believe I copied the formula correctly: this is what it looks like in my
worksheet now:

=INDEX(AllCos!$A$4:$B$140,MATCH(C8,AllCos!$B$4:$B$140,0),1)

Worksheet2 = AllCos
Worksheet1 is referenced by C8

Thanks very much.

 

[SteveG]

You probably have spaces at the end of some of your values. If you'd
rather lookup and match by the first say 8 characters you could use
this array formula.

=INDEX(Sheet1!$A$1:$A$6,MATCH(LEFT(Sheet2!B1,8),LEFT(Sheet1!$B$1:$B$6,8),0),1)

After typing the formula, hit Ctrl-Shift-Enter simultaneously. This
will create the array formula which will then appear as:

{=INDEX(Sheet1!$A$1:$A$6,MATCH(LEFT(Sheet2!B1,8),LEFT(Sheet1!$B$1:$B$6,8),0),1)}

Don't type the {} in yourself.

One suggestion is that you pick enough characters to ensure a match for
all possibilities. I chose 8 because it compared the entire first word
and at least the first letter of the second word for a match. For
instance, if you had Little Apples and Little Avacados, 8 would return
the first match, if you used 9, it would ensure it was looking at
"Little Av" or "Little Ap".

Array formulas can be a pain if you have a lot of users that are not
familiar with them using your workbook. You have to remember to always
commit with Ctrl-Shift-Enter.

I am working on incorporating the TRIM function in my original post
which may be easier but for now, if you are comfortable with the above,
it worked fine for me using your sample data.

HTH

Steve

 

[SteveG]

Here you go. This will work.


=INDEX(TRIM(Sheet1!$A$1:$A$6),MATCH(TRIM(Sheet2!B1),TRIM(Sheet1!$B$1:$B$6),0),1)

This also an array formula. Commit with Ctrl-Shift-Enter.

HTH
Steve

 

[Guest]

Super! Thanks very much. I really appreciate it.