Now we will perform the same task but here in this case the data tables are in different worksheets.
Using Multiple VLOOKUP from Multiple Columns in the Different Worksheets Now apply the same function for the rest of the “Item ID”. The “VLOOKUP” will not return only one value from multiple columns. 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” In the cell “I4”, apply the nested “VLOOKUP” function. Make another table anywhere in the worksheet where you want to return the only value from multiple columns lookup. We will find the price from these columns using the nested “VLOOKUP” formula. In the following example, consider two different tables where the “Item ID”, “Product” and “Price” are given. Using Multiple VLOOKUP from Multiple Columns in the Same Worksheetįirst, we will learn this method where the columns are results both are in the same worksheet. We will now get familiar with this technique. This formula is made by Nesting a “VLOOKUP” into another “VLOOKUP” function. Using Multiple VLOOKUP to Return Only One value From Multiple ColumnsĪnother effective way to lookup multiple columns and return only one value is to use the multiple “VLOOKUP” functions. Click on the “M01” worksheet and select the table array. Insert the values into the function and the final form is, Now, in cell “H5”, apply the “VLOOKUP” function. Read more: Excel VLOOKUP to Return Multiple Values VerticallyĪpply the “CONCATENATE” function in the column “Lookup_Value” to merge the values in columns “Item ID” and “Product”. Because the “VLOOKUP” function always searches for value from left to right. Create another column named “Lookup Column” which should be the “LEFTMOST” column in the table array.
So to make the “VLOOKUP” return only one value from multiple columns, we need to make some changes. We know that the VLOOKUP function only lookup for a value one column at a time. To complete the task, take this example like the given one in the screenshot.Ĭreate a table anywhere in the worksheet where you want to get the price for the product “Knife” with ID “M-04”. Using the VLOOKUP from Multiple Columns in the Same Worksheet Now you need to find out the “Price” of a specific “Product” with a specific “ID”. In your worksheet, you have “Item ID”, “Product Name” and “Price” of the products.
Using the Standard VLOOKUP to Return Only One value From Multiple ColumnsĬonsider a situation where you are working in a supermarket. We will now discuss two different ways to do it. But we can make some changes and can return only one value from multiple columns using VLOOKUP. The common “VLOOKUP” function does not allow its user to do it. Sometimes we need to lookup only one value in multiple columns and return the value. The “ VLOOKUP” or “Vertical Lookup” function takes the input value, searches it in the worksheets, and returns the value matching the input. VLOOKUP from Multiple Columns with Only One Return in Excel (2 Ways)