How to Vlookup from Multiple Columns with Only One Return in Excel

ByRhidi Barma Updated on Excel Formulas

You can use the VLOOKUP function to return only one value from multiple columns. Here’s how:

  1. Select cell A1.
  2. Write this formula:=B2&C2
  3. Press the ENTER button.
  4. Drag the Fill Handle from cell A2 to A8
  5. Then again, select a cell B12.
  6. Copy this formula: =VLOOKUP(B10&B11, A1:D8, 4, FALSE))
  7. Hit ENTER to see the result.

This formula searches column A in the range A1:D8 for the sum of the values of B10 and B11, and when it finds a match, it returns the equivalent value from column D.

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

You can utilize the VLOOKUP function to search across multiple columns and retrieve a single value. However, to do so, you’ll need to create a helper column first. In this helper column, you’ll perform the concatenation of values from multiple columns using the ampersand (&) operator.

Syntax

=VLOOKUP(Lookup_value,table_array,row_index_number,[range_lookup])

Formula

=VLOOKUP(B10&B11, A1:D8, 4, FALSE))

Formula Breakdown

This formula is an Excel VLOOKUP function that searches for a specific value within the range A1:D8.

  • It concatenates the values in cells B10 and B11 using the “&” operator, creating a single search key. The “4” indicates that it should return the value from the fourth column (column D) of the range A1:D8 when it finds a match.
  • The “FALSE” argument signifies an exact match, so it will return a result only if it finds the concatenated value in B10 and B11 exactly within the first column of the search range (column A).
  • In summary, it looks for the combined value of B10 and B11 in column A of the range A1:D8 and returns the corresponding value from column D when it discovers a match.

Use VLOOKUP to find a single value from multiple columns. Here’s how:

  1. Select cell A1.
  2. Write this formula:=B2&C2
  3. Press the ENTER button.
  4. From cell A1 through A8, drag the Fill Handle.Create a helper column using the ampersand (&)
  5. Again, select a cell B12.
  6. Copy this formula: =VLOOKUP(B10&B11, A1:D8, 4, FALSE))
  7. To view the outcome, press ENTER. Using the VLOOKUP function to retrieve values from multiple columns with only one return.

Final Result

Ultimately, by searching for “Google” in column B and “Pixel 8” in column C, the desired result can be located in column D.

The final output of using the VLOOKUP function to retrieve values from multiple columns with only one return.

2. Return Only One Value from Multiple Columns Using the Combination of CONCATENATE and VLOOKUP Functions

To retrieve a single value from multiple columns in Excel, consider utilizing the CONCAT function in conjunction with VLOOKUP. The CONCAT function’s role is to combine two or more values seamlessly.

Syntax

=VLOOKUP(CONCATENATE([text_1],[text_2],..),table_array,row_index_number,[range_lookup])

Formula

=VLOOKUP(CONCATENATE(B10," ",B11),A1:D8,4,FALSE)

Formula Breakdown

It is an Excel function that performs a VLOOKUP search within a specified range (A1:D8).

  • It first combines the values in cells B10 and B11 using the CONCATENATE function with a space character between them, creating a single search key, which effectively joins the contents of B10 and B11 into a single string. The “4” in the formula indicates that it should return the value from the fourth column (column D) of the range A1:D8 when a match is found.
  • The “FALSE” argument signifies that it should return an exact match.

In essence, this formula searches for the combined value of B10 and B11, separated by a space, in the first column of the range A1:D8 and retrieves the corresponding value from column D when it encounters a match.

You can utilize the combination of CONCATENATE and VLOOKUP functions to return only one value from multiple columns. The process is given below:

  1. Choose cell A1.
  2. Copy this formula:=CONCATENATE(B2,” “,C2)
  3. Hit ENTER to see the result.
  4. Drag the Fill Handle from cell B2 to B8.Using CONCATENATE function to create a helper column
  5. Again, select a cell B12.
  6. Write this formula: =VLOOKUP(CONCATENATE(B10,” “,B11),A1:D8,4,FALSE)
  7. To view the outcome, press ENTER. Using VLOOKUP and CONCATENATE functions to VLOOKUP multiple columns with one return

Final Result

Finally, we find our desired output using this combination of CONCATENATE and VLOOKUP functions.

The final output of using VLOOKUP and CONCATENATE functions to retrieve data from multiple columns with a single return

3. Using the Combination of TEXTJOIN and VLOOKUP Functions to Return Only One Value from Multiple Column

I can employ a combination of the TEXTJOIN and VLOOKUP functions to retrieve a single value from multiple columns. The TEXTJOIN function is used to merge text from various ranges or strings, with an option to add a specified delimiter between each text value. If the delimiter is set as an empty text string, this function essentially concatenates the ranges.

Syntax

=VLOOKUP(TEXJOIN (delimiter, ignore_empty, text1, [text2],..),table_array,row_index_number,[range_lookup])

Formula

=VLOOKUP(TEXTJOIN(" ", TRUE, B10, B11), A1:D8, 4, FALSE)

Formula Breakdown

This is an Excel function that utilizes the VLOOKUP to search for a specific value within a designated range (A1:D8).

  • It first uses the TEXTJOIN function to concatenate the values in cells B10 and B11, separated by a space, effectively creating a single search key.
  • The “4” in the formula indicates that it should return the value from the fourth column (column D) of the range A1:D8 when a match is found. The “FALSE” argument signifies that it should return an exact match.

In summary, this formula searches for the combined value of B10 and B11, separated by a space, within the first column of the range A1:D8 and retrieves the corresponding value from column D when it identifies a match.

Combine TEXTJOIN and VLOOKUP functions to get a single value from multiple columns. Here’s how you can do it:

  1. Pick cell A1.
  2. Type this formula:=TEXTJOIN(” “, TRUE, B2, C2)
  3. Press the ENTER button.
  4. Drag the Fill Handle from cell B2 to B8.Using TEXTJOIN function to create a helper column
  5. After that, select a cell B12.
  6. Write this formula: =VLOOKUP(TEXTJOIN(” “, TRUE, B10, B11), A1:D8, 4, FALSE)
  7. Press the ENTER button.Using VLOOKUP and TEXTJOIN functions to VLOOKUP multiple columns with one return

Final result

Finally, when we search for “Huawei” in column B and “Mate 30” in column C, the TEXTJOIN function first combines “Huawei” and “Mate 30” with a space between them. Next, it looks for this combined value in the A1:D8 range to find the price of the Huawei Mate 30.

The final output of using VLOOKUP and TEXTJOIN functions to VLOOKUP multiple columns with one return

4. Using INDEX and MATCH Functions to Return Only One Value from Multiple Columns

To extract a single value from multiple columns, you can utilize the INDEX and MATCH functions. The MATCH function is responsible for locating specified values within cells and ranges, while the INDEX function retrieves the value at a specified location within a given range.

Syntax

=INDEX(array, MATCH(lookup_value, lookup_array, match_type))

Formula

=INDEX(D2:D8, MATCH(1, (B10=B2:B8) * (B11=C2:C8), 0))

Formula Breakdown

  • It uses the MATCH function to find the position of a 1 within an array created by comparing the values in cell B10 with those in the range B2:B8 and the values in cell B11 with those in the range C2:C8.
  • The resulting array will contain either 1 or 0, depending on whether the corresponding conditions are met. The MATCH function then identifies the position of the first occurrence of “1” in this array.
  • The INDEX function is subsequently employed to return the value from the range D2:D8 located at the position specified by the MATCH function, effectively extracting the desired value from these columns based on the conditions met in columns B and C.

Merge INDEX and MATCH functions to extract a single value from multiple columns. Here’s a simple guide:

  1. Select a cell A1.
  2. Use this formula:=INDEX(D2:D8, MATCH(1, (B10=B2:B8) * (B11=C2:C8), 0))
  3. Press the ENTER button to see the result.Using INDEX MATCH functions to retrieve values from multiple columns with a single return

Final Result

I have successfully obtained precise results by using the INDEX and MATCH functions in combination.

The final output of using INDEX and MATCH functions to retrieve values from multiple columns with a single return

Conclusion

In this article, I have presented four methods for extracting a single value from multiple columns using Vlookup. So, when you encounter problems trying to retrieve results from multiple columns, you can employ these four methods.

Frequently Asked Questions

Can you VLOOKUP multiple columns at once?

No, the VLOOKUP function in Google Sheets can only look up and retrieve values from one column at a time. If you need to retrieve values from multiple columns, you would need to use multiple VLOOKUP functions for each column separately.

Alternatively, you can use other functions like INDEX and MATCH together to achieve a similar result by looking up values across multiple columns.

How do I lookup multiple values and return one value in Excel?

To lookup multiple values and return a corresponding value for each, you can use an array formula. For example:

=VLOOKUP(lookup_value, table_array, {col1, col2, col3}, 0)

Press Ctrl+Shift+Enter after typing the formula to make it an array formula.

This way, you can use VLOOKUP to find and return one value for each of the multiple lookup values in Excel.

How to use 2 VLOOKUPs in 1 formula?

You can nest two VLOOKUP functions in one Excel formula. Here’s a general guide:

=VLOOKUP(lookup_value, table_array1, col_index_num1, [range_lookup1]) + VLOOKUP(lookup_value, table_array2, col_index_num2, [range_lookup2])

This formula combines the results of two VLOOKUP functions. Adjust the parameters according to your specific data and requirements.

What is the difference between VLOOKUP and index match?

VLOOKUP and INDEX-MATCH are both Excel functions used for lookup purposes, but they differ in their approach:

VLOOKUP:

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  1. Searches for a value in the first column of a table and returns a value in the same row from a specified column.
  2. Requires the lookup column to be on the left side of the return column.
  3. Limited to vertical lookups and only allows you to look up a value to the right.
  4. VLOOKUP is simpler to use but has limitations in terms of flexibility.

INDEX-MATCH:

Syntax:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
  1. Combines the INDEX and MATCH functions.
  2. Offers more flexibility as it can perform both horizontal and vertical lookups.
  3. Does not require the lookup column to be on the left side of the return column.
  4. Allows for dynamic lookups, making it more versatile for complex scenarios.
  5. INDEX-MATCH is more versatile, allowing for both horizontal and vertical lookups and dynamic matching.
  6. INDEX-MATCH is generally preferred for more complex scenarios and when data structures may change.
5/5 - (2 votes)

Data Analyst

Howdy! I'm Rhidi Barma. I'm a professional Excel user. I've completed my graduation from Jahangirnagar University, Bangladesh. I've been working with Microsoft Excel since 2015. I love writing blogs on MS Excel tips & tricks, data analysis, business intelligence, capital market, etc. I love to spend my leisure time reading books and watching movies. To know more about me, you can follow me on social media platforms. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *