Index Match help using a partial match

[dakota727]

I have a general question about using index and match to include partial matches using wildcards. I am trying to use an array formula below to get what I'm after.

={INDEX(B3:F25,MATCH(1,(D3:D25="CAL")*(B3:B25="*"&K3&"*"),0),5)}

I hoped to use an array function to find the value where I return the value in column F where I find a match for Column B contains L1725662-1 and where column D equals "Cal". I think I'm close but I can't seem to get it to work. Does anyone see what I am missing? Or is there a better way to get what I'm after.
I trying to get to a formula that will not require a rigid format for entering the labels so the user only needs the base sample id anywhere in column B to find the corresponding row where column D equals "CAL".

Any help would be appreciated.

 

[arthurbr]

Try

=SUMPRODUCT(--(D3:D25="CAL"),--(ISNUMBER(SEARCH("*"&K3&"*",B3:B25)))*F23:F25)

 

[dakota727]

=SUMPRODUCT(--(D3:D25="CAL"),--(ISNUMBER(SEARCH("*"&K3&"*",B3:B25)))*F3:F25)

Thank you that works a lot better. I still have the issue for when I have a situation with both L1725662-1 and L1725662-11 in the list. The sum product will sum the two values in column F since L1725662-11 will satisfy L1725662-1*. I think that will be a problem with the wildcard strategy no matter what I try. I was hoping to avoid forcing the users into some kind of entry convention but I think something like that will have to happen.

My latest thought was to combine the text strings of columns B and D and use a simple vlookup with a reference to the combined text including wildcards. As long as the list is ascending it should work and return the result for -1 before finding -11 if that makes sense. Nothing I tried is perfect yet so I am still looking and trying any suggestions.

Thank you very much for your suggestion.