# INDEX MATCH Function using both Less Than and Equal to?

#### ffxdean

##### New Member
Hi there,

Does anyone know of a way to combine both the Less than and Equal to functions to MATCH? The 1 in the formula below only matches values that are less than the target value. Is there a way of changing the approximation of the match to include both equals to and less than? For some reason it seems to only accept one input (either less than (1), equals to (0) or greater than (-1) hence why I ask.

=INDEX(\$F\$5:\$F\$9,MATCH(B2,\$E\$5:\$E\$9,1))

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### elmer007

##### Active Member
I'm not very familiar with this type of formula, but it seems that you could modify it to say =INDEX(\$F\$5:\$F\$9,MATCH(B2+.0001,\$E\$5:\$E\$9,1)) so that it looks for something a little bit larger than what you actually need, thus returning the value equal and less than?
Just a thought- haven't tested it or anything...

#### minhhieule89

##### Board Regular
Hi there,

Does anyone know of a way to combine both the Less than and Equal to functions to MATCH? The 1 in the formula below only matches values that are less than the target value. Is there a way of changing the approximation of the match to include both equals to and less than? For some reason it seems to only accept one input (either less than (1), equals to (0) or greater than (-1) hence why I ask.

=INDEX(\$F\$5:\$F\$9,MATCH(B2,\$E\$5:\$E\$9,1))

that's part of MATCH function, 0 is exact match, 1 and -1 are the type of match I rarely use them

#### XOR LX

##### Well-known Member
Hi.

So you mean that if an exact match is not found then return the value corresponding to the closest match which is also less than the match value?

And I assume that the values in the range E5:E9 are sorted in strictly ascending order?

Regards

#### minhhieule89

##### Board Regular
Hi.

So you mean that if an exact match is not found then return the value corresponding to the closest match which is also less than the match value?

And I assume that the values in the range E5:E9 are sorted in strictly ascending order?

Regards

exactly, 1 will return the greatest match, -1 will return the smallest match. Again 1 and -1 are rarely used, I saw you're missing one last parameter of the INDEX function there

#### AhoyNC

##### Well-known Member
If the data is not sorted in ascending order try this array formula which will find the closest match that is equal to / or less than value in B2.
It must be entered with
Excel Workbook
BCDEF
27
3E
4
510A
62B
74E
88D
91E
CTRL-SHIFT-ENER.

#### XOR LX

##### Well-known Member
exactly, 1 will return the greatest match, -1 will return the smallest match. Again 1 and -1 are rarely used, I saw you're missing one last parameter of the INDEX function there

?? I was addressing the OP! And I haven't posted an INDEX function!

Regards

#### minhhieule89

##### Board Regular
?? I was addressing the OP! And I haven't posted an INDEX function!

Regards

sorry I messed up

Replies
6
Views
841
Replies
2
Views
124
Replies
10
Views
186
Replies
8
Views
700
Replies
5
Views
225

1,171,700
Messages
5,877,014
Members
433,228
Latest member
Sarah1989

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back