## Lookup a specific combination of characters exclusively, e.g. 5A not 5AA

Got the following question:

hi, how do i search for a specific combination of characters exclusively i.e. if one cell contains 5A;3B&4C while another cell contains 5AA;3B&4C, and my search criteria is 5A, i want the results to return only the cell that contains 5A, not both cells because i am not looking for 5AA

To visualize the data described, I put a few more cells following similar pattern.

 AABC;3B&4C 5AA;3B&4C 5A;3B&4C 5AA;3B&4CC 5A;ABCDE 5AC;3B&4C

And because of limited details given, I’ve made an important assumption: The search term followed by a common delimiter, i.e. “;” in this case.

If that’s the case, the request is doable with formula along:

### Option 1 – Regular VLOOKUP

```=VLOOKUP("*"&\$B\$1&";*",\$A\$5:\$A\$10,1,FALSE)
where B1 resides the search term, i.e. lookup value
A5:A10 is the lookup range, i.e. table_array```

As you see, the solution is a basic VLOOKUP construction with modification made to the lookup value.  The search term is “concatenated” with the delimited “;” after, and the * wildcard before and after.  By this little trick, we give a specific instruction to Excel to search for the item containing “5A;“.  That’s how we exclude those 5AA, 5AB, 5AC… etc.

Make sense?

However, what if, you expect case sensitive search? i.e. Search for “5A” but not “5a”?

Note: VLOOKUP is not case sensitive.   You may want to read this blogpost for case-sensitive VLOOKUP.

### Option 2 – Array formula comes to rescue

```{=INDEX(\$A\$5:\$A\$10,MATCH(TRUE,FIND(\$B\$1&";",\$A\$5:\$A\$10)>0,0))}
Note: this is an array formula.
The {} is input automatically by CTRL+SHIFT+ENTER.```

Again, the concatenation of “;” after the search term is the key to this solution.

This construction is very similar to first solution discussed in case-sensitive VLOOKUP.  The twist we did here is to change the “EXACT” to “FIND” and evaluate it with “>0” in order to locate the matching item.  “FIND” is case sensitive.  That’s how we differentiate “5A” from “5a”.  You may learn more about FIND vs SEARCH here.

Limitation to both solution: Only first match will be returned.  Put it in other word, it assumes there is only one match.  🙂

You may download the Sample File – Lookup 5A but not 5AA to play around.

### Challenge:

What if we don’t have a predefined delimiter after the search term?  Honestly, I don’t have a formula solution.  If you do, please share with us in comments.  🙂

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Formula and tagged , , , , . Bookmark the permalink.

### 3 Responses to Lookup a specific combination of characters exclusively, e.g. 5A not 5AA

1. Sidney Mashiloane says:

Hi

Thanks for the response. However i wanted a solution that would list all the cells that contain the entry. Once more, thanks a million

Ignore my post made this morning, it was the same question posted before I saw your previous response

Cheers

Like

2. Emmett says:

Depending on how long the lookup data is, what about the following Array ?

={INDEX(\$A\$5:\$A\$10,MATCH(B1&”*”,SUBSTITUTE(\$A\$5:\$A\$10,”5AA”,”x”),0))}

the x should avoid problems where the dataset contains “55AAA”

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.