Search Text in Multiple Columns and Return Value from Multiple Columns

[Nicole24]

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
	A	B	C	D	E	F	G	H	I	J
1	Item Desc	Grouping	Payment Type			Item	Grouping 1	Payment Type 1	Grouping 2	Payment Type 2
2	2010 Apple 20	Fruit				Fruit	2010 Apple	Credit	2010 Banana	Credit
3	2010 Apple 50	Fruit				Vegetable	2102 Carrot	Cash	2105 Carrot	Credit
4	2102 Carrot 15	Vegetable
5	2010 Apple 15	Fruit
6	2010 Banana 30	Fruit
7	2105 Carrot 60	Vegetable
8	2105 Carrot 15	Vegetable
9	2010 Apple 60	Fruit
10	2010 Apple 100	Fruit
11	2010 Banana 50	Fruit
12	2102 Carrot 45	Vegetable
Sheet1

Cell Formulas
Range	Formula
B2:B12	B2	=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))

 

[alz]

How about this?

Excel Message board.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Item Desc	Grouping	Payment Type			Item	Grouping 1	Payment Type 1	Grouping 2	Payment Type 2
2	2010 Apple 20	Fruit	Credit			Fruit	2010 Apple	Credit	2010 Banana	Credit
3	2010 Apple 50	Fruit	Credit			Vegetable	2102 Carrot	Cash	2105 Carrot	Credit
4	2102 Carrot 15	Vegetable	Cash
5	2010 Apple 15	Fruit	Credit
6	2010 Banana 30	Fruit	Credit
7	2105 Carrot 60	Vegetable	Credit
8	2105 Carrot 15	Vegetable	Credit
9	2010 Apple 60	Fruit	Credit
10	2010 Apple 100	Fruit	Credit
11	2010 Banana 50	Fruit	Credit
12	2102 Carrot 45	Vegetable	Cash
30

Cell Formulas
Range	Formula
B2:B12	B2	=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:C12	C2	=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))

 

[Nicole24]

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
	A	B	C	D	E	F	G	H	I	J
1	Item Desc	Grouping	Payment Type			Item	Grouping 1	Payment Type 1	Grouping 2	Payment Type 2
2	2005 Apple Cake 20	Fruit				Fruit	Apple Cake	Credit	Apple Pie	Cash
3	2030 Apple Pie 50	Fruit				Vegetable	Carrot	Cash	Cucumber	Credit
4	Apple Pie 15	Fruit
5	Cucumber	Vegetable
6	Carrot Cake	Vegetable
7	2020 Cucumber 15	Vegetable
8	2001 Carrot Cake 10	Vegetable
9	Apple Pie 50	Fruit
10	2002 Cucumber	Vegetable
11	2005 Carrot Cake	Vegetable
12	2010 Apple Cake 60	Fruit
13	Apple Cake	Fruit
14	2005 Cucumber 35	Vegetable
15	Carrot Cake 10	Vegetable
Sheet1

Cell Formulas
Range	Formula
B2:B15	B2	=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))

 

[alz]

Excel Message board.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Item Desc	Grouping	Payment Type			Item	Grouping 1	Payment Type 1	Grouping 2	Payment Type 2
2	2005 Apple Cake 20	Fruit	Credit			Fruit	Apple Cake	Credit	Apple Pie	Cash
3	2030 Apple Pie 50	Fruit	Cash			Vegetable	Carrot	Cash	Cucumber	Credit
4	Apple Pie 15	Fruit	Cash
5	Cucumber	Vegetable	Credit
6	Carrot Cake	Vegetable	#N/A
7	2020 Cucumber 15	Vegetable	Credit
8	2001 Carrot Cake 10	Vegetable	#N/A
9	Apple Pie 50	Fruit	Cash
10	2002 Cucumber	Vegetable	Credit
11	2005 Carrot Cake	Vegetable	#N/A
12	2010 Apple Cake 60	Fruit	Credit
13	Apple Cake	Fruit	Credit
14	2005 Cucumber 35	Vegetable	Credit
15	Carrot Cake 10	Vegetable	#N/A
30 (2)

Cell Formulas
Range	Formula
B2:B15	B2	=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:C15	C2	=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

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Item Desc	Grouping	Payment Type			Item	Grouping 1	Payment Type 1	Grouping 2	Payment Type 2
2	2005 Apple Cake 20	Fruit	Credit			Fruit	Apple Cake	Credit	Apple Pie	Cash
3	2030 Apple Pie 50	Fruit	Cash			Vegetable	Carrot	Cash	Cucumber	Credit
4	Apple Pie 15	Fruit	Cash
5	Cucumber	Vegetable	Credit
6	Carrot Cake	Vegetable	Cash
7	2020 Cucumber 15	Vegetable	Credit
8	2001 Carrot Cake 10	Vegetable	Cash
9	Apple Pie 50	Fruit	Cash
10	2002 Cucumber	Vegetable	Credit
11	2005 Carrot Cake	Vegetable	Cash
12	2010 Apple Cake 60	Fruit	Credit
13	Apple Cake	Fruit	Credit
14	2005 Cucumber 35	Vegetable	Credit
15	Carrot Cake 10	Vegetable	Cash
16
First sale

Cell Formulas
Range	Formula
B2:B15	B2	=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:C15	C2	=FILTER(FILTER($H$2:$J$3,$F$2:$F$3=B2),COUNTIF(A2,"*"&FILTER($G$2:$I$3,$F$2:$F$3=B2)&"*"))

 

[Nicole24]

Thank you! This works perfectly!

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Item Desc	Grouping	Payment Type			Item	Grouping 1	Payment Type 1	Grouping 2	Payment Type 2
2	2005 Apple Cake 20	Fruit	Credit			Fruit	Apple Cake	Credit	Apple Pie	Cash
3	2030 Apple Pie 50	Fruit	Cash			Vegetable	Carrot	Cash	Cucumber	Credit
4	Apple Pie 15	Fruit	Cash
5	Cucumber	Vegetable	Credit
6	Carrot Cake	Vegetable	Cash
7	2020 Cucumber 15	Vegetable	Credit
8	2001 Carrot Cake 10	Vegetable	Cash
9	Apple Pie 50	Fruit	Cash
10	2002 Cucumber	Vegetable	Credit
11	2005 Carrot Cake	Vegetable	Cash
12	2010 Apple Cake 60	Fruit	Credit
13	Apple Cake	Fruit	Credit
14	2005 Cucumber 35	Vegetable	Credit
15	Carrot Cake 10	Vegetable	Cash
16
First sale

Cell Formulas
Range	Formula
B2:B15	B2	=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:C15	C2	=FILTER(FILTER($H$2:$J$3,$F$2:$F$3=B2),COUNTIF(A2,"*"&FILTER($G$2:$I$3,$F$2:$F$3=B2)&"*"))

 

[Fluff]

Glad we could help & thanks for the feedback.