Index Match with "Or" Criteria and Search Text

Nicole24

New Member
Joined
Jan 20, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGH
1Item DescGroupingExperiment #1Experiment #2ItemGrouping 1Grouping 2
22010 Apple 20FruitFruit#N/AFruitAppleBanana
32008 Apple 50FruitFruit#N/AVegetableCarrotCarrot
42102 Carrot 15VegetableVegetable#N/A
52102 Apple 15FruitFruit#N/A
62010 Banana 30Fruit#N/A#N/A
72105 Carrot 60VegetableVegetable#N/A
82105 Carrot 15VegetableVegetable#N/A
92105 Apple 60FruitFruit#N/A
10AppleFruitFruitFruit
11BananaFruit#N/AFruit
12CarrotVegetableVegetable#N/A
Sheet1
Cell Formulas
RangeFormula
B2:B12B2=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:C12C2=INDEX($F$2:$F$3,MATCH(TRUE,ISNUMBER(SEARCH($G$2:$G$3,A2)),0))
D2:D12D2=INDEX($F$2:$F$3,MATCH(1,INDEX( ($G$2:$G$3=A2)+($H$2:$H$3=A2),0),0))
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,653
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
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))
 
Solution

Nicole24

New Member
Joined
Jan 20, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,653
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Nicole24

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

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,653
Office Version
  1. 365
Platform
  1. Windows
As this is now a different question, it needs a new thread. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,127,806
Messages
5,627,005
Members
416,214
Latest member
boston814

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
Top