Index Match help using a partial match

dakota727

Board Regular
Joined
Dec 3, 2006
Messages
163
Office Version
  1. 365
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.

Capture1.JPG
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,109
Office Version
  1. 2010
Try
Excel Formula:
=SUMPRODUCT(--(D3:D25="CAL"),--(ISNUMBER(SEARCH("*"&K3&"*",B3:B25)))*F23:F25)
 

dakota727

Board Regular
Joined
Dec 3, 2006
Messages
163
Office Version
  1. 365
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,856
Messages
5,541,464
Members
410,546
Latest member
htran4
Top