# Match type (-1)

#### NilsYan

##### New Member
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

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### shg

##### MrExcel MVP
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

##### New Member
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

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

The list need not be ordered.

#### NilsYan

##### New Member
Thanks, it works!

You're welcome.

Replies
9
Views
59
Replies
8
Views
84
Replies
2
Views
76
Replies
3
Views
59
Replies
3
Views
96