Search Text in Multiple Columns and Return Value from Multiple Columns

Nicole24

New Member
Joined
Jan 20, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am trying to return values if either criteria is found in the item description. For example if the text in Column G is found in Column A then I want the value in Column H to return; and if the text in Column I is found in Column A then I want the value in Column J to return.

Book2.xlsx
ABCDEFGHIJ
1Item DescGroupingPayment TypeItemGrouping 1Payment Type 1Grouping 2Payment Type 2
22010 Apple 20FruitFruit2010 AppleCredit2010 BananaCredit
32010 Apple 50FruitVegetable2102 CarrotCash2105 CarrotCredit
42102 Carrot 15Vegetable
52010 Apple 15Fruit
62010 Banana 30Fruit
72105 Carrot 60Vegetable
82105 Carrot 15Vegetable
92010 Apple 60Fruit
102010 Apple 100Fruit
112010 Banana 50Fruit
122102 Carrot 45Vegetable
Sheet1
Cell Formulas
RangeFormula
B2:B12B2=INDEX($F$2:$F$3,AGGREGATE(15,6,(ROW($F$2:$F$3)-ROW($F$2)+1)/(ISNUMBER(SEARCH($G$2:$I$3,A2))),1))
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
How about this?

Excel Message board.xlsm
ABCDEFGHIJ
1Item DescGroupingPayment TypeItemGrouping 1Payment Type 1Grouping 2Payment Type 2
22010 Apple 20FruitCreditFruit2010 AppleCredit2010 BananaCredit
32010 Apple 50FruitCreditVegetable2102 CarrotCash2105 CarrotCredit
42102 Carrot 15VegetableCash
52010 Apple 15FruitCredit
62010 Banana 30FruitCredit
72105 Carrot 60VegetableCredit
82105 Carrot 15VegetableCredit
92010 Apple 60FruitCredit
102010 Apple 100FruitCredit
112010 Banana 50FruitCredit
122102 Carrot 45VegetableCash
30
Cell Formulas
RangeFormula
B2:B12B2=INDEX($F$2:$F$3,AGGREGATE(15,6,(ROW($F$2:$F$3)-ROW($F$2)+1)/(ISNUMBER(SEARCH($G$2:$I$3,A2))),1))
C2:C12C2=CHOOSE(IFERROR(AGGREGATE(14,6,SEARCH(LEFT(SUBSTITUTE(A2," ","#",2),FIND("#",SUBSTITUTE(A2," ","#",2))-1),$G$2:$G$3),1),2),VLOOKUP(LEFT(SUBSTITUTE(A2," ","#",2),FIND("#",SUBSTITUTE(A2," ","#",2))-1),$G$2:$H$3,2,0),VLOOKUP(LEFT(SUBSTITUTE(A2," ","#",2),FIND("#",SUBSTITUTE(A2," ","#",2))-1),$I$2:$J$3,2,0))
 
Upvote 0
Super close! The Left function is my hang-up though as the begging of the text isn't always the same. My bad for not providing more diverse sample data. Below is a better example of what the Item Description in Column A would be.

Book2.xlsx
ABCDEFGHIJ
1Item DescGroupingPayment TypeItemGrouping 1Payment Type 1Grouping 2Payment Type 2
22005 Apple Cake 20FruitFruitApple CakeCreditApple PieCash
32030 Apple Pie 50FruitVegetableCarrotCashCucumberCredit
4Apple Pie 15Fruit
5CucumberVegetable
6Carrot CakeVegetable
72020 Cucumber 15Vegetable
82001 Carrot Cake 10Vegetable
9Apple Pie 50Fruit
102002 CucumberVegetable
112005 Carrot CakeVegetable
122010 Apple Cake 60Fruit
13Apple CakeFruit
142005 Cucumber 35Vegetable
15Carrot Cake 10Vegetable
Sheet1
Cell Formulas
RangeFormula
B2:B15B2=INDEX($F$2:$F$3,AGGREGATE(15,6,(ROW($F$2:$F$3)-ROW($F$2)+1)/(ISNUMBER(SEARCH($G$2:$I$3,A2))),1))
 
