CODEWITHSUNDEEP
excelexcel-formulalookup
horace_vr
horace_vr

Reputation: 3166

Find lowest or equal value on unsorted data

I am trying to find the first cell of a range containing a number lower or equal than the lookup value. The range is unsorted, and it must stay that way.

MATCH function was my first choice, but it is not working on un-sorted data. For example, the below situation should return a result of 4 for the lookup value of 3 (since 2 is the first number equal or lower than 3, and it is the 4th number in the array). There is no exact match in this example, and MATCHwith match_type=-1returns 3. I need to find a solution to return '4'.

UNSORTED_MATCH of LOOKUP_Value=3 in array:
8
6
99
2
11
17
18
5

Thanks in advance for any idea.

Upvotes: 3

Views: 1350

Answers (1)

user4039065
user4039065

Reputation:

With the value you are seeking in C1, this appears to return the correct row number.

For the minimum non-negative difference between the numbers in column A and the target lookup number, the formula would be,

=MATCH(C1-MIN(INDEX(C1-(A$1:A$8)+(C1-(A$1:A$8)<0)*1E+199,,)),A:A, 0)

For the first number less than or equal to the target lookup number, the formula would be,

=MIN(INDEX(ROW($1:$9)+(A$1:A$9>C1)*1E+99, , ))

Upvotes: 4

Related Questions