Excel matching multiple partial values

Question

I've posted an example image showing what I want to achieve. I want to match partial values from row D in row A. When there is a match it should return the value from D. I have words that can have multiple partial matches (for example 'summer dress' and 'dress' are both partial match for 'XXL Summer dress') I thought I'd order row D from most characters to least to get the best match.

Normally I'd use multiple IF functions, but in my file column A contains 30.000 values and I have 300 values in row D, which makes the formula too big I guess.

Is it possible to get the results as shown in the image for these amounts of values?

I've tried VLOOKUP / INDEX & MATCH and some macros, but none seem to return the value I want.

Scott Craner · Accepted Answer · 2019-09-12 14:42:19Z

3

Use INDEX/AGGREGATE:

=INDEX(D:D,AGGREGATE(15,7,ROW($D$3:$D$6)/(ISNUMBER(SEARCH($D$3:$D$6,A3))),1))

This will iterate through the values in $D$3:$D$6 to see if they exist in the longer string, then it will return if it finds a match. If no match is found it will return an error.

answered Sep 12, 2019 at 14:42

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chillin · Accepted Answer · 2019-09-12 15:49:40Z

MATCH accepts wildcards (for partial matches). You can try this formula in cell B11 of the worksheet shown (and drag/fill down).

=INDEX(A$11:A$14,AGGREGATE(15,6,MATCH("*"&$D$11:$D$14&"*",$A11,0)*(ROW($D$11:$D$14)-ROW(D$11)+1),1))

So:

MATCH is used to check if the Word partially matches any of the Categories. The * wildcard will match any character (including "no character"). It should return an array of #N/A for non-matches and 1 for matches.
ROW is used to create a corresponding 1-based array of equal length.
In the absence of a FILTER function, AGGREGATE is used to basically filter/exclude all errors. The 15 means only the first matching Category is returned.
INDEX is used to retrieve the appropriate Category and complete the lookup.

If performance is really an issue, you might want to consider turning off automatic re-calculation, or using a VBA routine, or not storing the data in Excel.

Collectives™ on Stack Overflow

Excel matching multiple partial values

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related