Need Help with Double Vlookup

[The Moose]

I just can't get my brain around this one:

I have a huge products listings file (over 4,000) products.

In separate columns on the main products worksheet, I have this
formula:

=ISNUMBER(SEARCH($X$1,$M2))

(What this does is look in the product name colum and search for a
specific word (or words) and enters either a TRUE or FALSE. I'm doing
it this way because you can't search beyond character 250(??) in a text
field.)

I've got a categories worksheet in the same workbook that has two
columns:
~keywords~ ~associated category to assign product
to~

(These two columns are named individually and together. First column =
shortcategory. Second column = categories. Both columns together =
categories2

OK -- this is what I want to do:

(1) check the current row in columns W:AH and locate the word TRUE.
When I find the word TRUE, I want to pick up the column heading and
locate that word on the categories page and assign the appropriate
category to that product row.

--can't figure out how to do the first part-- (basically these are 12
true/false columns with headings -- how do I pick up the column
heading??)

=VLOOKUP(. ^..,categories2, 2)


(2) If #1 does not have an associated product category, I want to check
to see if the product is NEW which can be done with a formula.

and

(3) If #1 and #2 do not obtain results, I want to pick the keyword that
the manufacturer assigned to the product and use that keyword to assign
a category from the categories worksheet:

=IF(LEN(N2)=0,"",IF(ISERR(FIND(",",N2)),N2,LEFT(N2,SEARCH(",",N2,1)-1)))

(This will pull the manufacturer-assigned keyword so that I can VLOOKUP
on categories worksheet. Right now, I have this keyword extracted into
column J -- so it's easy enough to up the word.)

=IF(J2="*new*","New Arrivals",VLOOKUP(J2,categories2,2))

What I'm having trouble with is how to express the first part of #1 and
how to put this whole thing together.

I'm thinking it should probably be something like this:

=IF(J2>="",VLOOKUP("HOW DO I PICK UP THIS WORD IN COLUMN
HEADING??",categories2,2),IF(J2="*new*","New
Arrivals",VLOOKUP(J2,categories2,2)))

This works if I manually type the column heading from #1 above between
the second set of quotes.

Thanks.

Barb

 

[Guest]

Have you looked at the FIND function to find the column heading you're
looking for?

 

[The Moose]

I finally got it to work. THIS ONE WORKS. (Finds 'special categories'
to the right of the main spreadsheet with TRUE condition and then looks
^up^ to get the column heading to use as the keyword to locate the
title on the categories worksheet. If not found, next looks for NEW;
if not found uses manufacturer's category.)

=IF(ISNA(VLOOKUP(OFFSET($W$1,0,MATCH(TRUE,W2:BU2,0)-1),categories2,2)=TRUE),IF(I2="*new*","New
Arrivals",VLOOKUP(I2,categories2,2)),VLOOKUP(OFFSET($W$1,0,MATCH(TRUE,W2:BU2,0)-1),categories2,2))

I'm still having ONE small problem. If one of the 'special categories'
column to the right of the products table contains text that is also
inside another word, it will categorize according to that word. For
example, one the words that I am want to have a separate category for
is 'deer' (as in animal) -- this formula also categorizes 'John Deere'
into the same category. I've found that if I put a space after 'deer',
I can prevent that.

The other one that I've noticed is there is a product called "Flower
Pot Angel something-or-other" -- I want these into a separate category
called 'Flower Pot'.

There is one other product called "Fairy Sunflower Pot
something-or-other" -- the formula also tries to categorize this into
the category 'Flower Pot'.

Anyone got any ideas on how to ensure that only EXACT matches from the
column headings are used as keywords to lookup the categories??

Thanks.

Oh, David, I can't just do a FIND, because what I'm looking is the word
TRUE and then I need to pick up the column heading that the word TRUE
appears in.

Thanks for you help. Your answer DID help me to clarify what I needed
to do. I was able to get to the resolution that I have so far because
of your input. Thanks again.

Barb

 

[Guest]

Change your TRUE to FALSE in your statement to get an exact match.

 

[The Moose]

Thanks, Christine. I have to keep the "true" because that is the value
that I'm looking for.

I think my problem is stemming from the equation prior to the one I
just posted.

This is what I've got:

column/row $L1301 = Pineapple salt and pepper shakers


column $X$1 = apple
the entire "X" column has this formula down the 4000 rows:
=ISNUMBER(SEARCH($X$1,$L1301)) = true <<-->> this should actually be
"FALSE" instead of true because "apple" does not match "pineapple". I
can't figure out how to fix this.


The cell that assigns the category has this formula:

=IF(ISNA(VLOOKUP(OFFSET($W$1,0,MATCH(TRUE,W1301:CG1301,0)-1),categories2,2)=TRUE),IF(I1301="*new*","New
Arrivals",VLOOKUP(I1301,categories2,2)),VLOOKUP(OFFSET($W$1,0,MATCH(TRUE,W1301:CG1301,0)-1),categories2,2))


results = Kitchen>Theme Decorating>Apples <<-->> shouldn't be
should be = Kitchen>Salt and Pepper Shakers


Thanks.

Barb