How to Perform VLOOKUP with Multiple Rows in Excel (5 Methods)

While working with the Microsoft Excel dataset, sometimes we need to look up unique values from multiple rows. We can easily do that by applying the VLOOKUP function along with the SUM, COUNTIF, INDEX, MATCH, FILTER functions. In this article, we’ll learn five quick and suitable ways to VLOOKUP multiple rows in Excel effectively with appropriate illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


5 Suitable Ways to Use VLOOKUP with Multiple Rows in Excel

Let’s say, we have a dataset that contains information about sales of fruits in several months. The name of the fruits, and their sales in January, February, and March is given in columns B, C, D, and E respectively. Today, we will calculate the total sales of any kind of fruit in several months. Here’s an overview of the dataset for today’s task.

vlookup multiple rows


1. Combine the VLOOKUP and SUM Functions in Multiple Rows

From our dataset, we will find out the total sales of any kind of fruits in several months by applying the VLOOKUP and the SUM functions. Let’s follow the instructions below to learn!

Step 1:

  • First, select cell D18 to apply the SUM and VLOOKUP functions.

Combine the VLOOKUP and SUM Functions in Multiple Rows

  • After that, type the SUM and VLOOKUP functions in the Formula Bar. The SUM and VLOOKUP functions are,
=SUM(VLOOKUP(D17, $B$5:$E$15, {2,3,4}, FALSE))
  • Where D17 is the Lookup value and $B$5:$E$15 is the table_array.
  • {2,3,4} is the col_index_num.
  • FALSE is defining the exact match.
  • Hence, press Enter to get the result.

Step 2:

  • After completing the above process, enter any fruit name in cell D17 to get the total sales. Let’s say, we will enter Apple from our dataset, and press Enter on your keyboard, and we get $361.00 as the output of the SUM and VLOOKUP functions.

Combine the VLOOKUP and SUM Functions in Multiple Rows

Read More: How to VLOOKUP and Return Multiple Values in Excel (8 Methods)


2. Perform the SUMPRODUCT Function to VLOOKUP Multiple Rows in Excel

In this method, we will learn how to find a unique product by using the SUMPRODUCT function in several rows. From our dataset, we will calculate the total sale of blackberries by using the SUMPRODUCT function in several months from different rows. Let’s follow the steps below to learn!

Steps:

  • First, select cell C19 to calculate the total sale of blackberries in several months.

Perform the SUMPRODUCT Function to VLOOKUP Multiple Rows in Excel

  • After selecting cell C19, write down the SUMPRODUCT function in the Formula Bar. the SUMPRODUCT function is,
=SUMPRODUCT((B6=B6:B16)*C6:E16)
  • Where B6 is the lookup value and B6:B16 is the lookup_array.
  • C6:E16 is the sale array.

  • Hence, simply press Enter on your keyboard and you will get $1720.00 as the total sale of blackberries in several months which has been given in the below screenshot.

Perform the SUMPRODUCT Function to VLOOKUP Multiple Rows in Excel

Read More: Excel VLOOKUP Function to Return Min Value from Multiple Hits


3. Apply the COUNTIF Function to VLOOKUP Multiple Rows in Excel

Here we will learn how to look up a value by applying the COUNTIF and VLOOKUP functions. Let’s follow the steps below.

Step 1:

  • First of all, select cell E6 and write down the COUNTIF function. The COUNTIF function is,
=B6&COUNTIF(B6:B$16,B6)
  • Where B6 is the cell reference, and B6:B$16 is the cell array.

Apply the COUNTIF Function to VLOOKUP Multiple Rows in Excel

  • Hence, press Enter on your keyboard and you will get the output of the COUNTIF function, and the output is Blackberry4.

Apply the COUNTIF Function to VLOOKUP Multiple Rows in Excel

  • Now, autoFill the COUNTIF function to the rest of the cells in column E.

Step 2:

  • After that, copy the value of column C and paste it into column F by applying keyboard shortcuts Ctrl + C and Ctrl + V respectively.

Step 3:

  • Further, select cell G6, and type the VLOOKUP function in the Formula Bar. The VLOOKUP function in the Formula Bar is,
=VLOOKUP(E6,E6:F16,2,FALSE)
  • Where E6 is the VLOOKUP value, E6:F16 is the table_array.
  • 2 is the col_index_num.
  • FALSE is defining the exact match.

Apply the COUNTIF Function to VLOOKUP Multiple Rows in Excel

  • Hence, again, press Enter on your keyboard and you will get the output of the VLOOKUP function, and the output is $150.00.

Apply the COUNTIF Function to VLOOKUP Multiple Rows in Excel

  • After completing the above process, similarly, autoFill the VLOOKUP function to the rest of the cells in column G.

Read More: VLOOKUP Partial Match Multiple Values (3 Approaches)


4. Merge the INDEX and MATCH Functions to VLOOKUP with Multiple Rows in Excel

You can also calculate the total sales of a particular fruit by applying the INDEX and MATCH functions. From our dataset, we will calculate the total sales of Orange. Let’s follow the instructions below to learn!

Step 1:

  • Select cell C18 first to calculate the total sales of a particular fruit by applying the INDEX and MATCH functions.

Merge the INDEX and MATCH Functions to VLOOKUP with Multiple Rows in Excel

  • Hence, type the INDEX and MATCH functions in the Formula Bar. The INDEX and MATCH functions in the Formula Bar is,
=SUM(INDEX(C5:E15,,MATCH(C17,B5:B15,0)))
  • Inside the MATCH function, C17 is the cell reference that is matched from B5 to B15, and 0 is applied to get an exact match.
  • Then inside the INDEX function, C5:E15 is the array of cells.

Step 2:

  • After typing both of MATCH and INDEX functions in cell C18, simply press Enter on your keyboard and you will get the total sales of Orange by applying the MATCH and INDEX functions, and the total sales of Orange is $1623.00.

Merge the INDEX and MATCH Functions to VLOOKUP with Multiple Rows in Excel

Read More: VLOOKUP Max of Multiple Values (With Alternative)


5. Apply the FILTER Function to VLOOKUP Multiple Rows in Excel

We can use the FILTER function to lookup values in Excel. Microsoft 365 only has the FILTER function. Please follow the steps below to learn!

Steps:

  • First of all, select cell E17 to apply the FILTER function.

Apply the FILTER Function to VLOOKUP Multiple Rows in Excel

  • Now, write down the FILTER function in the selected cell. The FILTER function is,
=FILTER(B4:D14, C4:C14=E16)
  • Where B4:D14 is the lookup_array, and E16 is the Lookup value.

  • Hence, press Enter on your keyboard, and instantly you will get your desired output that has been given in the below screenshot.

Apply the FILTER Function to VLOOKUP Multiple Rows in Excel


Things to Remember

👉 #N/A error occurs when the reference cell value is not found.

👉 You can use the FILTER function in Microsoft 365.


Conclusion

I hope all of the suitable methods mentioned above to VLOOKUP multiple rows will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo