INDEX MATCH Function using both Less Than and Equal to?

ffxdean

New Member
Joined
Apr 26, 2015
Messages
2
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
Joined
Aug 29, 2014
Messages
299
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
Joined
Jul 16, 2014
Messages
68
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
Joined
Jul 2, 2012
Messages
4,517
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
Joined
Jul 16, 2014
Messages
68
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
Joined
Oct 10, 2011
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 2, 2012
Messages
4,517
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top