How to Use VLOOKUP for Multiple Columns in Excel

Formula Using the CHOOSE and VLOOKUP Functions

Today I will talk about MS Excel’s popular and highly used function name VLOOKUP. This function can be used to extra, compare, shift, or search data in any table or range. These functionalities can be used for multiple columns. In this article, I will show how we can use the VLOOKUP function in multiple columns for various purposes in Excel.

Download the Practice Workbook

For method 1, 3, 4, and alternative.

For method 2 only

4 Ways to Use VLOOKUP Function for Multiple Columns

1. Get Values from Multiple Columns Using VLOOKUP Function

Let’s consider that we have a product list with their ID, Name, and Unit Price. There is another table which will be called Statement. In this table, there will be ID, Name, Price, Quantity, and Total Amount. Our task is to generate an auto calculation of the total price in the Statement table if you just enter the product ID. Our formula will extract product names and prices from the product list table and generate a total amount automatically.

Excel VLOOKUP Multiple Columns

[ Note: To generate Total Amount, I have used =D20*E20 this formula and copied it down up to F31]

Step 1: Enter the formula in C20 and press CTRL + SHIFT + ENTER

=VLOOKUP(B20, $B$4:$D$15, {2,3},0)

Formula Explanation:

  • In the VLOOKUP function, the first argument is carrying the data which will be searched in the table. Here B20 is containing the ID which is going to be matched with the product list table ID.
  • $B$4:$D$15 is the table array range where the data will be searched.
  • {2,3} this means we are extracting second and third column values of the matched rows.
  • 0 is defining that we want to get an exact match.
  • If you want to explore more about this function you can check this link

Formula using VLOOKUP function

Step 2: Now copy the formula up to C31

Now copy the formula up to C31

2. VLOOKUP Array on Multiple Columns in Different Workbooks

In this part, we will use the VLOOKUP function’s array formula to get data from multiple columns in different workbooks. Now our dataset will be the same, but the two tables will be in two different workbooks. The name of the workbooks will be Product List Table and Statement Table. We will do the same operation as the previous one but here we will use a different workbook.

Product List Table Workbook

Product List Table Workbook

Statement Table Workbook

Statement Table Workbook

Step 1: Go to the Statement Table workbook and enter the following formula in cell C4 and then press CTRL + SHIFT + ENTER

=VLOOKUP(B4,'[Product List Table.xlsx]Sheet1'!$B$4:$D$15,{2,3},0)

Formula Explanation

  • This formula is the same as the previous method’s formula. The only key difference is that here as the source table is in another file that’s why we had to use the reference of the file using ‘[Product List Table.xlsx]Sheet1’!$B$4:$D$15 part.

Enter formula using VLOOKUP function

Step 2: Now copy the formula up to C15

Now copy the formula up to C15

3. Compare Multiple Columns Using VLOOKUP Function

For this section, let’s consider we have a dataset of employees with their ID and name. There is a table of Old Employees, New Employees, and Current Expected Employees. Now our task is to compare two columns of Old Employees and New Employees Names and identify the matches. Then, we need to compare the Current Expected Employees Names column with the identified matches.

Compare Multiple Columns Using VLOOKUP Function

Step 1: Select the cells from J5 to J15

Select the cells from J5 to J15

Step 1: Enter the following formula in cell J5 and press CTRL + SHIFT + ENTER

=IFERROR(VLOOKUP(IFERROR(VLOOKUP(B5:B15,E5:E15,1,FALSE),""),H5:H15,1,FALSE),"")

Formula Explanation:

  • VLOOKUP(B5:B15, E5:E15,1, FALSE) this part compares the first two names.
  • H5:H15 the range to look up (third name column) against the matching values returned from the comparison of the first and second name columns.
  • 1: the column to return the matching values from.
  • The IFERROR function is used to replace the #N/A with blank cells. If you want to explore more about this function, you can check this link

Formula using VLOOKUP and IFERROR functions

4. VLOOKUP with Multiple Criteria – Using the CHOOSE and VLOOKUP Functions

Here we will see how we can extract data from multiple columns using multiple criteria. Let’s consider we have a dataset of sales information with ID, Name, Month, and Sales. Now our task is to create a new table where we will show all the sales in the column where each column will represent each month.

VLOOKUP with Multiple Criteria – Using the CHOOSE Function

Step 1: Enter the formula in cell I4 and press CTRL + SHIFT + ENTER. Then copy the formula in the whole table

Formula using VLOOKUP and CHOOSE function

Alternative Option

In MS-Excel there is an alternative of the VLOOKUP function named INDEX or MATCH functions. Now we will do the same operation but with a different function (without VLOOKUP)

Return Values from Multiple Columns Using INDEX/MATCH Function

Now let’s consider the same dataset that we have used in the first example. We will find the price of the product using the Name and ID.

Return Values from Multiple Columns Using INDEX/MATCH Function

Step 1: Enter the formula in cell D19 and press Enter

=INDEX(D:D,MATCH(1,(C:C=C19)*(B:B=B19),0))

Formula Explanation

  • Here D:D is the range from where we will extract data.
  • MATCH(1,(C: C=C19)*(B: B=B19) using this we are matching the entered ID and Name with the dataset, and the “1” here refers to TRUE as in return the row number where all the criteria are TRUE.
  • If you want to learn more about this INDEX function, you can visit this link

Formula using INDEX function

Things to Remember

Common Errors When they show
#NA! This error will occur if the formula is an array formula, and you just press Enter. To solve press CTRL+SHIFT+ENTER.
#N/A in VLOOKUP In practice, there are many reasons why you might see this error, including:
  • The lookup value does not exist in the table
  • The lookup value is misspelled or contains extra space.
  • The table range is not entered correctly.
  • You are copying VLOOKUP, and the table reference is not locked.
#VALUE in CHOOSE If index_num is out of range, CHOOSE will return #VALUE
Range or an array constant CHOOSE function will not retrieve values from a range or array constant.
#VALUE in INDEX All ranges must be on one sheet otherwise INDEX returns a #VALUE error.

Conclusion

These are some ways to use the VLOOKUP function for multiple columns in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo