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 example!

DOWNLOAD EXCEL WORKBOOK

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(

VLOOKUP Multiple Columns

 

STEP 2: The VLOOKUP arguments:

Lookup_value

What is the value to be looked up?

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

=VLOOKUP(G15,

VLOOKUP Multiple Columns

Table_array

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

[Range_lookup]

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.

VLOOKUP Multiple Columns

 

How to VLOOKUP Multiple Columns in Excel

Vlookup_Multiple Columns

 

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Related Excel Tutorials