If you want to look up certain criteria in a range and find the matching value, this article is the right place for you. I’ll show you 5 easy ways to lookup value in a range and return in Excel.
In the following picture, you can see the overview for the returned lookup value in a range. Let’s get into the article so that you can do the task as well.
How to Lookup and Return Value in a Range in Excel: 5 Easy Ways
The following dataset has the Income Greater than, Income Lower than or Equal, and Tax Rate columns. Here, the Tax Rate for different Income ranges is shown. After that, using this dataset, we will go through 5 easy methods to lookup values in a range and return in Excel. Here, we used Excel 365. You can use any available Excel version.
1. Use of LOOKUP Function to Find and Return Value in a Range
The easiest to lookup value in a range and return value from a particular column is using the LOOKUP function. Therefore, we will use the LOOKUP function in this method.
Steps:
- In the beginning, type the following formula in cell C13.
=LOOKUP(C12,B4:D10,D4:D10)
Formula Breakdown
- C12 is the lookup value, which is Income for our dataset.
- B4:D10 is the entire dataset.
- D4:D10 is the range (Different Tax Rate) from which the matched value for the lookup value will be returned.
- LOOKUP(C12,B4:D10,D4:D10) → becomes
- Output: 31%
- Therefore, you can see the result in cell C13.
Note: here the lookup value(Income) doesn’t match exactly with any of the values of Columns B and C. It just lies in the range. Regardless of that, we are able to find the returned value (Tax Rate) for the lookup value.
2. Combining INDEX and MATCH Functions to Lookup and Return Value in a Range
In this method, we will use the combination of the INDEX function and the MATCH function to lookup value in a range and return in Excel.
Steps:
- In the first place, we will type the following formula in cell C13.
=INDEX(D5:D10,MATCH(C12,B5:B10,1))
Formula Breakdown
- C12 is the lookup value (Income).
- D5:D10 is the range (Different Tax Rate) from which the matched value for the lookup value will be returned.
- B5:B10 is the range for lookup value (Lower limit of Income for a particular Tax Rate).
- INDEX(D5:D10,MATCH(C12,B5:B10,1)) → therefore, it becomes
- Output: 36%
- Afterward, press ENTER.
- As a result, you can see the result in cell C13.
3. Employing VLOOKUP Function
Using the VLOOKUP function is another way to lookup for a value in a range and get the matching value from a particular column.
Steps:
First of all, we will type the following formula in cell C13.
=VLOOKUP(C12,B4:D10,3,TRUE)
Formula Breakdown
- C12 is the lookup value, which is Income for our dataset.
- B4:D10 is the entire dataset.
- 3 indicates that the value will be returned from the third column(Tax rate) of our dataset.
- TRUE indicates that Excel will return a value if the lookup value exists in any one of the data ranges.
- VLOOKUP(C12,B4:D10,3,TRUE) → As a result, it becomes
- Output: 28%
- After that, press ENTER.
- Hence, you can see the result in cell C13.
4. Combining INDEX, SUMPRODUCT, and ROW Functions to Search and Extract a Value
You can also lookup for a value in a range and get the matching value from a particular column by using INDEX, SUMPRODUCT, and ROW functions altogether.
Steps:
- In the first place, type the following formula in cell C13.
=@INDEX(D5:D10,SUMPRODUCT(--($C$12<=C5:C10),--($C$12>=B5:B10),ROW(1:6)))
Formula Breakdown
- C12 is the lookup value, which is Income for our dataset.
- D5:D10 is the range (Different Tax Rate) from which the matched value for the lookup value will be returned.
- B5:B10 is the upper limit of different ranges (Income lower than or Equal to) and
- C5:C10 is the lower limit of different ranges (Income Greater than).
- 1:6 is the first six rows.
- @INDEX(D5:D10,SUMPRODUCT(–($C$12<=C5:C10),–($C$12>=B5:B10),ROW(1:6))) → therefore, it becomes
- Output: 40%
Note: you have to select the same number of rows as your dataset has from the beginning. Here we have 6 rows so we select row 1:6. If you have 10 rows in your dataset, you have to select 1:10.
- At this point, press ENTER.
- Therefore, you can see the result in cell C13.
5. Use of XLOOKUP Function to Return Value
Using the XLOOKUP function is another way to lookup for a value in a range and get the matching value from a particular column.
Steps:
- In the beginning, we will type the following formula in cell C13.
=XLOOKUP(C12,C5:C10,D5:D10,0,1,1)
Formula Breakdown
- C12 is the lookup value (Income).
- C5:C10 is the range for lookup value (Upper limit of Income for a particular tax rate).
- D5:D10 is the range (Different Tax Rate) from which the matched value for the lookup value will be returned.
- 0 indicates that no value will be shown if the lookup value isn’t found.
- The first 1 in the argument indicates that if an exact match is not found, then the formula will return the next smaller value.
- The second 1 indicates that the search will be started from the beginning of your dataset.
- XLOOKUP(C12,C5:C10,D5:D10,0,1,1) → hence, it becomes
- Output: 45%
- In addition, press ENTER.
- As a result, you can see the result in cell C13.
Practice Section
You can download the Excel file and practice the explained methods.
Download Practice Workbook
You can download the Excel file and practice while reading this article.
Conclusion
Any of the above-described, methods will allow you to look up values in a range and return them in Excel. If you face any confusion about any of the ways, please leave a comment. You can visit our website to explore more.
<< Go Back to Lookup | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
