VLOOKUP from Multiple Columns with Only One Return in Excel

The VLOOKUP function is a game-changer when you have to retrieve information from a range of data in the same or different worksheets. The Excel VLOOKUP function is remarkably helpful when it comes to searching across worksheets for a definite value. Though this function is powerful it lacks a major feature. Its syntax allows this function for one lookup value at a time. But we can make some modifications to improvise this function to look up multiple columns and return only one value. Today we will discuss how to use the VLOOKUP function to return only one value from multiple columns in Excel.

VLOOKUP from Multiple Columns with Only One Return in Excel: 2 Suitable Ways

The VLOOKUP or “Vertical Lookup” function takes the input value, searches it in the worksheets, and returns the value matching the input. Sometimes we need to look up only one value in multiple columns and return the value. The common VLOOKUP function does not allow its user to do it. But we can make some changes and can return only one value from multiple columns using VLOOKUP. We will now discuss two different ways to do it.

1. Using the Standard VLOOKUP to Return Only One Value from Multiple Columns

Consider a situation where you are working in a supermarket. In your worksheet, you have the “Item ID”, “Product Name” and “Price” of the products. Now you need to find out the “Price” of a specific “Product” with a specific “ID”.

Go through any of the ways below to complete this task.

Read More: 10 Best Practices with VLOOKUP in Excel

1.1 Using the VLOOKUP from Multiple Columns in the Same Worksheet

In this example, say, we want to find the product Knife bearing the ID M-04. You can complete your task in the same worksheet by following the steps below.

📌 Steps:

• First, create a new column named Lookup Column which should be the “LEFTMOST” column in the table array. Because the VLOOKUP function always searches for values from left to right. 
• Afterward, create a table anywhere in the worksheet where you want to get the price for the product “Knife” with ID “M-04”.

• Following, apply the CONCATENATE function in the column “Lookup Column” to merge the values in columns “Item ID” and “Product”.
• To do this, click on cell B5 and insert the following formula.

• Subsequently, press the Enter key to get the merged values.

• Now, use the Fill handle feature below to copy the same formula dynamically and get the lookup column value for every data.

• Afterward, in cell H6, apply the VLOOKUP function. Insert the values into the function and the final form is,

• Subsequently, press Enter.

Thus, we will be able to VLOOKUP from multiple columns with only one return.

Read More: 7 Practical Examples of VLOOKUP Function in Excel

1.2 Using the VLOOKUP from Multiple Columns in the Different Worksheets

Here we will do the same operation but in this case, the data array is in a different worksheet. In this case, the dataset is in the “M01” worksheet and we will apply the formula in the “M02” worksheet to get values. Follow the steps below to do this.

📌 Steps:

• First and foremost, create a table in another worksheet where you want to know the price using the VLOOKUP function.

• Afterward, in cell D5 apply the VLOOKUP function to return only one value from multiple-column lookups. The final formula is,

• Following, press the Enter key.

As a result, we will get the lookup value in a different worksheet from multiple columns with only one return.

2. Using Multiple VLOOKUP Functions to Return Only One value from Multiple Columns

We will make this formula by nesting a VLOOKUP function into another VLOOKUP. We will now get familiar with this technique.

2.1 Using Multiple VLOOKUP from Multiple Columns in the Same Worksheet

First, we will learn this method where the data and results are both in the same worksheet.

In the following example, consider two different tables where one contains “Item ID” and “Product” columns; and the other contains “Product” and “Price”. Now, we want to find the price from these columns using the nested VLOOKUP formula.

Follow the steps below to accomplish this target.

📌 Steps:

• At the very beginning, make a table anywhere in the worksheet where you want to return the only value from multiple columns lookup.

• Afterward, in cell I5, apply the nested VLOOKUP function.

The final formula is,

• Subsequently, press the Enter key. Thus, the VLOOKUP will return only one value from multiple columns.

• Now apply the same function for the rest of the “Item ID”.
• You can also use the Fill handle feature to copy the same formula below dynamically.

As a result, you will get the VLOOKUP result from multiple columns with only one return.

2.2 Using Multiple VLOOKUP from Multiple Columns in Different Worksheets

Now we will perform the same task but here in this case the data tables are in different worksheets. Go through the steps below to learn this process.

📌 Steps:

• Initially, create two Data tables in two different worksheets. For the “W1” worksheet, create the Data Range 1 table.

• Following, create a data table named Data Range 2 in the “W2” worksheet.

• At this time, create a table in a new worksheet where you want to return value from those multiple columns.

• Following, in cell C5 apply the nested VLOOKUP function. Insert the values and the final form of the formula is,

• Subsequently, press Enter to return only “Price” from multiple columns lookup.

• Following, use the fill handle feature to apply the same formula for the rest of the “Item ID”.

Consequently, you will get the desired result in this different worksheet.

How to Apply Excel VLOOKUP with Multiple Column Index Numbers?

Now, say, you need to look up multiple values at a time with a single VLOOKUP function. You can achieve this by using multiple-column index numbers.

Say, you have “Item ID”, “Product”, and “Price” in your given dataset. Now, you want to return both the Product and Price for the M-09 item.

Follow the steps below to do this.

📌 Steps:

• First, create a table in the worksheet where you want to get your results.

• Afterward, select cells G5:H5.

• Following, insert the formula below and press the Ctrl+Shift+Enter keys. You can press just Enter if you are an Excel 365 user.

Thus, you will get multiple lookup values with multiple-column index numbers.

💬 Things to Remember

•  The VLOOKUP function always searches for lookup values from the leftmost top column to the right. This function “Never” searches for the data on the left.
•  If you enter a  value less than “1” as the column index number, the function will return an error.
•  When you select your “Table_Array” you have to use the absolute cell references ($) to “BLOCK” the array.
•  Always use the 4th argument as “FALSE” to get the exact result.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

Conclusion

So, in this article, I have shown you 2 suitable ways to VLOOKUP from multiple columns with only one return in Excel. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

Related Articles

• How to Use VLOOKUP for Multiple Columns in Excel
• How to Use VLOOKUP to Return Multiple Columns in Excel
• How to Use VLOOKUP Function to Compare Two Lists in Excel
• How to Use the VLOOKUP Ascending Order in Excel
• VLOOKUP with Drop Down List in Excel
• How to Use VLOOKUP for Rows in Excel
• How to Use Column Index Number Effectively in Excel VLOOKUP
• Perform VLOOKUP by Using Column Index Number from Another Sheet
• How to Find Column Index Number in Excel VLOOKUP

<< Go Back to Advanced VLOOKUP | Excel VLOOKUP Function | Excel Functions | Learn Excel