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.

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.  🙂

Other topics about LOOKUP:

1. The basic of vlookup
2. Tips in constructing vlookup
3. vlookup with Match
4. vlookup options – True or False?
5. Advanced vlookup – Text vs. Number
6. Advanced vlookup – Wildcard Characters “?” and “*”
7. Alternative to vlookup – Index and Match

8. Perform VLOOKUP with 2 lookup values

9. Three different ways to do case-sensitive lookup
10. 3D vlookup
11. Lookup Nth match