Match type (-1)

NilsYan

New Member
Joined
Jan 9, 2017
Messages
15
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
 

Some videos you may like

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
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Jan 9, 2017
Messages
15
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
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
=SMALL(A1:A16, COUNTIF(A1:A16, "<=" & C1) + 1)

The list need not be ordered.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,998
Messages
5,526,161
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top