## Return multiple matches with wildcard vlookup

Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP:

*"The data in column A contains a series of phrases. Every phrase contains one color in the phrase. The goal of the challenge is to use the lookup table in D2:E10 to assign the phrase to one of the names in column E."
Source : https://www.mrexcel.com/pc18.shtml*

The winner created this formula to return last match in the range:

The third winner used circular reference and iterations to return multiple matches.

### How to return multiple matches without circular references

**Array formula in cell C2:**

Recommended article:

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Search for a text string and return multiple adjacent values

Alternative array formula in C2:

**How to create an array formula**

- Select cell C2
- Copy above array formula
- Paste to formula bar
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys

Copy cell C2 and paste it right as far as needed. Then copy cells and paste down as far as needed.

**How to copy array formula**

- Select cell C2
- Copy (Ctrl + c)
- Select cell D2
- Paste (Ctrl + v)

### Explaining array formula in cell C2

**Step 1 - Find text string**

The COUNTIF function counts values based on a condition or criteria, in this case two asterisks are appended to the second argument. This makes the COUNTIF function count any value that contains the string, asterisk matches zero or more characters.

COUNTIF($A2,"*"&$F$2:$F$12&"*")

becomes

COUNTIF("the ocean is blue",{"*blue*"; "*red*"; "*yellow*"; "*pink*"; "*orange*"; "*brown*"; "*white*"; "*lavendar*"; "*magenta*"; "*ocean*"; "*shirt*"})

and returns {1; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0}

**Step 2 - Convert array to row numbers**

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

IF(COUNTIF($A2,"*"&$F$2:$F$12&"*"), ROW($F$1:$F$11), "")

becomes

IF({1; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, "")

and returns {1; ""; ""; ""; ""; ""; ""; ""; ""; 10; ""}

**Step 3 - Return the k-th smallest row number in this data set**

To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest. SMALL( array, k) The second argument changes when the cell is copied to cells below, this will extract a new value in each cell.

SMALL(IF(COUNTIF($A2,"*"&$F$2:$F$12&"*"), ROW($F$1:$F$11), ""), COLUMN(A1))

becomes

SMALL({1; ""; ""; ""; ""; ""; ""; ""; ""; 10; ""},1)

and returns 1.

**Step 4 ****- Return a value or reference of the cell at the intersection of a particular row and column**

The INDEX function returns a value based on a cell reference and column/row numbers.

INDEX($G$2:$G$12, SMALL(IF(COUNTIF($A2,"*"&$F$2:$F$12&"*"), ROW($F$1:$F$11), ""), COLUMN(A1)))

becomes

=INDEX($G$2:$G$12, 1)

becomes

=INDEX({"Joe"; "Bob"; "Mary"; "Fred"; "Ralph"; "Lora"; "Tracy"; "Earl"; "Jenny"; "John"; "Theresa"}, 1)

and returns "Joe" in cell C2.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Lookup with multiple criteria and return multiple search results

Question: How do I search a list containing First name column and a last name column? I want to search […]

Search and display all cells that contain multiple search strings

Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]

Search for multiple text strings in column – AND logic

Question: How do I search a list for two text strings and return a list with where both strings occurs? […]

Search columns for a string and return records – AND logic

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Search each column for a string each and return multiple records – OR logic

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]

Wildcard lookups and include or exclude criteria

This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]

Row is returned if both search strings are found on the same row

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

Minh Hung asks: Hello Mr Oscar I have the matter to create a megaformula to categorize my list. For short […]

### 9 Responses to “Return multiple matches with wildcard vlookup”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

I have used the winning solution for my cause and it helped me a lot and saved a lot of very valuable time!!!

THANK YOU VERY MUCH for your kind help.

Andrey

LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10)

Anyone explain this formula step by step..because i don't understand the function of the formula....

I totally agree with Ahmed.

I ma intrigued by the formula that won. I totally get the "lookup" and the "search" portions of it but I do NOT understand the "2^15". What function is this performing? How does this work? I can't even find anything online that resembles it (other than in its simplest form of "2 to the 15th power").

Can you offer some insight???

Hi,

My table array has wildcards that is of the format "ABCD****" or sometimes "ABCDE***" or sometimes "ABCDEF**". The main sheet contains that data of the format "ABCDEFGH" and needs to fetch an account from another cell in Sheet2(that contains the array).

Kindly help with handling this case.

Thanks,

CK.

CK,

Perhaps you are looking for this post:

Search for a text string and return multiple adjacent values

is this possible? I have multiple terminal IDs and need to convert them into a location. This is just the beginning of the 'if' statement with 2 of the beginning ID numbers. I am using a table.

=IF(A25="@ ATM Deposit 0087*",VLOOKUP("@ ATM Deposit 0087*",$A4:$B18,2,FALSE),IF(A25="@ ATM Deposit 0487*",VLOOKUP("@ ATM Deposit 0487*",$A4:$B18,2,FALSE)))

Hopefully you can help me!! I'm using this formula with no issues: =+IF(C24="","",IFERROR(INDEX('Edited Data'!$P$2:$P$300,SMALL(IF(C24='Edited Data'!$N$2:$N$300,ROW('Edited Data'!$N$2:$N$300)-ROW('Edited Data'!$N$2)+1),COLUMN('Edited Data'!$A$1))),""))

Basically, C24 is a name, and it's looking for that name in N2:N300 on a tab called Edited Data. It works perfectly, but instead of looking for the name, I want it to look for the name as a wild card. 99% of the time it works, but sometimes the name is apart of a string of text and that formula doesn't pick it up. I'm OK with having it done manually if necessary, but if I can have it look for *C24* instead of C24, that would be amazing!!

Hi Oscar,

Tks for the "Return multiple matches with wildcard vlookup in excel" formula " =LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10)" whick worked 100% for me. Is it possible to get it to an exact match? eg If I use Brown as the keyword to lookup then it would find brown and brownish, etc?

Tks!

2 things.

1. Your explanation references COUNTIF, but the actual formula uses ISNUMBER(SEARCH(...)). I can only assume this is because COUNTIF doesn't appear to maintain array awareness the way it is depicted in the explanation.

2. I did find this useful in addressing a related problem I was having, so I thought I would share back. The following formula will return the same results as in columns C in the downloaded workbook, but without requiring that the formula be entered in array mode.

=IFERROR(INDEX($G$2:$G$12,SMALL(IF(N(IF({1},INDEX(ISNUMBER(SEARCH($F$2:$F$12,$A2)),,1))),N(IF({1},INDEX(ROW($G$2:$G$12)-1,,1))),""),1)),"")

To get the second result (column D), one would change the last 1 in the formula to a 2.

Clearly this could be set to a cell reference to facilitate getting multiple results, or returning an arbitrary result.

I've wrapped it in an IFERROR function to avoid the NUM! errors, though this is not required.

What is required is that this be done in an Excel 2007 or newer file format (.xlsx), otherwise Excel will refuse to accept the formula.

I tend to avoid using array mode formulas because I find the strictures of array mode formulas interfere with my preferred method of presentation. An even simpler version is likely possible in the newest versions of Excel (2019, or the latest 365 version), but alas I do not have access to them.

If it helps you, cheers.