Multi Criteria Index/Match Function w Partial Search

[HKM]

Hi

I am trying to write an index-match function with a partial text match.
Here is the formula that I have so far,
{=INDEX($A$5:$A$8, MATCH(1, ("*"&B13&"*"=$A$5:$A$8)*(C13=$B$5:$B$8), 0))}



Is there any way to make this work?

Thank you!

 

[Villalobos]

Hi,

Check out this approach.

 

[mohadin]

Hi
please see the attached
hope you like it
regards

 

[HKM]

Hi,

Check out this approach.

Villalobos, thank you! This is awesome! Would it be possible to request a brief explanation of your function? Just so I can replicate it in the future.

Also, how would i go about adding additional criteria to this?

 

[HKM]

Hi
please see the attached
hope you like it
regards

Mohadin, Thank you for your response. I see that the function isn't giving an error anymore. However, it is returning the wrong value. It should return B7. Any ideas?

 

[Khalid NGO]

Hi HKM,

Another option:

=LOOKUP(2,1/(SEARCH(C13,B5:B8)*(C5:C8=D13)),B5:B8)

Regards,

 

[HKM]

Hi HKM,

Another option:

=LOOKUP(2,1/(SEARCH(C13,B5:B8)*(C5:C8=D13)),B5:B8)

Regards,

Thanks Khalid!

When I try to add another search criteria in the function, I get an error:
=LOOKUP(2,1/(SEARCH(C13,B5:B8)*(E13,B5:B8)*(C5:C8=D13)),B5:B8)

I need to match multiple criteria against a large file of text strings.

Thank you.

 

[mohadin]

Hi KHM
you'd better do this as I understand
LOOKUP(2,1/(SEARCH(C13,B5:B8)*SEARCH(E13,B5:B8)*(C5:C8=D13)),B5:B8)

 

[bosco_yip]

Maybe,

=LOOKUP(2,1/((C5:C8=D13)*MMULT(ISNUMBER(SEARCH(B13:C13,B5:B8))+0,{1;1})),B5:B8)

Remark : criteria in B13,C13 and D13 as per post# 1 indicated.

Regards
Bosco