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!