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

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.

creating table

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

creating table

Step-3:

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

Applying formula

Press “Enter” to get the merged values.

Getting result

Step-4:

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

=VLOOKUP(H3&H4, B4:E17,4,FALSE)

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)

Result

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

final result

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.

creating table

Step-2:

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

The final formula is,

=VLOOKUP(B5&C5,’M01′!B4:E17,4,FALSE)

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)

applying formula

Get the price by pressing “Enter”.

final result

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.

creating table

Step-2:

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

Input new table

Step-3:

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

The final formula is,

=VLOOKUP(VLOOKUP(H4,B$4:C$17,2,FALSE),E$4:F$17,2,FALSE)

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)

applyong formula

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

getting result

Step-4:

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

result

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,

creating table

 

And for the “W2” worksheet.

creating table

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

creating table

Step-3:

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

=VLOOKUP(VLOOKUP(D4,’W1′!B$4:C$17,2,FALSE),’W2′!B$4:C$17,2,FALSE)

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)

applying formula

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

getting rresult

Step-4:

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

final result

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:

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo