• Status: Solved
• Priority: Medium
• Security: Public
• Views: 280

# Trying to use formula to find closest value to number from list in descending order

I have a list of number in descending order.  With a number that doesn't necessarily match one in the list, I'm trying to locate the largest number that is still less than the number I'm looking for.  I know this is possible in VBA via a loop, but I'm wondering if anyone knows how to do it with functions.  See attached.
FindClosestValue.xlsx
0
BBlu
• 3
• 3
• 2
• +1
3 Solutions

Commented:
Here is one way:

=INDEX(D3:D20,MATCH(G8,D3:D20,-1)+1)
0

Commented:
=OFFSET(D3,COUNT(D3:D1000)-SUM(IF(D3:D1000<G8,IF(NOT(ISBLANK(D3:D1000)),1,0))),0)

Where:
D3 is the top of the column range containing your list
D1000 is the bottom of an arbitrary range size, it could be any size, because the COUNT returns the number of rows in the range.
G8 contains your reference value (e.g. 77)
the SUM(IF(IF(NOT(ISBLANK()))) construct returns the count of values less that your reference value in the entire range excluding blank cells.  The result of this sum function is substracted from the count of values, and then that difference is used as the row offset for the top of the range.

Remember to type CTRL-Enter to calculate as it is an array formula.

HTH,
Dave
0

Commented:
actually there's an error -- gimme a sec
0

Commented:
=OFFSET(D3,COUNT(D3:D1000)-SUM(IF(NOT(ISBLANK(D3:D1000)),IF(D3:D1000<G8,1,0))),0)

OK, there you go.  switched the IFS.
0

Commented:
I think Stephen's suggestion works except when there's an exact match, if G8 is 74 in your example then the suggested formula returns 69, I assume it should be 74?

Try this "array formula"

=MAX(IF(D3:D20<=G8,D3:D20))

confirmed with CTRL+SHIFT+ENTER

That works whatever the order of the numbers in D3:D100

regards, barry
0

Commented:
Heh Barry you always spot things which I should, but don't.
0

Commented:
I'm still not sure if that's the requirement - question says "less than" but in the workbook it's "without going over"......?

regards, barry
0

Commented:
Good point, OP needs to clarify.
0

Author Commented:
I haven't tested Dave or Stephen's but they look right.  Barry's worked perfectly and was the easiest for me to understand.  Thanks to you all!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.