how to add a partial text search to complicated index and match formula

[neuropsychiatry]

Hi I have a complicated index and match formula but I cannot figure out how to add a partial text search to it. Eg how do I add search for "apple*" without an array.

thank you.....here is the current formula. alex

=INDEX(Sheet2!E5:E1346,MATCH(Chooser!E26&Chooser!E23&Chooser!E29,Sheet2!B5:B1346&Sheet2!C5:C1346&Sheet2!D5:D1346,0),1,1)

 

[steve the fish]

See if this works for you:

=INDEX(Sheet2!E5:E1346,MATCH(1,INDEX(ISNUMBER(SEARCH(Chooser!E26,Sheet2!B5:B1346))*(Sheet2!C5:C1346=Chooser!E23)*(Sheet2!D5:D1346=Chooser!E29),0),0))

Ive assumed apple is in E26 and to be found in B5:B1346.

 

[neuropsychiatry]

See if this works for you:

=INDEX(Sheet2!E5:E1346,MATCH(1,INDEX(ISNUMBER(SEARCH(Chooser!E26,Sheet2!B5:B1346))*(Sheet2!C5:C1346=Chooser!E23)*(Sheet2!D5:D1346=Chooser!E29),0),0))

Ive assumed apple is in E26 and to be found in B5:B1346.

yes thanks, or close enough that I followed your lead and found the solution!