INDEX/MATCH with Multiple Criteria and Return a Different Value

[kparadise]

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

 

[Eric W]

How about:

Book2
	A	B	C	D	E	F	G	H	I
1				Result	Matches
2	orange banana			CM	CAT	Mouse
3	BB		c	CCC	Orange	Banana	Grape
4	BB			BB	Orange	Banana
5		b		O	Orange
6				P	Dog
7				Felix	Cat
Sheet2

Cell Formulas
Range	Formula
A2	A2	=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))
A3	A3	=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))
A4	A4	=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]

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]

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.