CODEWITHSUNDEEP
excelexcel-formula
APlouff
APlouff

Reputation: 13

Index Match? multiple criteria with search

I'm trying to find a formula which will return "xx no" in my example below. I've tried multiple formulas and keep coming up with n/a.

=INDEX(C1:C11,MATCH(F2,A1:A11,0),MATCH("xx",C1:C11,0))
{=INDEX(C1:C11,MATCH(F2,A1:A11,0),MATCH("xx",C1:C11,0))}
=INDEX(C1:C11,MATCH(F2,A1:A11,0),MATCH(1,ISNUMBER(SEARCH("xx",C1:C11)),0))
{=INDEX(C1:C11,MATCH(F2,A1:A11,0),MATCH(1,ISNUMBER(SEARCH("xx",C1:C11)),0))}
=INDEX(A1:C11,MATCH(F2,A1:A11,0),MATCH(ISNUMBER(SEARCH("xx",C1:C11)),0))
{=INDEX(A1:C11,MATCH(F2,A1:A11,0),MATCH(ISNUMBER(SEARCH("xx",C1:C11)),0))}
=INDEX(C1:C11,MATCH(F2,A1:A11,0),MATCH(ISNUMBER(SEARCH("*"&"xx"&"*",C1:C11)),0))
{=INDEX(C1:C11,MATCH(F2,A1:A11,0),MATCH(ISNUMBER(SEARCH("*"&"xx"&"*",C1:C11)),0))}

Nemesis Image

Upvotes: 1

Views: 337

Answers (2)

Absinthe
Absinthe

Reputation: 3391

Here's a short array formula to do it:

{=INDEX(A2:C8,MATCH(F2&F3,A2:A8&LEFT(C2:C8,LEN(F3)),0),3)}

It looks like you already know about array formulas, enter then by pressing Ctrl and Shift as well as Enter

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

Use:

=INDEX(C:C,AGGREGATE(15,6,ROW($C$2:$C$11)/(($A$2:$A$11=F2)*(ISNUMBER(SEARCH(F3,$C$2:$C$11)))),1))

Or you can use this formula:

=INDEX($C$2:$C$11,MATCH(1,INDEX(($A$2:$A$11=F2)*(ISNUMBER(SEARCH(F3,$C$2:$C$11))),),0))

Upvotes: 1

Related Questions