# How to Lookup and Return Value in a Range in Excel (5 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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%
At the moment, press ENTER.
• 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%
• As a result, you can see the result in cell C13. ## Practice Section ## 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects. Prantick Bala

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 Advanced Excel Exercises with Solutions PDF  