VLOOKUP the Nth matched item, with helper column

This should be the last post in 2015.  So it’s better to answer unanswered question in the year.  :)

Question: VLOOKUP will return the first matched value found, in case there are multiple matched records what formula should we use?”

This can be achieved by using VLOOKUP with a helper column, or by complicated array formula. VLOOKUP with helper column is my preference not only because it is easier to construct, but also (and mainly) because most users with experience in writing formula would understand it.  I think the latter is important.  It is better using a formula that you (and other regular users) can modify according to your need than seeking help from time to time.

Let’s take a look at the demonstration below:Excel Tip - VLOOKUP nth match.gif

The formula in G2:


Yes.  It is as simple as that.  You may notice that the table_array starts in column A instead of column B.  That hidden column is essential and it makes the VLOOKUP of multiple matched record easy.

Let’s examine what is hidden:Excel Tip - VLOOKUP nth match 1.PNG

In A2,

=COUNTIF($B$2:$B2,B2)&"|"&B2   'copy down

This formula creates a unique value for VLOOKUP.

  • The COUNTIF($B$2:$B2,B2) tells us the number of occurrence of an item on the list.  (Note the absolute reference $B$2 and the relative row reference $B2 in the range for COUNTIF);
  • The &”|”&B2 then gives you the unique value for VLOOKUP.

With the helper column, VLOOKUP the Nth matched record is a piece of cake.

  • F2&”|”&E2 is the lookup_value i.e. which item and which one (the same structure used in the helper column) 
  • $A$2:$C$13 is the table_array (it starts from column A, not B)
  • The column_index is 3 (not 2 because it starts from column A)
  • FALSE (or 0) in the final argument for exact match.

Excel Tip - VLOOKUP nth match

Tip: To stay in the same cell after Enter, press Ctrl+Enter

Final notes:

  • #N/A means the item you input and the case of match does not exist on the table.  You may replace the #N/A with any messages by using IFERROR;
  • If you apply this technique to a really large table, speed may suffer.

Now it’s time to turn off your computer.  It’s Christmas Eve.

Sincere wishes to all of you and your beloved ones!

Merry Christmas


 Happy New Year!  

See you in 2016🙂

This entry was posted in Formula and tagged , . Bookmark the permalink.

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