INDEX/MATCH with Multiple Criteria and Return a Different Value

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
177
Hello,

I am trying to build a formula with multiple criteria. I know there are a fews ways of doing this. However, what I am trying to add to that formula is a text search, than a return of a different value. So, basically, I can get me formula to work fine, and it will return the appropriate value...but I do not want that value.... I want to return something different based on a search.

So, if the answer to my multiple index match contains the work "orange"; please return "O", if the answer contains the word "orange" and "banana", please return "BB", if the answer contains the word "dog" please return "P"; if the results is a blank cell, return blank.

Here is my current index match formula without that word search:
{=INDEX('Assessment Extract'!$F$2:$F$3510,MATCH(Sheet2!C$3&Sheet2!$B5,'Assessment Extract'!$C$2:$C$3510&'Assessment Extract'!$E$2:$E$3510,0))}
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,328
How about:

Book2
ABCDEFGHI
1ResultMatches
2orange bananaCMCATMouse
3BBcCCCOrangeBananaGrape
4BBBBOrangeBanana
5bOOrange
6PDog
7FelixCat
Sheet2
Cell Formulas
RangeFormula
A2A2=INDEX('Assessment Extract'!$F$2:$F$3510,MATCH(Sheet2!C$3&Sheet2!$B5,'Assessment Extract'!$C$2:$C$3510&'Assessment Extract'!$E$2:$E$3510,0))
A3A3=INDEX(D2:D7,MATCH(5,MMULT(--ISNUMBER(SEARCH(E2:I7,INDEX('Assessment Extract'!$F$2:$F$3510,MATCH(Sheet2!C$3&Sheet2!$B5,'Assessment Extract'!$C$2:$C$3510&'Assessment Extract'!$E$2:$E$3510,0)))),{1;1;1;1;1}),0))
A4A4=INDEX(D2:D7,MATCH(5,MMULT(--ISNUMBER(SEARCH(E2:I7,A2)),{1;1;1;1;1}),0))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.


Build a table somewhere, like I show here in D1:I7, containing the list of matching words and their results. The D column is the result, and the E:I columns are the words that must be found. I have a limit of 5 words per line, but that can be changed. Also note that the order of the items is important. The INDEX will return the first match, so if you have Orange before Orange,Banana then the match for Orange will be returned.

The A4 formula takes the output from your original formula in A2, the A3 formula incorporates the A2 formula within it.
Let us know how this works.
 

kparadise

Board Regular
Joined
Aug 13, 2015
Messages
177
So there is nothing we can do to do an IF(SEARCH("*orange*'), index formula.... etc. I do not think I want to go down the rout of building a new table to show results. Plus, there is only one result per item, so I do not need to worry about the "first" record" being picked up.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,328
So there is nothing we can do to do an IF(SEARCH("*orange*'), index formula.... etc. I do not think I want to go down the rout of building a new table to show results.

Well, it is possible to embed the table within the formula. The A4 formula above would become:

=INDEX({"CM";"CCC";"BB";"O";"P";"Felix";""},MATCH(5,MMULT(--ISNUMBER(SEARCH({"cat","Mouse","","","";"orange","banana","Grape","","";"Orange","banana","","","";"Orange","","","","";"dog","","","","";"Cat","","","","";"","","","",""},A2)),{1;1;1;1;1}),0))

But this is unwieldy and hard to read or change. If you don't expect to ever (or rarely) change your list, it might work for you. A table still seems to be a better option. If I knew better what your list was, I might be able to simplify it. Your example where you are matching on one word in one case and two words in another case raises the difficulty level. How many results might you have, and how many matches per item could there be?

Plus, there is only one result per item, so I do not need to worry about the "first" record" being picked up.
In your original example, you said "Orange" should return "O", and "Orange" and "banana" should return "BB". That is why I said order matters. The order you evaluate those 2 conditions will make a difference in the results. If you chose "orange" as a random word, twice, then it was not a very representative example of your data.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,783
Messages
5,542,497
Members
410,557
Latest member
chidambaramseetha
Top