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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with Double Vlookup
Have you looked at the FIND function to find the column heading you're
looking for? -- Brevity is the soul of wit. "The Moose" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with Double Vlookup
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(OFFSE T($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 Dave F wrote: Have you looked at the FIND function to find the column heading you're looking for? -- Brevity is the soul of wit. "The Moose" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with Double Vlookup
Change your TRUE to FALSE in your statement to get an exact match.
-- ~Christine, CPA "The Moose" wrote: 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(OFFSE T($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 Dave F wrote: Have you looked at the FIND function to find the column heading you're looking for? -- Brevity is the soul of wit. "The Moose" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help with Double Vlookup
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:CG 1301,0)-1),categories2,2)=TRUE),IF(I1301="*new*","New Arrivals",VLOOKUP(I1301,categories2,2)),VLOOKUP(OF FSET($W$1,0,MATCH(TRUE,W1301:CG1301,0)-1),categories2,2)) results = KitchenTheme DecoratingApples <<-- shouldn't be should be = KitchenSalt and Pepper Shakers Thanks. Barb Christine wrote: Change your TRUE to FALSE in your statement to get an exact match. -- ~Christine, CPA "The Moose" wrote: 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(OFFSE T($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 Dave F wrote: Have you looked at the FIND function to find the column heading you're looking for? -- Brevity is the soul of wit. "The Moose" wrote: 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 |
Reply |
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 |