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

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

- Select cell
**A1.** - Write this formula:
**=B2&C2** - Press the
**ENTER**button. - Drag the Fill Handle from cell
**A2**to**A8** - Then again, select a cell
**B12**. - Copy this formula:
**=VLOOKUP(B10&B11, A1:D8, 4, FALSE))** - 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:

- Select cell
**A1.** - Write this formula:
**=B2&C2** - Press the
**ENTER**button. - From cell
**A1**through**A8**, drag the Fill Handle. - Again, select a cell
**B12**. - Copy this formula:
**=VLOOKUP(B10&B11, A1:D8, 4, FALSE))** - To view the outcome, press
**ENTER.**

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

## 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:

- Choose cell
**A1.** - Copy this formula:
**=CONCATENATE(B2,” “,C2)** - Hit
**ENTER**to see the result. - Drag the Fill Handle from cell
**B2**to**B8**. - Again, select a cell
**B12**. - Write this formula:
**=VLOOKUP(CONCATENATE(B10,” “,B11),A1:D8,4,FALSE)** - To view the outcome, press
**ENTER.**

**Final Result**

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

## 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:

- Pick cell
**A1.** - Type this formula:
**=TEXTJOIN(” “, TRUE, B2, C2)** - Press the
**ENTER**button. - Drag the Fill Handle from cell
**B2**to**B8**. - After that, select a cell
**B12**. - Write this formula:
**=VLOOKUP(TEXTJOIN(” “, TRUE, B10, B11), A1:D8, 4, FALSE)** - Press the
**ENTER**button.

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

## 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:

- Select a cell
**A1.** - Use this formula:
**=INDEX(D2:D8, MATCH(1, (B10=B2:B8) * (B11=C2:C8), 0))** - Press the
**ENTER**button to see the result.

**Final Result**

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

## 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])**

- Searches for a value in the first column of a table and returns a value in the same row from a specified column.
- Requires the lookup column to be on the left side of the return column.
- Limited to vertical lookups and only allows you to look up a value to the right.
**VLOOKUP**is simpler to use but has limitations in terms of flexibility.

**INDEX-MATCH**:

Syntax:

**=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))**

- Combines the
**INDEX**and**MATCH**functions. - Offers more flexibility as it can perform both horizontal and vertical lookups.
- Does not require the lookup column to be on the left side of the return column.
- Allows for dynamic lookups, making it more versatile for complex scenarios.
**INDEX-MATCH**is more versatile, allowing for both horizontal and vertical lookups and dynamic matching.**INDEX-MATCH**is generally preferred for more complex scenarios and when data structures may change.