How to Lookup and Return the Value in a Range in Excel

This is an overview of the returned lookup value in a range.

The following dataset has the Income Greater than, Income Lower than or Equal, and Tax Rate columns.

Steps:

=LOOKUP(C12,B4:D10,D4:D10)

Formula Breakdown

C12 is the lookup value, (Income).
B4:D10 is the entire dataset.
D4:D10 is the range (Different Tax Rate) from which the match value for the lookup value will be returned.
LOOKUP(C12,B4:D10,D4:D10) → becomes
- Output: 31%

Press ENTER.

Using the combination of the INDEX and MATCH functions:

Steps:

=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 match value for the lookup value will be returned.
B5:B10 is the range for the lookup value (Lower limit of Income for a particular Tax Rate).
INDEX(D5:D10,MATCH(C12,B5:B10,1)) → it becomes
- Output: 36%

Steps:

Enter the following formula in C13.

=VLOOKUP(C12,B4:D10,3,TRUE)

Formula Breakdown

C12 is the lookup value, (Income).
B4:D10 is the entire dataset.
3 indicates that the value will be returned from the third column(Tax rate).
TRUE indicates that Excel will return a value if the lookup value exists in any data range.
VLOOKUP(C12,B4:D10,3,TRUE) → it becomes
- Output: 28%

Using INDEX, SUMPRODUCT, and ROW functions:

Steps:

=@INDEX(D5:D10,SUMPRODUCT(--($C$12<=C5:C10),--($C$12>=B5:B10),ROW(1:6)))

Formula Breakdown

C12 is the lookup value, (Income).
D5:D10 is the range (Different Tax Rate) from which the match value for the lookup value will be returned.
B5:B10 is the upper limit of different ranges (Income lower than or Equal to)
C5:C10 is the lower limit of different ranges (Income Greater than).
1:6 refers to the first six rows.
@INDEX(D5:D10,SUMPRODUCT(–($C$12<=C5:C10),–($C$12>=B5:B10),ROW(1:6))) → it becomes
- Output: 40%

Note: You have to select the exact number of rows in your dataset.

Steps:

=XLOOKUP(C12,C5:C10,D5:D10,0,1,1)

Formula Breakdown

C12 is the lookup value (Income).
C5:C10 is the range for the lookup value (Upper limit of Income for a particular tax rate).
D5:D10 is the range (Different Tax Rate) from which the match 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 in the beginning of your dataset.
XLOOKUP(C12,C5:C10,D5:D10,0,1,1) → it becomes
- Output: 45%