Multi-Criteria Index-Match with Wildcard

[RockandGrohl]

Hi guys,

I'm using a multi-criteria Index-Match to return a cost-code where two criteria are found in two ranges:

{=INDEX(Validation!M:M,MATCH(1,(Validation!L:L="*"&B28&"*")*(Validation!N:N=C28),0))}

So what I want is in Validation column M, I'm looking in Validation Column L for B28 = "Banana" and Validation Column N for C28 = "Donkey"

Column L in Validation has string values such as "Apple_Banana_Coconut"


So I thought if I wrap B28 in wildcards it would find it.

I have confirmed that my formula works if I remove the wildcards and replace the appropriate value in Column L with just "Banana", so it's clearly an issue with multi-criteria index-match not liking wildcards.


Thank you.

 

[duggie33]

Possibly replace

Validation!L:L="*"&B28&"*"

with

ISNUMBER(SEARCH(B28,L:L,1))

I do not have any data to test it on so I cannot say for sure that it will work.

Hope that helps,

Doug

 

[AhoyNC]

Try something like this.
Also, it's best not to use whole column references. If your data would never go below say row 10000, then use something like M2:M10000.
If you would only match 1 row then use the formula in D6 of the example. The formula in D2 will work if you have multiply matches. Drag this formula down as needed.

Book2
	A	B	C	D	E						K	L	M	N
1				More than 1 match								Header1	Validation	Header2
2				Val3								Apple	Val1	Cat
3				Val5								Apple_Coconut	Val2	Dog
4												Apple_Coconut_Banana	Val3	Donkey
5				Only 1 match								Orange	Val4	Bird
6				Val3								Grape_Banana_Apple	Val5	Donkey
27
28		Banana
Sheet2

Cell Formulas
Range	Formula
D2:D3	D2	=IFERROR(INDEX($M$2:$M$6,AGGREGATE(15,6,(ROW($M$2:$M$6)-ROW($M$2)+1)/(ISNUMBER(SEARCH($B$28,$L$2:$L$6))*($N$2:$N$6="Donkey")),ROWS($D$2:D2))),"")
D6	D6	=INDEX($M$2:$M$6,AGGREGATE(15,6,(ROW($M$2:$M$6)-ROW($M$2)+1)/(ISNUMBER(SEARCH($B$28,$L$2:$L$6))*($N$2:$N$6="Donkey")),1))

 

[Phuoc]

Try this

=LOOKUP(1,-SEARCH(B28,Validation!$L$1:$L$10000)/(Validation!$N$1:$N$10000=C28),Validation!$M$1:$M$10000)