Can you use VLOOKUP to sum values? - Excel Tips and Tricks

Learn how to perform conditional summation using VLOOKUP, also know as VLOOKUP within a SUM function.
I have seen people asking about how do I do a VLOOKUP with Sumif in Excel?, or can you do a VLOOKUP in conditional formatting? And can you do a VLOOKUP with multiple conditions? With this video, you will also be able to know the difference between VLOOKUP and Sumifs? And answer the question of can you use VLOOKUP to sum values? And also can I use a VLOOKUP in conditional formatting?

VLOOKUP() function stands for "Vertical Lookup." It's used to search for a value in the leftmost column of a range (table), and when found, it returns a corresponding value from a specified column within the same row.

Create Drop Down List
1) Select B1
2) Data ~ Data Tools ~ Data Validation
3) List
4) Source set to "A4:A29"
5) OK

Get All Sales Data For Person
=VLOOKUP(B1,A4:E29,{2,3,4,5},0)

Sum Sales For Person
=SUM(VLOOKUP(B1,A4:E29,{2,3,4,5},0))

This is the breakdown of the formula.

=SUM(VLOOKUP(B1,A4:E29,{2,3,4,5},0))

VLOOKUP(B1, A4:E29, {2,3,4,5}, 0): This is the VLOOKUP function being used to retrieve values based on the value in cell B1.

B1: This is a cell reference. It's the cell that contains the value you want to look up in the data.

A4:E29: This is the range of data where Excel will search for the value in B1. It includes cells from row 4 to row 29 and columns A to E. Each row represents a different record or entry, and columns B, C, D, and E contain additional information related to each entry.

{2,3,4,5}: These are the column indices within the table array (A4:E29) from which you want to retrieve values. This notation specifies columns 2, 3, 4, and 5 (columns B, C, D, and E). So, the formula will return values from these columns.

0: This is the "range_lookup" parameter. When set to 0, it indicates an exact match lookup. Excel will only return a result if it finds an exact match for the value in B1 within the leftmost column of the table array.

SUM(VLOOKUP(...)): This wraps the VLOOKUP function in the SUM function. It instructs Excel to add up the values retrieved by the VLOOKUP function.

So, when you put it all together, the formula is performing the following steps. It searches for the value in B1 within the leftmost column of the table A4:E29. Once it finds a match, it retrieves values from columns 2, 3, 4, and 5 in the same row as the match. These retrieved values are then added up using the SUM function, providing a single total. In essence, the formula is finding related information from the same row in the data table and then calculating the sum of these values.


conditional summation using VLOOKUP,VLOOKUP within a SUM function,How do I do a VLOOKUP with Sumif in Excel?,Can you do a VLOOKUP in conditional formatting?,Can you do a VLOOKUP with multiple conditions?,What is the difference between VLOOKUP and Sumifs?,