How to VLOOKUP from Multiple Columns with Only One Return in Excel (2 Ways)

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 lookup in 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.

Read more: VLOOKUP with Two Lookup Values and How to Use VLOOKUP for Multiple Columns in Excel

Download Practice Workbook

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

VLOOKUP from Multiple Columns with Only One Return in Excel (2 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 lookup 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 “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”. Let’s complete this task.

i. Using the VLOOKUP from Multiple Columns in the Same Worksheet

Step-1:

To complete the task, take this example like the given one in the screenshot.

Step-2:

Create a table anywhere in the worksheet where you want to get the price for the product “Knife” with ID “M-04”. We know that the VLOOKUP function only lookup for a value one column at a time. So to make the “VLOOKUP” return only one value from multiple columns, we need to make some changes. Create another column named “Lookup Column” which should be the “LEFTMOST” column in the table array. Because the “VLOOKUP” function always searches for value from left to right.

Read more: Excel VLOOKUP to Return Multiple Values Vertically

Step-3:

Apply the “CONCATENATE” function in the column “Lookup_Value” to merge the values in columns “Item ID” and “Product”.

Press “Enter” to get the merged values.

Step-4:

Now, in cell “H5”, apply the “VLOOKUP” function. Insert the values into the function and the final form is,

Where,

• Lookup_value is H3&H4. 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 B4:E17
• Col_index_num is 2
• [range_lookup]: we want the exact match (FALSE)

Press “Enter”. Our Price for the criteria is retrieved.

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

Read more: How to Pull Data from Multiple Worksheets in Excel

Step-1:

Create a table in another worksheet where you want to know the price using the “VLOOKUP” function.

Step-2:

In cell “D5” apply the “VLOOKUP” to return only one value from multiple column lookup.

The final formula is,

Where,

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

Get the price by pressing “Enter”.

2. Using Multiple VLOOKUP to Return Only One value From Multiple Columns

Another effective way to lookup multiple columns and return only one value is to use the multiple “VLOOKUP” functions. This formula is made by Nesting a “VLOOKUP” into another “VLOOKUP” function. We will now get familiar with this technique.

i. Using Multiple VLOOKUP from Multiple Columns in the Same Worksheet

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

Step-1:

In the following example, consider two different tables where the “Item ID”, “Product” and “Price” are given. We will find the price from these columns using the nested “VLOOKUP” formula.

Step-2:

Make another table anywhere in the worksheet where you want to return the only value from multiple columns lookup.

Step-3:

In the cell “I4”, apply the nested “VLOOKUP” function.

The final formula is,

Where,

• Lookup_value is VLOOKUP(H4,B$4:C$17,2,FALSE). Here, we use this second “VLOOKUP” to pull the “Item ID” from the table and then use this ID as a lookup value for the first “VLOOKUP”
• table_array: is “E$4:F$17”.
• Col_index_num is 2
• [range_lookup]: we want the exact match (FALSE)

Press “Enter”. The “VLOOKUP” will not return only one value from multiple columns.

Step-4:

Now apply the same function for the rest of the “Item ID”.

ii. Using Multiple VLOOKUP from Multiple Columns in the Different Worksheets

Now we will perform the same task but here in this case the data tables are in different worksheets.

Read more: VLOOKUP Formula in Excel with Multiple Sheets

Step-1:

Create two Data tables in two different worksheets. For the “W1” worksheet,

And for the “W2” worksheet.

Step-2:

Now create a table in a new worksheet where you want to return value from those multiple columns.

Read more: VLOOKUP to Return Multiple Columns in Excel

Step-3:

In cell “E4” apply the nested “VLOOKUP” function. Insert the values and the final form of the formula is,

Where,

• Lookup_value is VLOOKUP(D4,’W1′!B$4:C$17,2,FALSE). This second “VLOOKUP” will pull the Item ID from the “W1”
• table_array: is ‘W2’!B$4:C$17.
• Col_index_num is 2
• [range_lookup]: we want the exact match (FALSE)

Press “Enter” to return only “Price” from multiple columns lookup.

Step-4:

Select and Drag the mouse to apply the same formula for the rest of the “Item ID”.

Quick Notes

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

Conclusion

The VLOOKUP function is one of the most useful and at the same time one of the most sophisticated functions in Excel. Today we used this amazing function to return only one value from multiple columns lookup. If you have any suggestions or thoughts you are most welcome to comment.

Further Readings:

• VLOOKUP Example Between Two Sheets in Excel
• VLOOKUP and Return All Matches in Excel (7 Ways)
• Use of VLOOKUP in VBA to Find Values from Another Worksheet in Excel
• How to Return the Highest Value Using VLOOKUP in Excel
• VLOOKUP Max of Multiple Values (With Alternative)
• VLOOKUP to Find Duplicates in Two Columns
• 10 Best Practices with VLOOKUP in Excel