Index Match with "Or" Criteria and Search Text

[Nicole24]

Hi!

I am trying to return an item name if either criteria is found in the item description. Column B has the formula that I am currently using to accomplish this, and although it works fine for this example, it is way too long in my real data set and is an obstacle when more items needs to be added on. Column C has the search function but I am only able to return Grouping 1. Column D has the or criteria however, I cannot search for text and if both Grouping 1 and Grouping 2 are the same it will not return. I've tried numerous variations of merging the two formulas together but I haven't had any luck. Hoping someone else can help me think outside the box.

Here is my example:

Book8
	A	B	C	D	E	F	G	H
1	Item Desc	Grouping	Experiment #1	Experiment #2		Item	Grouping 1	Grouping 2
2	2010 Apple 20	Fruit	Fruit	#N/A		Fruit	Apple	Banana
3	2008 Apple 50	Fruit	Fruit	#N/A		Vegetable	Carrot	Carrot
4	2102 Carrot 15	Vegetable	Vegetable	#N/A
5	2102 Apple 15	Fruit	Fruit	#N/A
6	2010 Banana 30	Fruit	#N/A	#N/A
7	2105 Carrot 60	Vegetable	Vegetable	#N/A
8	2105 Carrot 15	Vegetable	Vegetable	#N/A
9	2105 Apple 60	Fruit	Fruit	#N/A
10	Apple	Fruit	Fruit	Fruit
11	Banana	Fruit	#N/A	Fruit
12	Carrot	Vegetable	Vegetable	#N/A
Sheet1

Cell Formulas
Range	Formula
B2:B12	B2	=IF(ISNUMBER(SEARCH($G$2,$A2)),$F$2,IF(ISNUMBER(SEARCH($H$2,$A2)),$F$2,IF(ISNUMBER(SEARCH($G$3,$A2)),$F$3,IF(ISNUMBER(SEARCH($H$3,$A2)),$F$3,""))))
C2:C12	C2	=INDEX($F$2:$F$3,MATCH(TRUE,ISNUMBER(SEARCH($G$2:$G$3,A2)),0))
D2:D12	D2	=INDEX($F$2:$F$3,MATCH(1,INDEX( ($G$2:$G$3=A2)+($H$2:$H$3=A2),0),0))

 

[Fluff]

Hi & welcome to MrExcel.
How about

INDEX($F$2:$F$3,AGGREGATE(15,6,(ROW($F$2:$F$3)-ROW($F$2)+1)/(ISNUMBER(SEARCH($G$2:$H$3,A2))),1))

 

[Nicole24]

Hi & welcome to MrExcel.
How about

INDEX($F$2:$F$3,AGGREGATE(15,6,(ROW($F$2:$F$3)-ROW($F$2)+1)/(ISNUMBER(SEARCH($G$2:$H$3,A2))),1))

Worked like a charm. Thank you!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Nicole24]

You're welcome & thanks for the feedback.

Thank you again for the help. I have a follow up question that is similar to my original one if you are able to help with that too.

I need the payment type to return in column C that is specific to either group 1 or group 2. Similar to my original post I was using the IF(ISNUMBER(SEARCH( formula to find if either grouping was in column A, and then returning the payment type in column C. I assumed once I got the first formula I would have a better understanding of how to go about the second formula on my own, but alas here I am.

Thank you in advance!

Book2
	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))

 

[Fluff]

As this is now a different question, it needs a new thread. Thanks

 

[Nicole24]

As this is now a different question, it needs a new thread. Thanks

Got it. Thank you.

 

[Fluff]