What does it do?

Searches for a value in the first column of a table array and returns the sum of values in the same row from other columns (to the right) in the table array.

Formula breakdown:

{=SUM(VLOOKUP(lookup_value, table_array, {col_index_num1,col_index_num2}, [range_lookup]))}

What it means:

{=SUM(VLOOKUP(this value, in this list, {and sum the value in this column, with the value in this column}, Exact Match/FALSE/0]))}

The VLOOKUP function can be combined with other functions such as the Sum, Max, or Average to calculate values in multiple columns.  As this is an array formula, to make it work we simply need to press CTRL+SHIFT+ENTER at the end of the formula.  A very powerful feature for any serious analyst!

See how easy it is to implement in less than 1 minute with this VLOOKUP for multiple columns example!

Want to learn more about how to use VLOOKUP to Sum Multiple Columns?

*** Watch our video and step by step guide below on Excel VLOOKUP multiple columns with a free downloadable Excel workbook to practice ***

Watch on YouTube and give it a thumbs up 👍


We want to get the total number of units for Laptop (16,700 + 18,700 units).

STEP 1: We need to enter the VLOOKUP function in a blank cell:


VLOOKUP Multiple Columns


STEP 2: The VLOOKUP arguments:


What is the value to be looked up?

Select the cell that contains the item name, which is Laptop.


VLOOKUP Multiple Columns


Where is the list of data?

Select the Units Sold table, as that is where our formula is going to get the unit numbers.

=VLOOKUP(G15, B14:D17,

VLOOKUP Multiple Columns

{Col_index_num1, Col_index_num2}

Which columns in the table_array contains the data you want to return?

We want to get the unit numbers of Years 2013 and 2014. So that will be columns 2 and 3.

=VLOOKUP(G15, B14:D17, {2,3}, 

VLOOKUP Multiple Columns


Would it be an approximate match? 

Set this to FALSE as we want an exact match for Laptop.

=VLOOKUP(G15, B14:D17, {2,3}, FALSE)

VLOOKUP Multiple Columns


STEP 3: Now wrap the formula with the SUM formula as we want to get the total number of sold units for Laptop.

=SUM(VLOOKUP(G15, B14:D17, {2,3}, FALSE))

Ensure you are pressing CTRL+SHIFT+ENTER as we want to calculate this as an array formula.

VLOOKUP Multiple Columns

Do the exact same formula for Max Units and Average Units, by changing the SUM Formula with the MAX Formula and Average Formula respectively.

excel vlookup multiple columns






If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn