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:

Excel Tips - Lookup AA but not ABC.PNG

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
Advertisements
This entry was posted in Formula and tagged , , , , . Bookmark the permalink.

One Response to Lookup a specific combination of characters exclusively, e.g. 5A not 5AA

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

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s