Upvote 0
Excel Message board.xlsm
ABCDEFGHIJ
1Item DescGroupingPayment TypeItemGrouping 1Payment Type 1Grouping 2Payment Type 2
22005 Apple Cake 20FruitCreditFruitApple CakeCreditApple PieCash
32030 Apple Pie 50FruitCashVegetableCarrotCashCucumberCredit
4Apple Pie 15FruitCash
5CucumberVegetableCredit
6Carrot CakeVegetable#N/A
72020 Cucumber 15VegetableCredit
82001 Carrot Cake 10Vegetable#N/A
9Apple Pie 50FruitCash
102002 CucumberVegetableCredit
112005 Carrot CakeVegetable#N/A
122010 Apple Cake 60FruitCredit
13Apple CakeFruitCredit
142005 Cucumber 35VegetableCredit
15Carrot Cake 10Vegetable#N/A
30 (2)
Cell Formulas
RangeFormula
B2:B15B2=INDEX($F$2:$F$3,AGGREGATE(15,6,(ROW($F$2:$F$3)-ROW($F$2)+1)/(ISNUMBER(SEARCH($G$2:$I$3,A2))),1))
C2:C15C2=CHOOSE(IFERROR(AGGREGATE(14,6,SEARCH(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")),$G$2:$G$3),1),2),VLOOKUP(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")),$G$2:$H$3,2,0),VLOOKUP(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")),$I$2:$J$3,2,0))


you need to make standard name in item group .
In order to work excel formula you need to change Carrot to Carrot Cake
 
Upvote 0
How about
+Fluff 1.xlsm
ABCDEFGHIJ
1Item DescGroupingPayment TypeItemGrouping 1Payment Type 1Grouping 2Payment Type 2
22005 Apple Cake 20FruitCreditFruitApple CakeCreditApple PieCash
32030 Apple Pie 50FruitCashVegetableCarrotCashCucumberCredit
4Apple Pie 15FruitCash
5CucumberVegetableCredit
6Carrot CakeVegetableCash
72020 Cucumber 15VegetableCredit
82001 Carrot Cake 10VegetableCash
9Apple Pie 50FruitCash
102002 CucumberVegetableCredit
112005 Carrot CakeVegetableCash
122010 Apple Cake 60FruitCredit
13Apple CakeFruitCredit
142005 Cucumber 35VegetableCredit
15Carrot Cake 10VegetableCash
16
First sale
Cell Formulas
RangeFormula
B2:B15B2=INDEX($F$2:$F$3,AGGREGATE(15,6,(ROW($F$2:$F$3)-ROW($F$2)+1)/(ISNUMBER(SEARCH($G$2:$I$3,A2))),1))
C2:C15C2=FILTER(FILTER($H$2:$J$3,$F$2:$F$3=B2),COUNTIF(A2,"*"&FILTER($G$2:$I$3,$F$2:$F$3=B2)&"*"))
 
Upvote 0
Solution
Thank you! This works perfectly!
How about
+Fluff 1.xlsm
ABCDEFGHIJ
1Item DescGroupingPayment TypeItemGrouping 1Payment Type 1Grouping 2Payment Type 2
22005 Apple Cake 20FruitCreditFruitApple CakeCreditApple PieCash
32030 Apple Pie 50FruitCashVegetableCarrotCashCucumberCredit
4Apple Pie 15FruitCash
5CucumberVegetableCredit
6Carrot CakeVegetableCash
72020 Cucumber 15VegetableCredit
82001 Carrot Cake 10VegetableCash
9Apple Pie 50FruitCash
102002 CucumberVegetableCredit
112005 Carrot CakeVegetableCash
122010 Apple Cake 60FruitCredit
13Apple CakeFruitCredit
142005 Cucumber 35VegetableCredit
15Carrot Cake 10VegetableCash
16
First sale
Cell Formulas
RangeFormula
B2:B15B2=INDEX($F$2:$F$3,AGGREGATE(15,6,(ROW($F$2:$F$3)-ROW($F$2)+1)/(ISNUMBER(SEARCH($G$2:$I$3,A2))),1))
C2:C15C2=FILTER(FILTER($H$2:$J$3,$F$2:$F$3=B2),COUNTIF(A2,"*"&FILTER($G$2:$I$3,$F$2:$F$3=B2)&"*"))
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,550
Messages
6,179,455
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top