Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with Double Vlookup
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add excel horizontal & vertical ruler | Excel Worksheet Functions | |||
Parse csv files | Excel Discussion (Misc queries) | |||
dynamic, double vlookup, match, index, dget?? different workbooks | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |