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:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]

94​

[/td][td][/td][td]

55​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

93​

[/td][td][/td][td="bgcolor:#E5E5E5"]

51​

[/td][td]C2: {=INDEX(A1:A16, MATCH(TRUE, A1:A16 < C1, 0))}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

85​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

76​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

61​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

56​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

55​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

51​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

46​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

42​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

32​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

26​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

25​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

24​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

23​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

10​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[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.