Pinterest Pixel

Excel VLOOKUP Multiple Columns

VLOOKUP formula is primarily used to look for a value in the leftmost column of the table... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Excel VLOOKUP Multiple Columns | MyExcelOnline Excel VLOOKUP Multiple Columns | MyExcelOnline

VLOOKUP formula is primarily used to look for a value in the leftmost column of the table and return the corresponding value from another column on the right.

What if you want to VLOOKUP multiple columns at once?

You can use Excel VLOOKUP multiple columns by using an Array Formula!

Without further ado let’s dive into these topics and understand how to use VLOOKUP for 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!” player-type=”default” override-embed=”default”]

Excel VLOOKUP Multiple Columns Syntax

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]))}

Now that you are familiar with the syntax let’s look at an example of how to use Excel VLOOKUP multiple columns!

 

Return Multiple Values

One of the downsides of using VLOOKUP is that it can return value from a single column only.

In this example, we want to find a match for both Item Description and Price. But it won’t be possible to use the basic VLOOKUP syntax.

Excel VLOOKUP Multiple Columns

You can modify the VLOOKUP formula with an array formula and extract both description and price by matching the item code!

 

Follow the step-by-step tutorial on how to VLOOKUP for multiple sheets with example and download this Excel workbook to practice along:

download excel workbookVlookup_Multiple-Columns.xlsx

STEP 1: Select the cells (H8 and I8) where you want to insert the values from multiple columns.

Excel VLOOKUP Multiple Columns

STEP 2: We need to enter the VLOOKUP function in the selected cell:

=VLOOKUP(

Excel VLOOKUP Multiple Columns

STEP 3: We need to enter the first argument – Lookup_value

What is the value to be looked up?

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

=VLOOKUP(G8,

Excel VLOOKUP Multiple Columns

STEP 4: We need to enter the second argument – Table_array

Where is the list of data?

Select the Inventory table, as that is where our formula is going to get both description and price for different item codes.

Make sure you freeze the range by pressing F4!

=VLOOKUP(G15,$B$6:$D$17,

Excel VLOOKUP Multiple Columns

STEP 5: We need to enter the third argument – {Col_index_num1, Col_index_num2}

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

We want to get the description and price. So that will be columns 2 and 3.

=VLOOKUP(G8, $B$6:$D$17, {2,3},

Excel VLOOKUP Multiple Columns

STEP 6: We need to enter the fourth argument – [Range_lookup]

Would it be an approximate match? 

Set this to FALSE or 0 as we want an exact match for the Item code.

=VLOOKUP(G8, $B$6:$D$17, {2,3}, 0)

Excel VLOOKUP Multiple Columns

STEP 7: Press Ctrl + Shift + Enter at the end of the formula to change it into an array function.

Excel VLOOKUP Multiple Columns

Copy-Paste this formula for the remaining item codes mentioned in the Invoice!

Excel VLOOKUP Multiple Columns

 

Return Sum of Multiple Values

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 above and step by step guide below on Excel VLOOKUP multiple columns with a free downloadable Excel workbook to practice ***

 

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(

Excel 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,

Excel 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,

Excel VLOOKUP Multiple Columns

{Col_index_num1, Col_index_num2}

Which columns in the table_array contain 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}, 

Excel 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)

Excel 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.

Excel 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

Conclusion

This completes our tutorial on how to use VLOOKUP to return values from multiple columns at once!

You can learn more about VLOOKUP basics, VLOOKUP with multiple criteria, and VLOOKUP in multiple sheets.

If you like this Excel tip, please share it
Excel VLOOKUP Multiple Columns | MyExcelOnline Excel VLOOKUP Multiple Columns | MyExcelOnline
Founder & Chief Inspirational Officer at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  INDIRECT Function for Dependent Dropdown Lists in Excel

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...