INDEX MATCH Function using both Less Than and Equal to?

[ffxdean]

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

 

[elmer007]

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]

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]

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]

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]

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
	B	C	D	E	F
2	7
3	E
4
5				10	A
6				2	B
7				4	E
8				8	D
9				1	E
CTRL-SHIFT-ENER.

 

[XOR LX]

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]

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

Regards

sorry I messed up