Selecting a Keyword
Hello guy i am trying to come up with a formula but i have no idea how i need
some help from the experts. I have two columns, 1 contains information and the other one is empty where the formula is going to be called "product type" So what i need is: If column A contains the word: Apple, strawberry, banana. Column B should be = Fruits If column A contains: tomatoe, carrot, onion B = Vegetable If columb A contains anything other than: Apple, strawberry, banana,tomatoe, carrot, onion. B= Beef Is it possible to create this formula? Thanks |
Selecting a Keyword
One way...
Create a 2 column lookup table like this: apple...fruit strawberry...fruit banana...fruit tomatoe...vegetable carrot...vegetable onion...vegetable Assume that table is in the range D1:E6 Then use this formula in column B: =IF(COUNTIF(D$1:D$6,A1),VLOOKUP(A1,D$1:E$6,2,0),IF (A1<"","Beef","")) -- Biff Microsoft Excel MVP "Gmata" wrote in message ... Hello guy i am trying to come up with a formula but i have no idea how i need some help from the experts. I have two columns, 1 contains information and the other one is empty where the formula is going to be called "product type" So what i need is: If column A contains the word: Apple, strawberry, banana. Column B should be = Fruits If column A contains: tomatoe, carrot, onion B = Vegetable If columb A contains anything other than: Apple, strawberry, banana,tomatoe, carrot, onion. B= Beef Is it possible to create this formula? Thanks |
Selecting a Keyword
THe formula works great, but how can i modified it so in case it contains the
keyword strawberry it gets strayberry even if there is another keyword in the cell. For example: Strawberry Cake = Strawberry Apple Pie = Apple chocolate Strawberry = Strawberry "T. Valko" wrote: One way... Create a 2 column lookup table like this: apple...fruit strawberry...fruit banana...fruit tomatoe...vegetable carrot...vegetable onion...vegetable Assume that table is in the range D1:E6 Then use this formula in column B: =IF(COUNTIF(D$1:D$6,A1),VLOOKUP(A1,D$1:E$6,2,0),IF (A1<"","Beef","")) -- Biff Microsoft Excel MVP "Gmata" wrote in message ... Hello guy i am trying to come up with a formula but i have no idea how i need some help from the experts. I have two columns, 1 contains information and the other one is empty where the formula is going to be called "product type" So what i need is: If column A contains the word: Apple, strawberry, banana. Column B should be = Fruits If column A contains: tomatoe, carrot, onion B = Vegetable If columb A contains anything other than: Apple, strawberry, banana,tomatoe, carrot, onion. B= Beef Is it possible to create this formula? Thanks |
Selecting a Keyword
Ok, it's more complicated...
Still using the lookup table in D1:E6. Array entered** : =IF(COUNT(SEARCH(D$1:D$6,A1)),LOOKUP(1E100,SEARCH( D$1:D$6,A1),E$1:E$6),IF(A1<"","Beef","")) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note that if there are multiple keywords in a cell like: Strawberry Onion The formula will match whichever keyword appears in the lookup table furthest down the list. So, for an entry like Strawberry Onion, the formula will match both keywords but will return vegetable because onion appears in the lookup table below Strawberry. -- Biff Microsoft Excel MVP "Gmata" wrote in message ... THe formula works great, but how can i modified it so in case it contains the keyword strawberry it gets strayberry even if there is another keyword in the cell. For example: Strawberry Cake = Strawberry Apple Pie = Apple chocolate Strawberry = Strawberry "T. Valko" wrote: One way... Create a 2 column lookup table like this: apple...fruit strawberry...fruit banana...fruit tomatoe...vegetable carrot...vegetable onion...vegetable Assume that table is in the range D1:E6 Then use this formula in column B: =IF(COUNTIF(D$1:D$6,A1),VLOOKUP(A1,D$1:E$6,2,0),IF (A1<"","Beef","")) -- Biff Microsoft Excel MVP "Gmata" wrote in message ... Hello guy i am trying to come up with a formula but i have no idea how i need some help from the experts. I have two columns, 1 contains information and the other one is empty where the formula is going to be called "product type" So what i need is: If column A contains the word: Apple, strawberry, banana. Column B should be = Fruits If column A contains: tomatoe, carrot, onion B = Vegetable If columb A contains anything other than: Apple, strawberry, banana,tomatoe, carrot, onion. B= Beef Is it possible to create this formula? Thanks |
All times are GMT +1. The time now is 04:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com