Weâ€™re going to show you **5 **quick methods of using **VLOOKUP **if a condition is between **multiple ranges **in **Excel**. For this purpose, weâ€™ve taken a dataset with **2 **lookup tables. In the first table, there are home appliances from **3 **companies and personal devices in the second table. Moreover, both of these tables have **3 columns**: â€ś**Company**â€ť, â€ś**Product**â€ť, and â€ś**Price**â€ť.

**Table of Contents**hide

## Download Practice Workbook

## 5 Ways to Use VLOOKUP If Condition Lies Between Multiple Ranges in Excel

### 1. Merging VLOOKUP & IF Functions If Condition Lies Between Multiple Ranges in Excel

For the first method, weâ€™re going to use an **IF **inside the **VLOOKUP **function to choose a **range **from **multiple ranges **in **Excel**, from which we will return a value. We want to find the **Price **of the **Mobile **from **Company B **here.

Weâ€™re going to use **Named Range** in our all methods. To name a **range**, do these-

- Firstly, select a
**range**. Weâ€™re selecting**B4:D9**. - Secondly, type in the
**Name Box**. Weâ€™ve typed â€ś**AC**â€ť. - Finally, press
**ENTER**.

Thus weâ€™ll create a **Named Range**.

Similarly, weâ€™re gonna name the range **F4:H9** â€ś**Mobile**â€ť.

- Then, type the following formula in
**cell D13**.

`=VLOOKUP(C13,IF(B13="Air Conditioner",AC,Mobile),3,FALSE)`

**Formula Breakdown**

**IF(B13=â€ťAir Conditionerâ€ť,AC,Mobile)**- When the value of
**cell B13**is â€ś**Air****Conditioner**â€ť it will set our lookup**range**as**B4:D9**. Else, it will use the**range F4:H9**. - Our
**cell**value in**B13**is â€ś**Mobile**â€ť, hence it will set the lookup range as the second table.

- When the value of
- Then our formula reduces to ->
**VLOOKUP(C13,Mobile,3,FALSE)****Output:****1500**.- The
**VLOOKUP**function returns a value from a**range**. This function searches for the value of**cell C13**in the â€ś**Mobile**â€ť**range**. It will find it in**row 6**. - In our formula
is set to*col_index_num***3**. Therefore, weâ€™ll get the value from the third column (â€ś**Price**â€ť). - Finally, weâ€™ve set
as*range_lookup***FALSE**, because we want the**exact match**.

- After that, press
**ENTER**.

Thus, we can use the **VLOOKUP **function to lookup values between **multiple ranges**.

**Read More:** **How to Use VLOOKUP to Find a Value That Falls Between a Range**

### 2. Using VLOOKUP & INDIRECT Functions When Condition Lies Between Multiple Ranges

In this second method, weâ€™re going to merge the **INDIRECT **and **VLOOKUP **functions to lookup values between **multiple ranges**. Here, weâ€™ll use the same dataset from the first method.

**Steps:**

- Firstly,
**as shown in the first method**, name the range**B4:D9**as â€ś**PriceAC**â€ť.**F4:H9**as â€ś**PriceMobile**â€ť.

- Secondly, type the following formula in
**cell D13**.

`=VLOOKUP(C13,INDIRECT("Price"& B13),3,FALSE)`

**Formula Breakdown**

**INDIRECT(â€śPriceâ€ť& B13)**- This function is used to store
**cell**references. In our case, weâ€™re storing the â€ś**PriceAC**â€ť**Named Range**using this.

- This function is used to store
- Then our formula reduces to ->
**VLOOKUP(C13,PriceAC,3,FALSE)****Output: 4000**.- The
**VLOOKUP**function returns a value from a**range**. This function searches for the value of**cell C13**in the â€ś**PriceAC**â€ť range. It will find it in**row 5**. - In our formula
is set to*col_index_num***3**. Therefore, weâ€™ll get the value from the third**column**(â€ś**Price**â€ť). - Finally, weâ€™ve set
as*range_lookup***FALSE**, because we want the**exact match**.

- Finally, press
**ENTER**.

Therefore, weâ€™ll get the **price **of **AC **from **Company A**.

**Read More: Lookup Value in a Range and Return in Excel (5 Easy Ways)**

### 3. Applying Nested VLOOKUP to Lookup Between Multiple Ranges in Excel

For the third method, weâ€™ve changed our dataset. Weâ€™ll use the **nested VLOOKUP** formula to search values from **multiple ranges**. Here, weâ€™re going to match the values from a **cell **in our first **range **and then use that value to return a value from the second **range**.

**Steps:**

- Firstly,
**as shown in the first method**, name the range**B4:C9**as**â€śProduct_Nameâ€ť**.**E4:F9**as**â€śProduct_Priceâ€ť**.

- Secondly, type the following formula in cell
**C13**.

`=VLOOKUP(VLOOKUP(B13,Product_Name,2,0),Product_Price,2,FALSE)`

**Formula Breakdown**

**VLOOKUP(B13,Product_Name,2,0)****Output: â€śAir Conditionerâ€ť**.- The
**VLOOKUP**function returns a value from a**range**. This function searches for the value of**cell B13**in the â€ś**Product_Name**â€ť**range**. It will find it in**row 5**. - In our formula
is set to*col_index_num***2**. Therefore, weâ€™ll get the value from the second**column**(â€ś**Product**â€ť). - Finally, weâ€™ve set
as*range_lookup***0**, because we want the exact match.

- Then, our formula reduces to ->
**VLOOKUP(â€śAir Conditionerâ€ť,Product_Price,2,FALSE)****Output:****800**.- This function searches for â€ś
**Air****Conditioner**â€ť in the â€ś**Product_Price**â€ť**range**. It will find it in**row 5**. - In our formula
is set to*col_index_num***2**. Therefore, weâ€™ll get the value from the second**column**(â€ś**Price**â€ť). - Finally, weâ€™ve set
as*range_lookup***FALSE**, as we want the**exact match**.

- Finally, press
**ENTER**.

Weâ€™ve shown another way of using the **VLOOKUP **function in **multiple ranges**.

**Read More: How to Find Second Match with VLOOKUP in Excel (2 Simple Methods)**

### 4. Use of VLOOKUP and Nested IF to Lookup Between Multiple Ranges in Excel

In this section, weâ€™ll use the **nested IF** inside a **VLOOKUP **function for **multiple ranges**. Here, weâ€™ve **3 ranges**. Weâ€™re gonna input the **Company **and **Product **name and weâ€™ll get the **price **of that **Product**.

**Steps:**

- Firstly,
**as shown in the first method**, name the**rangeÂ****B4:C10**as â€ś**Company_A**â€ť.**E4:F10**as â€ś**Company_B**â€ť.**H4:I10**as â€ś**Company_C**â€ť.

- Secondly, type the following formula in
**cell C13**.

`=VLOOKUP(C13,IF(B13="B",Company_B,IF(B13="C",Comapny_C,Company_A)),2,0)`

**Formula Breakdown**

**IF(B13=â€ťBâ€ť,Company_B,IF(B13=â€ťCâ€ť,Comapny_C,Company_A))**- Here, weâ€™re using the nested
**IF**formula. When the value of**cell B13**is â€ś**B**â€ť weâ€™ll use the**Company_B range**, â€ś**C**â€ť for**Company_C**and else it will be**Company_A range**. - The value of
**cell B13**is â€ś**C**â€ť, therefore weâ€™ll use the third**Named Range**here.

- Here, weâ€™re using the nested
- Our formula reduces to ->
**VLOOKUP(C13,Comapny_C,2,0)****Output:****449**.- The
**VLOOKUP**function returns a value from a range. This function searches for the value of**cell C13**in the â€ś**Company_C**â€ť**range**. It will find it in**row 10**. - In our formula
is set to*col_index_num***2**. Therefore, weâ€™ll get the value from the third column (â€ś**Price**â€ť). - Finally, because we want the
**exact match**, weâ€™ve setas*range_lookup***0**.

- Finally, press
**ENTER**.

In conclusion, weâ€™ve used the **VLOOKUP **function for **3 ranges**. Moreover, this is what the final step should look like.

**Read More: How to Use Column Index Number Effectively in Excel VLOOKUP Function**

### 5. Using IF & Nested VLOOKUP If Condition Lies Between Multiple Ranges

For the last method, weâ€™re going to use the **nested VLOOKUP **functions inside an **IF **function for **multiple ranges**. Here, weâ€™ve chosen the exact dataset from method **4**. Weâ€™ll look for the **Product Price **of our defined **Company**.

**Steps:**

- Firstly,
**as shown in the first method**, name the**rangeÂ****B4:C10**as â€ś**Company_A**â€ť.**E4:F10**as â€ś**Company_B**â€ť.**H4:I10**as â€ś**Company_C**â€ť.

- Secondly, type the following formula in
**cell C13**.

`=IF(B13="A",VLOOKUP(C13,Company_A,2,0),IF(B13="B",VLOOKUP(C13,Company_B,2,0),VLOOKUP(C13,Comapny_C,2,0)))`

**Formula Breakdown**

- Weâ€™re checking if the value of
**cell B13**is equal to â€ś**A**â€ť or â€ś**B**â€ť or â€ś**C**â€ť (in case of no match). Whenever a match is found, weâ€™ll set our lookup range in the corresponding**Companyâ€™s range**. - Our
**cell B13**value is â€ś**C**â€ť. Therefore our formula will reduce to â€“ >**VLOOKUP(C13,Comapny_C,2,0)****Output: 449**.- The
**VLOOKUP**function returns a value from a**range**. This function searches for the value of**cell C13**in the â€ś**Company_C**â€ť range. It will find it in**row 10**. - In our formula
is set to*col_index_num***2**. Therefore, weâ€™ll get the value from the third**column**(â€ś**Price**â€ť). - Finally, because we want the
**exact match**, weâ€™ve setas*range_lookup***0**.

- Finally, press
**ENTER**.

Thus, weâ€™ll return the **Watch Price** from **Company C **using this formula.

## Practice Section

Weâ€™ve provided practice datasets for each method in the **Excel **file.

## Conclusion

Weâ€™ve shown you **5 **quick methods of using **VLOOKUP **if a condition is between **multiple ranges **in **Excel**. If you face any difficulties regarding this tutorial, feel free to comment below. As always, thanks for reading, and keep excelling!