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