How to Find Second Match with VLOOKUP in Excel (2 Simple Methods)

Finding specific cell values manually from a range in Excel datasheets is a really inconvenient process. This article will show you the simple methods to Find the Second Match with the Excel VLOOKUP function.

For instance, I’m going to use a sample dataset to help you understand better. The following dataset represents the Salesman, Product, and Net Sales of a company.

How to Find Second Match with VLOOKUP in Excel


Download Practice Workbook

Download the following workbook to practice by yourself.


Introduction to Excel VLOOKUP Function

  • Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • Arguments

lookup_value: The value to look for in the leftmost column of the given table.

table_array: The table in which it looks for the lookup_value in the leftmost column.

col_index_num: The number of the column in the table from which a value is to be returned.

[range_lookup]: Tells whether an exact or partial match of the lookup_value is required. 0 for an exact match, 1 for a partial match. Default is 1 (partial match). This is optional.


2 Simple Methods to Find Second Match with VLOOKUP in Excel

1. Using Helper Column to Find Second Match with VLOOKUP in Excel

In our first method, we’ll create a Helper Column to find Second Match with VLOOKUP in Excel. Here, we’ll find the Net Sales of Wilham2 in this example.

Using Helper Column to Find Second Match with VLOOKUP in Excel

STEPS:

  • First of all, select cell A5 and type the formula:
=B5&COUNTIF($B$5:$B5,B5)

Using Helper Column to Find Second Match with VLOOKUP in Excel

Here, this formula will create a unique look-up value consisting of B5 and the result of the COUNTIF function in the helper column. The COUNTIF function counts the occurrence of Salesman.

  • Next, press Enter and use the AutoFill feature to fill the series like it’s shown below.

Using Helper Column to Find Second Match with VLOOKUP in Excel

  • After that, select cell G5 and type the formula:
=VLOOKUP("Wilham"&2,A5:D10,4)

In this formula, it looks for Wilham2 in the helper column. And it returns the cell value present in the 4th column from the range A5:D10.

  • Finally, press Enter and you’ll get the accurate result.

Read More: How to Use VLOOKUP to Find a Value That Falls Between a Range


2. Finding Second Match with Excel VLOOKUP & IFNA Functions

We can use the Excel VLOOKUP and IFNA functions for getting the second match. Here, we’ll track down the Net Sales of Wilham2.

Finding Second Match with Excel VLOOKUP & IFNA Functions

STEPS:

  • In the beginning, select cell A5 and type the formula:
=B5&COUNTIF($B$5:$B5,B5)

Finding Second Match with Excel VLOOKUP & IFNA Functions

Here, this formula will create a unique look-up value consisting of B5 and the result of the COUNTIF function in the helper column. The COUNTIF function counts the occurrence of Salesman.

  • Next, press Enter and use the AutoFill feature to fill the series like the following picture.

Finding Second Match with Excel VLOOKUP & IFNA Functions

  • Then, select cell G5 and type the formula:
=IFNA(VLOOKUP(F5,A5:D10,4),"")

  • In the end, press Enter and it’ll return the desired output.

🔎 How Does the Formula Work?

➤ VLOOKUP(F5, A5:D10,4)

First, this part of the formula looks for the F5 cell value in the range A5:D10 and it returns the value present in the 4th column.

➤ IFNA(VLOOKUP(F5,A5:D10,4),””)

And subsequently, this part of the formula will just return the result of VLOOKUP(F5, A5:D10,4), but it’ll return a blank cell if there is no value available.

Read More: How to Use Column Index Number Effectively in Excel VLOOKUP Function


Alternative Way to Find Second Match with Array Formula in Excel

We can also find the Second Match without using the Excel VLOOKUP function. For that, we’ll create an Array formula with INDEX, SMALL, and IF functions. Here, we’ll find the Net sales of Simon2.

STEPS:

  • Firstly, select cell G5 and type the formula:
=INDEX(D5:D10,SMALL(IF("Simon"=B5:B10,ROW(B5:B10)-ROW(B5)+1),2))

Alternative Way to Find Second Match with Array Formula in Excel

  • Lastly, press Enter and you’ll see the precise outcome.

🔎 How Does the Formula Work?

➤ ROW(B5:B10)

Firstly, this returns the row numbers of the cells.

➤ IF(“Simon”=B5:B10,ROW(B5:B10)-ROW(B5)+1)

After that, this part of the formula performs a logical test and if true, returns the result of ROW(B5:B10)-ROW(B5)+1.

➤ SMALL(IF(“Simon”=B5:B10,ROW(B5:B10)-ROW(B5)+1),2)

Then, This part of the formula returns the 2nd smallest numeric value.

➤ INDEX(D5:D10,SMALL(IF(“Simon”=B5:B10,ROW(B5:B10)-ROW(B5)+1),2))

In conclusion, it returns the value present in the range D5:D10.

Read More: Lookup Value in a Range and Return in Excel (5 Easy Ways)


Conclusion

Now you will be able to find the Second Match in Excel with the VLOOKUP function. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung

Aung

I'm Aung. Recently I've earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo