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

## Download Practice Workbook

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

## 2 Suitable Ways to VLOOKUP from Multiple Columns with Only One Return in Excel

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 look up 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 the **“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”**.

Go through any of the ways below to complete this task.

### 1.1 Using the VLOOKUP from Multiple Columns in the Same Worksheet

In this example, say, we want to find the product **Knife** bearing the ID **M-04**. You can complete your task in the same worksheet by following the steps below.

**📌 Steps:**

- First, create a new column named
which should be the*Lookup Column***“LEFTMOST”**column in the**table array**. Because the**VLOOKUP**function always searches for values from left to right. - Afterward, create a table anywhere in the worksheet where you want to get the price for the product
**“Knife”**with ID**“M-04”**.

- Following, apply the
**CONCATENATE function**in the column**“**to merge the values in columns*Lookup Column”*and*“Item ID”**“Product”*. - To do this, click on
**cell B5**and insert the following formula.

`=CONCATENATE(C5,D5)`

- Subsequently, press the
**Enter**key to get the merged values.

- Now, use the
**fill handle**feature**below**to copy the same formula dynamically and get the lookup column value for every data.

- Afterward, in
**cell H6**, apply the**VLOOKUP function**. Insert the values into the function and the final form is,

`=VLOOKUP(H4&H5,B5:E16,4,FALSE)`

- Subsequently, press
**Enter**.

**🔎 Formula Breakdown:**

**Lookup_value**is**H4&H5**. 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**B5:E16.****Col_index_num**is**4.****[range_lookup]:**we want the exact match**(FALSE).**

Thus, we will be able to **VLOOKUP** from multiple columns with only one return.

#### 1.2 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. In this case, the dataset is in the “** M01”** worksheet and we will apply the formula in the “

**worksheet to get values. Follow the steps below to do this.**

*M02”***📌**** Steps:**

- First and foremost, create a table in another worksheet where you want to know the price using the
**VLOOKUP function**.

- Afterward, in
**cell D5**apply the**VLOOKUP function**to return only one value from multiple-column lookups. The final formula is,

`=VLOOKUP(B5&C5,'M01'!B5:E16,4,FALSE)`

- Following, press the
**Enter**key.

**🔎 Formula Breakdown:**

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

As a result, we will get the lookup value in a different worksheet from multiple columns with only one return.

**Similar Readings**

**VLOOKUP Not Working (8 Reasons & Solutions)****Excel LOOKUP vs VLOOKUP: With 3 Examples****Why VLOOKUP Returns #N/A When Match Exists? (5 Causes & Solutions)****INDEX MATCH vs VLOOKUP Function (9 Examples)****Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)**

### 2. Using Multiple VLOOKUP Functions to Return Only One value from Multiple Columns

We will make this formula by nesting a **VLOOKUP** function into another **VLOOKUP**. We will now get familiar with this technique.

#### 2.1 Using Multiple VLOOKUP from Multiple Columns in the Same Worksheet

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

In the following example, consider two different tables where one contains “** Item ID” **and “

**columns; and the other contains “**

*Product”***and “**

*Product”***. Now, we want to find the price from these columns using the nested**

*Price”***VLOOKUP**formula.

Follow the steps below to accomplish this target.

**📌**** Steps:**

- At the very beginning, make a table anywhere in the worksheet where you want to return the only value from multiple columns lookup.

- Afterward, in
**cell I5**, apply the nested**VLOOKUP function**.

The final formula is,

`=VLOOKUP(VLOOKUP(H5,B$5:C$16,2,FALSE),E$5:F$16,2,FALSE)`

- Subsequently, press the
**Enter**key. Thus, the**VLOOKUP**will return only one value from multiple columns.

**🔎 Formula Breakdown:**

**Lookup_value**is**VLOOKUP(H5,B$5:C$16,2,FALSE)**. Here, we use this second**VLOOKUP**to pull the**“Product”**from the table and then use this as a lookup value for the first**VLOOKUP.****table_array:**is “**E$5:F$16”**.**Col_index_num**is**2****[range_lookup]:**we want the exact match**(FALSE)**

- Now apply the same function for the rest of the
**“Item ID”.** - You can also use the
**fill handle**feature to copy the same formula below dynamically.

As a result, you will get the VLOOKUP result from multiple columns with only one return.

**Read More:** **How to Use Nested VLOOKUP in Excel (3 Criteria)**

#### 2.2 Using Multiple VLOOKUP from Multiple Columns in Different Worksheets

Now we will perform the same task but here in this case the data tables are in different worksheets. Go through the steps below to learn this process.

**📌 Steps:**

- Initially, create two Data tables in two different worksheets. For the
worksheet, create*“W1”*table.*Data Range 1*

- Following, create a data table named
in the*Data Range 2**“W2”*

- At this time, create a table in a new worksheet where you want to return value from those multiple columns.

- Following, in
**cell C5**apply the nested**VLOOKUP function**. Insert the values and the final form of the formula is,

`=VLOOKUP(VLOOKUP(B5,'W1'!B$5:C$16,2,FALSE),'W2'!B$5:C$16,2,FALSE)`

- Subsequently, press
**Enter**to return only**“Price”**from multiple columns lookup.

**🔎 Formula Breakdown:**

**Lookup_value**is**VLOOKUP(B5,’W1′!B$5:C$16,2,FALSE)**. This second**VLOOKUP**will pull the**Product**from the**“W1”**sheet.**table_array:**is**‘W2’!B$5:C$16.****Col_index_num**is**2****[range_lookup]:**we want the exact match**(FALSE)**

- Following, use the fill handle feature to apply the same formula for the rest of the
.*“Item ID”*

Consequently, you will get the desired result in this different worksheet.

## How to Apply Excel VLOOKUP with Multiple Column Index Numbers

Now, say, you need to look up multiple values at a time with a single **VLOOKUP function**. You can achieve this by using multiple-column index numbers.

Say, you have “** Item ID”**, “

**, and**

*Product”***in your given dataset. Now, you want to return both the**

*“Price”***Product**and

**Price**for the

**M-09**item.

Follow the steps below to do this.

**📌 Steps:**

- First, create a table in the worksheet where you want to get your result.

- Afterward, select cells
**G5:H5**.

- Following, insert the formula below and press the
**Ctrl+Shift+Enter**keys. You can press just**Enter**if you are an**Excel 365**user.

`=VLOOKUP(F5,B5:D16,{2,3},FALSE)`

Thus, you will get multiple lookup values with multiple-column index numbers.

## 💬 Things to Remember

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

**Read More:** **VLOOKUP Example Between Two Sheets in Excel**

## Conclusion

So, in this article, I have shown you 2 suitable ways to **VLOOKUP** from multiple columns with only one return in Excel. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

And, visit **ExcelDemy** to learn more things about Excel! Have a nice day!