How to Use VLOOKUP Function on Multiple Rows in Excel

Get FREE Advanced Excel Exercises with Solutions!

Looking for ways to use the VLOOKUP function on multiple rows in Excel? Do not worry, we are here for you. In this article, we will extensively describe how you can use VLOOKUP on multiple rows.

VLOOKUP is an Excel function frequently utilized to locate a particular value within a table and retrieve a matching value from the same row. It can also be used to retrieve values from multiple rows at the same time.

Here, in the following picture, you can see that we have found out the total sales combining 3 rows for the fruit Apple. We have used the VLOOKUP function for this. So let’s dive into the article to find out the ways to do so.

Overview Image for VLOOKUP Multiple Rows


When to Use VLOOKUP for Multiple Rows in Excel?

Below, we are providing some cases when it is necessary to use VLOOKUP for multiple rows.

  • Finding Multiple Matching Values: VLOOKUP can help you find many matching values in a big collection of data. For instance, imagine you have a chart with sales information and want to find all the sales made by a particular salesperson. With VLOOKUP, you can extract all the related rows easily.
  • Retrieving Specific Information from a Table: VLOOKUP is handy when you want to find certain information from a big table. For instance, suppose you have a list of employees and want to know their salary, department, and job title. You can use VLOOKUP to get this information for each employee.
  • Searching Data in Large Dataset: VLOOKUP is a useful tool that saves time when dealing with big sets of data. Instead of searching through the data to find specific information, VLOOKUP lets you easily get the information you need.

How to Use VLOOKUP Function on Multiple Rows in Excel: The Quick Easy Way

In the following dataset, you can see that we have the Fruit, January, February, and March columns. Using this dataset, we will use VLOOKUP on multiple rows in Excel and find the total according to lookup criteria.

Here, we will mainly find the total sales for the fruit Apple. Thus, we will find the sum of sales for the months of January, February, and March using the VLOOKUP function.

To do so, we have used Microsoft 365. You can use any available Excel version.

Dataset to Use VLOOKUP with Multiple Rows

  • First of all, we will type the following formula using the SUM and VLOOKUP functions in cells D18:E18.
=SUM(VLOOKUP(D17, $B$5:$E$15, {2,3,4}, FALSE))
  • After that, we will press ENTER.

As a result, you can see the outcome in cells D18:E18.

Note: Here, each item is presented only once in the Fruit column. This is because the above formula only works when the required item is presented only once in the column.

Combining SUM and VLOOKUP Functions

Formula Breakdown

SUM(VLOOKUP(D17, $B$5:$E$15, {2,3,4}, FALSE))

  • The SUM function adds up a range of cells or values.
  • The VLOOKUP function looks up a value in the first column of a table or range, and returns a value in the same row from a column you specify.
  • D17 is the value being looked up by VLOOKUP.
  • $B$5:$E$15 is the range where VLOOKUP looks for the value specified in D17.
  • {2,3,4} specifies the columns from which to return values. In this case, the second, third, and fourth columns of the range $B$5:$E$15 will be returned.
  • FALSE is an optional parameter that tells VLOOKUP to only return exact matches of the lookup value.
  • =SUM(VLOOKUP(D17, $B$5:$E$15, {2,3,4}, FALSE)) → becomes
    • Output: $361

An Alternative to Excel VLOOKUP with Multiple Rows Using INDEX-MATCH Formula

Here, we will use the combination of the SUM, INDEX, and MATCH functions to VLOOKUP multiple rows in Excel.

Here, we will calculate the total sales of Orange using these functions.

  • First, we will type the following formula in cells D18:E18.
=SUM(INDEX(C5:E15,,MATCH(D17,B5:B15,0)))
  • Then, we will press ENTER.

As a result, you can see the outcome in cells D18:E18.

Use of SUM, INDEX and MATCH Functions

Formula Breakdown

SUM(INDEX(C5:E15,,MATCH(D17,B5:B15,0)))

  • The SUM function adds up a range of cells or values.
  • The INDEX function returns a value or reference to a cell at the intersection of a specified row and column in a given range.
  • C5:E15 is the range from which the values will be selected by INDEX.
  • The MATCH function returns the position of a value within an array. In this case, it will find the position of the value in D17 within the range B5:B15.
  • D17 is the value being matched by MATCH.
  • B5:B15 is the range within which MATCH is searching for a match to D17.
  • 0 is an optional parameter that tells MATCH to look for an exact match.
  • SUM(INDEX(C5:E15,,MATCH(D17,B5:B15,0))) → becomes
    • Output: $1623

Read More: Find Max of Multiple Values by Using VLOOKUP Function in Excel


How to Vlookup and Return Multiple Rows in Excel Using FILTER Function

Here, we will use the FILTER function to return multiple rows in Excel. One thing must be remembered, that the FILTER function is only available in Microsoft 365.

Using the FILTER function for our dataset, we will return the rows with Vegetables in them.

  • In the beginning, we will type the following formula in cell D18.
=FILTER(B5:D15,C5:C15=D17)

The FILTER function filters a range of values in a dataset based on certain criteria.

  • After that, we will press ENTER.

As a result, you can see the outcome in cells D18:F19.


How to Use VLOOKUP Based on Multiple Criteria in Excel

Here, we will use VLOOKUP based on multiple criteria.

For our dataset, we have set the criteria as Mike and Charger in cells E17 and E18. Now, we will find out Sales in January based on these criteria.

  • First of all, we will insert a Helper Column.
  • We will type the following formula in cell B5.
=C5&" "&D5
  • We will drag down the formula with the Fill Handle tool.

Therefore, you can see the complete Helper Column.

Writing Formula for Helper Column

  • After that, we will type the following formula in cell E19.
=VLOOKUP(E17&" "&E18, B5:E15, 4, FALSE)
  • Then, press ENTER.

As a result, you can see the result in cell E19.

VLOOKUP Based on Multiple Criteria

Read More: How to VLOOKUP Multiple Values in One Cell in Excel


How to VLOOKUP in Both Rows and Columns in Excel (Two-Way Lookup)

Here, we will use the VLOOKUP and MATCH functions to find out the Sales of Orange for the month of February. Thus, we have to VLOOKUP from rows and columns, which is a two-way lookup.

  • In the first place, we will type the following formula in cells D19:E19.
=VLOOKUP(D17, B5:E15, MATCH(D18, B4:E4, 0), FALSE)
  • After that, press ENTER.

Therefore, you can see the result in cells D19:E19.

Using VLOOKUP in Row and Column


How to Use VLOOKUP to Return Nth Match in Excel

If you have a dataset, where you have repetitive matches, and you want to return Nth match, you can do so using the VLOOKUP function.

In the following dataset, you can see that we have repetitive Customer Names. Now, we will find out the Product Purchased for customer Mike for the 3rd match.

You can see the Customer Name in cell D17, and the Instance in cell D18.

Now, to find the Product Purchased we need to insert an Instances column.

=C5&COUNTIF($C$5:C5, C5)

The COUNTIF function calculates a range of cells based on criteria.

  • At this point, press ENTER.

As a result, you can see the outcome in cell B5.

  • Now, we will drag down the formula with the Fill Handle tool.

As a result, you can see the complete Instances column.

Applying VLOOKUP to Find Nth Instances

  • After that, we will type the following formula in cell D19.
=VLOOKUP(D17&D18, B5:D15, 3, FALSE)
  • After that, press ENTER.

Therefore, you can see the result in cell D19.

Nth instance after applying VLOOKUP


Things to Remember

  • You must be sure of what value you are searching for.
  • Insert a Helper column whenever you want to find the Nth match or use VLOOKUP for multiple criteria.
  • Check function syntax properly.

Download Practice Workbook

You can download the Excel file from the link below and practice the explained methods.


Conclusion

In this article, we extensively describe how to use the VLOOKUP function on multiple rows in Excel. We describe an easy and quick way for this.

In addition to this, we describe how you can return multiple rows using the FILTER function.

Also, we describe how you can use VLOOKUP based on multiple criteria, in rows and columns (two ways lookup), and to return Nth match.

Thank you for going through the article. We hope it was helpful. If you have any queries or suggestions, please let us know in the comment section.


Related Articles


<< Go Back to VLOOKUP Multiple Values | Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo