Finding the second match with VLOOKUP in Excel – 2 ways

Here's our easy to follow step-by-step guide.

The Microsoft Excel logo on a green background.

You can trust PC GuideOur team of experts use a combination of independent consumer research, in-depth testing where appropriate – which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

Last Updated on

If you want to learn about finding the second match with VLOOKUP in Excel, you’ve come to the right place.

Manually searching for specific cell values within a range in Excel datasheets can be a cumbersome process. The process can be challenging, especially for beginners. However, this article demonstrates easy methods to identify the second match using the Excel VLOOKUP function.

So, without any further ado, let’s dive in!

How to find second match with VLOOKUP in Excel

This dataset can be used to demonstrate how to find the second sale made by a particular salesperson for a specific book using VLOOKUP in Excel. The challenge usually is that VLOOKUP by default finds the first match in the dataset.

An Excel table featuring the book title and sales amount, with the assistance of the VLOOKUP function for second match lookups.

Parameters:

  • lookup_value: The value sought in the leftmost column of the specified table.
  • table_array: The table where the lookup_value is searched in its leftmost column.
  • col_index_num: The column number in the table from which a value is to be retrieved.
  • [range_lookup]: Specifies whether an exact or partial match of the lookup_value is needed. Use 0 for an exact match, and 1 for a partial match. The default is 1 (partial match), and this parameter is optional.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Below are the two ways to look for a second match with VLOOKUP

Method 1 – Finding second match with Excel VLOOKUP & IFNA functions

To find the second sales amount for Alice in our dataset, we would use a combination of the VLOOKUP and IFNA functions along with a helper column to differentiate between the first and second occurrence. Here’s how you can do it step by step:

Step 1: Add a Helper Column

  • First, you need to create a helper column in your Excel dataset that will keep track of the occurrences of each Salesperson’s name.
  • Click on the first cell of the Helper column (let’s say it’s C4, right next to the first salesperson’s name).
  • Enter the following formula into cell C4:
    =D4 & COUNTIF($D$4:D4, D4)
  • Now, press enter and use the AutoFill feature to fill the series.
How to create a spreadsheet in Excel for finding and VLOOKUP.

Step 2: Use VLOOKUP with the Helper Column

  • You would use the VLOOKUP function to search for the third occurrence of “Alice” by creating a new search term that includes the number 3 (since you are looking for the third occurrence).

Step 3: Handle Non-Matches with IFNA

  • Then, In the formula bar after selecting the cell I4 OR select the cell I4 and type the formula:
    =IFNA(VLOOKUP(“Alice3”, $C$4:$F$9, 4, FALSE), “Not found”)
  • Press enter to get the desired total sales amount for ‘Alice3.
How to create a Finland VLOOKUP table in Excel.

How the formula works

  • VLOOKUP(“Alice3”, $B$4:$F$9, 4, FALSE)

This part of the formula searches for the value “Alice3” within the first column of the range $B$4:$F$9. If it finds a match, it returns the value from the 4th column of the matched row within that range.

  • IFNA(VLOOKUP(“Alice3”, $B$4:$F$9, 4, FALSE), “Not found”)

This part of the formula essentially does the same as the VLOOKUP part above but includes an error-handling mechanism. The IFNA function captures cases where the VLOOKUP function does not find the “Alice3” value and would normally return a #N/A error. Instead of displaying #N/A, IFNA overrides this with the text “Not found”, thus providing a clear and user-friendly response indicating that “Alice3” does not have a corresponding Sales Amount in the dataset.

Method 2 – Utilizing a Helper Column to discover the second match with VLOOKUP in Excel

In our initial approach, we’ll set up a Helper Column to easily locate the Second Match using VLOOKUP in Excel.

Let’s look at our dataset and look for the total sales for salesperson ‘Bob2.’

        Description: Finding second match using VLOOKUP and helper column.

Let’s see it step-by-step:

  • Select cell C4, or beside the first entry in the salesperson entry, and enter the following formula:
    =D4&COUNTIF($D$4:$D4,D4)
  • You will get an output like this:
How to create a column in Excel using VLOOKUP.
  • Here, the formula combines B5 with the COUNTIF result in the helper column, creating a unique lookup value. The COUNTIF function tallies Salesman occurrences. Hit Enter, then employ AutoFill to extend the series as illustrated below.
How to create a column in Excel using VLOOKUP.
  • After selecting the cell I4 or the cell you want the corresponding value, paste the following formula replacing it with your desired cell and cell ranges.
    =VLOOKUP(“Bob”&2,C3:F10,4)
Learn how to create a pivot table in Excel, using VLOOKUP and Second match techniques for effective data analysis.
  • In this VLOOKUP formula, it looks for a match for Bob2 and returns back the sales amount from the column. You can press enter and see the results.
How to create a spreadsheet in Excel using VLOOKUP for the Second Match.

Conclusion

In conclusion, these two straightforward methods offer a simple way to find the second match using VLOOKUP in Excel. Whether utilizing a helper column or an array formula, these approaches enhance efficiency in locating specific data points within your Excel datasets.

Learn more about the VLOOKUP function in Excel with these in-depth guides: