Rafiul Haq
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”.
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.
- 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 col_index_num is set to 3. Therefore, we’ll get the value from the third column (“Price”).
- Finally, we’ve set range_lookup as 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.
- 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 col_index_num is set to 3. Therefore, we’ll get the value from the third column (“Price”).
- Finally, we’ve set range_lookup as 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 col_index_num is set to 2. Therefore, we’ll get the value from the second column (“Product”).
- Finally, we’ve set range_lookup as 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 col_index_num is set to 2. Therefore, we’ll get the value from the second column (“Price”).
- Finally, we’ve set range_lookup as 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.
- 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 col_index_num is set to 2. Therefore, we’ll get the value from the third column (“Price”).
- Finally, because we want the exact match, we’ve set range_lookup as 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 col_index_num is set to 2. Therefore, we’ll get the value from the third column (“Price”).
- Finally, because we want the exact match, we’ve set range_lookup as 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!
