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.

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.

Find Second Match with VLOOKUP in Excel: 2 Simple Methods

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

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

STEPS:

• First of all, select cell A5 and type the formula:

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 a Salesman.

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

• After that, select cell G5 and type the formula:

In this formula, it looks for Wilham2 in the helper column. 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 Search Text in Excel

2. Finding Second Match with Excel VLOOKUP & IFNA Functions

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

STEPS:

• In the beginning, select cell A5 and type the formula:

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.

• Then, select cell G5 and type the formula:

• 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 Apply Double VLOOKUP in Excel

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:

• 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: How to Use VLOOKUP Function with Exact Match in Excel

Download Practice Workbook

Download the following workbook to practice by yourself.

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

• How to Use VLOOKUP with Two Lookup Values in Excel
• How to Use VLOOKUP to Find Duplicates in Two Columns
• VLOOKUP and Return All Matches in Excel
• VLOOKUP Fuzzy Match in Excel
• Excel VLOOKUP to Find Last Value in Column
• How to Apply VLOOKUP by Date in Excel
• Return the Highest Value Using VLOOKUP Function in Excel
• VLOOKUP with Numbers in Excel

<< Go Back to VLOOKUP a Range | Excel VLOOKUP Function | Excel Functions | Learn Excel