Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


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

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

Sample Dataset to VLOOKUP from Multiple Columns with Only One Return

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


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

Lookup Column

  • Following, apply the CONCATENATE function in the columnLookup Column” to merge the values in columns “Item ID” and “Product”.
  • To do this, click on cell B5 and insert the following formula.
=CONCATENATE(C5,D5)
  • Subsequently, press the Enter key to get the merged values.

Use the CONCATENATE Function to Extract Lookup Column Data

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

Lookup Column Values to VLOOKUP from Multiple Columns with Only One Return

  • Afterward, in cell H6, apply the VLOOKUP function. Insert the values into the function and the final form is,
=VLOOKUP(H4&H5,B5:E16,4,FALSE)
  • Subsequently, press Enter.

Standard VLOOKUP Formula to VLOOKUP from Multiple Columns with Only One Return

🔎 Formula Breakdown:

  • Lookup_value is H4&H5. We use this Concatenate Operator (“&”) to help the VLOOKUP function to search in columns “Item ID” and “Product” simultaneously and return only one value.
  • table_array: is B5:E16.
  • Col_index_num is 4.
  • [range_lookup]: we want the exact match (FALSE).

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


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.

Created Table to Insert Formula

  • Afterward, in cell D5 apply the VLOOKUP function to return only one value from multiple-column lookups. The final formula is,
=VLOOKUP(B5&C5,'M01'!B5:E16,4,FALSE)
  • Following, press the Enter key.

Formula to VLOOKUP from Multiple Columns with Only One Return in Different Worksheet

🔎 Formula Breakdown:

  • Lookup_value is B5&C5.
  • table_array: is ‘M01’!B5:E16. Click on the “M01” worksheet and select the table array.
  • Col_index_num is 4.
  • [range_lookup]: we want the exact match (FALSE).

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


Similar Readings


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.

Sample Dataset to Apply Nested VLOOKUP Formula to VLOOKUP from Multiple Columns with Only One Return

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.

Created Table to Insert Formula to VLOOKUP from Multiple Columns with Only One Return in Excel

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

The final formula is,

=VLOOKUP(VLOOKUP(H5,B$5:C$16,2,FALSE),E$5:F$16,2,FALSE)
  • Subsequently, press the Enter key. Thus, the VLOOKUP will return only one value from multiple columns.

Nested VLOOKUP Function to VLOOKUP from Multiple Columns with Only One Return in Excel

🔎 Formula Breakdown:

  • Lookup_value is VLOOKUP(H5,B$5:C$16,2,FALSE). Here, we use this second VLOOKUP to pull the “Product” from the table and then use this as a lookup value for the first VLOOKUP.
  • table_array: is “E$5:F$16”.
  • Col_index_num is 2
  • [range_lookup]: we want the exact match (FALSE)
  • 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.

Nested VLOOKUP Function to Get All the Lookup Values from Multiple Columns with Only One Return

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

Read More: How to Use Nested VLOOKUP in Excel (3 Criteria)


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 Data Range 1 table.

Data Range 1

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

Data Range 2

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

Table to Apply Nested VLOOKUP Function to VLOOKUP from Multiple Columns with Only One Return

  • Following, in cell C5 apply the nested VLOOKUP function. Insert the values and the final form of the formula is,
=VLOOKUP(VLOOKUP(B5,'W1'!B$5:C$16,2,FALSE),'W2'!B$5:C$16,2,FALSE)
  • Subsequently, press Enter to return only “Price” from multiple columns lookup.

Nested VLOOKUP Function to Lookup Value from Multiple Columns with Only One Return in Different Worksheet

🔎 Formula Breakdown:

  • Lookup_value is VLOOKUP(B5,’W1′!B$5:C$16,2,FALSE). This second VLOOKUP will pull the Product from the “W1” sheet.
  • table_array: is ‘W2’!B$5:C$16.
  • Col_index_num is 2
  • [range_lookup]: we want the exact match (FALSE)
  • Following, use the fill handle feature to apply the same formula for the rest of the “Item ID”.

All LOOKUP Values with Nested VLOOOKUP Function

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.

Sample Dataset to Apply Multiple Column Index Numbers in VLOOKUP

Follow the steps below to do this.

📌 Steps:

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

Create Table to Apply Formula

  • Afterward, select cells G5:H5.

Select Cells to Apply VLOOKUP Formula with Multiple Column Index Numbers

  • Following, insert the formula below and press the Ctrl+Shift+Enter keys. You can press just Enter if you are an Excel 365 user.
=VLOOKUP(F5,B5:D16,{2,3},FALSE)

VLOOKUP Formula with Multiple Column Index Numbers

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

Applied VLOOKUP 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.

Read More: VLOOKUP Example Between Two Sheets in Excel


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.

And, visit ExcelDemy to learn more things about Excel! Have a nice day!


Further Readings

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo