Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Lookup and Return Value in a Range in Excel (5 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.
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.

Overview Image for looking up value in a range in Excel


Download Practice Workbook

You can download the Excel file and practice while reading this article.


5 Easy Ways to Lookup and Return Value in a Range in Excel

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.

Dataset for Looking up Value in a Range in Excel


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)

Applying LOOKUP Function to lookup value in a range and return

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.

The Result after using LOOKUP Function

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.

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


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))

Employing INDEX and MATCH Functions

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.

The Outcome after using INDEX and MATCH Functions


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)

Employing VLOOKUP Function to lookup for a value in a range in Excel

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.

Result after using VLOOKUP Function

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


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.

[email protected](D5:D10,SUMPRODUCT(--($C$12<=C5:C10),--($C$12>=B5:B10),ROW(1:6)))

Combining Functions

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.

The Outcome after using Combined functions


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)

Use of XLOOKUP Function to lookup value in a range and return in Excel

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.

The output after using XLOOKUP Function


Practice Section

You can download the Excel file and practice the explained methods.

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 Exceldemy to explore more.


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