Excel OFFSET MATCH formula returning incorrect list

Question

Working on a OFFSET MATCH formula to return a unique list of values based on a reference cell.

The lookup value cell will have a one word value i.e. Cream

This formula works exactly how I need it to for exact matches

= OFFSET(PRODUCT,MATCH($E$21,PRODUCT_MIX,0)-1,0,COUNTIF(PRODUCT_MIX,$E$21), 1)

But Cream may appear in the lookup array as Cream or Ice Cream or Ice Cream Cone .. etc can appear as either single value or anywhere within a text string or comma separated text so I need the formula to include wild card ""&$E$21&""

Reference and look up array etc are named ranges

We have spent a huge amount of time researching and testing to get this right, we almost have it working but now stuck. Would appreciate if anyone can point us in the right direction

When the wildcard is added the formula is returning the correct number of results BUT its not returning the actual correct results.

= OFFSET(PRODUCT,MATCH("*"&$E$21&"*",PRODUCT_MIX,0)-1,0,COUNTIF(PRODUCT_MIX,"*"&$E$21&"*"), 1)

Example, if the correct number of results should be 4 this formula is returning the first 4 values in the column instead of the correct 4 values

Example data

PRODUCT_MIX	PRODUCT
Cream	Thickened Cream
Milk, Cream, Eggs	Scrambled Eggs
Cheese, Crackers, Olives	Antipasto
Ice, Cream	Vanilla Ice Cream
Ice, Cream, Cone	Ice Cream Cone
Milk, Milo	Chocolate Milk

If Cream was selected, I want the result to be a list containing Thickened Cream, Scrambled Eggs, Vanilla Ice Cream, Ice Cream Cone.

But the current formula is returning the following list instead, Thickened Cream, Scrambled Eggs, Antipasto, Vanilla Ice Cream

See below, Cream is selected in yellow and the formula is returning the incorrect results

Any help would be appreciated

Answer 1

I'll post this in case it is helpful. It's the closest I have been able to get but I can't figure out how to squeeze the empty cells out. I might have some more time to look at this later on.

=IF(ISNUMBER(SEARCH("*" & $E$21 & "*", PRODUCT_MIX)),PRODUCT,"")

P.S. You could accomplish the same goal with one line (Range.AdvancedCopy) in VBA.

Answer 2

When using office365 you can use: =FILTER(B:B,ISNUMBER(SEARCH($E$21,A:A)))

Answer 3

SOLVED! (3 ways)

---

HIGH-LEVEL

• This solution solves the following requirement: input 'cream' and return all cells in "PRODUCT" list that contain this word (i.e. "If Cream was selected, I want the result to be a list containing Thickened Cream, Scrambled Eggs, Vanilla Ice Cream, Ice Cream Cone")
• Notes: screenshot of cell references (& link to macro-free/safe google sheets) below
• 3 solutions (Office 365 + earlier versions)

---

SCREENSHOTS

See Below (Links section), for google sheets (with full macro code, descriptions) for 4 solutions (3 macro based + 1 macro-free albeit dynamic soln). Briefly:

• A: utilises Filter method (proposed soln.)
• B: no filter, simple if statement (spill effect, returns nuisance blanks though)
• C: as for B, but reduces nuisance blanks

LINKS

• Macro-free/safe google sheets sample

FUNCTIONS

(for convenience, as all functions within google sheet link above. For first cell in respective area, column D. References yellow-shaded input cell. Case insensitive):

Method A

=FILTER(B2:B7,1*IFERROR(SEARCH(LOWER(D1),LOWER(B2:B7))/SEARCH(LOWER(D1),LOWER(B2:B7)),0)=1))

Method B

=SORT(IF(IFERROR(SEARCH(LOWER(C7),$A$7:$A$13)/SEARCH(LOWER(C7),$A$7:$A$13),0)=1,$A$7:$A$13,""),,-1)

Method C

=UNIQUE(SORT(IF(IFERROR(SEARCH(LOWER(C7),$A$7:$A$13)/SEARCH(LOWER(C7),$A$7:$A$13),0)=1,$A$7:$A$13,""),,-1))

---

NOTES

• Potential improvements (A-C): dynamic Product range list
• Question includes superfluous info / data
• Formula proposed includes "lower" - i.e. to make 'case sensitive'
• Preferred method = A (requires Office 365)