Match type (-1)

[NilsYan]

Hi,

Match type (-1) returns the largest value that is less then or equal to the lookup value. Is there a way to return the largest value that is only less then the lookup value, and now equal to?

Thanks

 

[shg]

Yes, but it requires a linear search:

	A​	B​	C​	D​
1​	94​		55​
2​	93​		51​	C2: {=INDEX(A1:A16, MATCH(TRUE, A1:A16 < C1, 0))}
3​	85​
4​	76​
5​	61​
6​	56​
7​	55​
8​	51​
9​	46​
10​	42​
11​	32​
12​	26​
13​	25​
14​	24​
15​	23​
16​	10​

 

[NilsYan]

Apologies, I had a mistake above
Match (-1) returns the smallest value that is greater than or equal to the lookup value. Is there a way to return the smallest value that is only greater then the lookup value, and not equal to?

 

[shg]

=SMALL(A1:A16, COUNTIF(A1:A16, "<=" & C1) + 1)

The list need not be ordered.

 

[NilsYan]

Thanks, it works!

 

[shg]

You're welcome.