Lookup Value in a Range and Return in Excel (5 Easy Ways)

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.

Let’s say, we have a dataset, where the tax rate for different Income ranges is shown. Now we will lookup for a given income in different income ranges and find the tax rate for that particular income.

dataset

Download Practice Workbook

5 ways to Lookup Value in a Range and Return in Excel

1. LOOKUP Function to find and Return Value in Range

The easiest to lookup value in a range and return value from a particular column is using the LOOKUP function. Type the following formula in an empty cell (F8),

=LOOKUP(F7,A5:C11,C5:C11)

Here, F7 is the lookup value, which is Income for our dataset. A5:C11 is the entire dataset and C5:C11 is the range (Different Tax Rate) from which the matched value for the lookup value will be returned.

lookup function

Press ENTER and the tax rate for the Income will be returned in cell F8.

Lookup Value in Range and Return

Observe that, here the lookup value(Income) doesn’t match exactly with any of the values of columns A and B. It just lies in the range. Regardless of that, we are able to find the returned value ( tax rate) for the lookup value.

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

2. INDEX and MATCH function to Lookup value in Range and Return

With the combination of the INDEX function and the MATCH function you can lookup for a value in a range and get the matching value for your lookup value.

Type the following formula in an empty cell (F8),

=INDEX(C6:C11,MATCH(F7,A6:A11,1))

Here, F7 is the lookup value,(Income). C5:C11 is the range (Different Tax Rate) from which the matched value for the lookup value will be returned. A6:A11 is the range for lookup value (Lower limit of Income for a particular tax rate).

INDEX AND MATCH

After pressing ENTER, the tax rate for the Income given in cell F7 will be returned in the F8 cell.

Lookup Value in Range and Return

3. VLOOKUP function to Return Value in a Range

Using the VLOOKUP function is another way to lookup for a value in a range and get the matching value from a particular column. Type the following formula in an empty cell (F8)

=VLOOKUP(F7,A5:C11,3,TRUE)

Here, F7 is the lookup value, which is Income for our dataset. A5:C11 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

After pressing ENTER, the tax rate for the Income given in cell F7 will be returned in cell F8.

Lookup Value in Range and Return

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

4. INDEX SUMPRODUCT and ROW function to Lookup and Return Value in Range

You can also lookup for a value in a range and get the matching value from a particular column by using the INDEX function, the SUMPRODUCT function, and the ROW function altogether. Type the following formula in an empty cell (F8)

=INDEX(C6:C11,SUMPRODUCT(--($F$7<=B6:B11),--($F$7>=A6:A11),ROW(1:6)))

 Here, F7 is the lookup value, which is Income for our dataset. C5:C11 is the range (Different Tax Rate) from which the matched value for the lookup value will be returned. A6:A11 is the upper limit of different ranges (Income lower than or Equal to) and B6:B11 is the lower limit of different ranges (Income Greater than). 1:6 is the first six rows.

index, sumproduct, row

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

After pressing ENTER, the tax rate for the Income given in cell F7 will be returned in cell F8.

Lookup Value in Range and Return

5. XLOOKUP function to Return Value in a Range

Using the XLOOKUP function is another way to lookup for a value in a range and get the matching value from a particular column. Type the following formula in an empty cell (F8)

=XLOOKUP(F7,B6:B11,C6:C11,0,1,1)

Here, F7 is the lookup value (Income). B6:B11 is the range for lookup value (Upper limit of Income for a particular tax rate). C5:C11 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 and the second 1 indicates that the search will be started from the beginning of your dataset.

XLOOKUP

Press ENTER and the tax rate for the Income will be returned in cell F8.

Lookup Value in Range and Return

Conclusion

Any of the above described, methods will allow you to look up values in the range and return in Excel. If you face any confusion about any of the ways, please leave a comment.


Related Articles

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo