Find Match or Next Lowest Number In Descending Column of Numbers

Usually I can figure these things out for myself, but I guess I've had too much coffee today, I can't think straight.

I have descending numbers. Just assume they start in A1
284.0
283.6
282.8
282.2
281.5
281
280.8

etc.

Assume the number I want to search for is 281.8. I want the row number for either that number, or if not found give me the next lowest number, which in this case would be 281.5.

Thanks for your help.

Let's go with:

=MATCH(281.8,A1:A7,-1)+1

OR

=SUMPRODUCT(--(A1:A7<281.8))

Hi,
Assuming like you said that the numbers are in descending order, starting in A1 and that the number to find is in B1, this should work :

Please Login or Register  to view this content.

If you want to return the value instead of the row number then :

Please Login or Register  to view this content.

=countif(a1:a7,">" &281.8)+1

Here's a simpler formula to return the value (not the row number):

Please Login or Register  to view this content.

Hi Sky,

Something like this?

Please Login or Register  to view this content.

Originally Posted by daffodil11

=countif(a1:a7,">" &281.8)+1

So the data might not start in A1 there may be some blank rows above it.
Your formula above gave me an idea and this is what I came up with and it seems to work.
With the data going from A4:A10 and the number I want to look for in D4 this formula here seems to give me the desired results.
Thanks for your help.

Formula:

Please Login or Register  to view this content.

Originally Posted by xladept

Hi Sky,

Something like this?

Please Login or Register  to view this content.

I will definitely take a look at this as what I am doing is ultimately going to be in VBA. I didn't want to loop through a bunch of cells, I was hoping to use Evaluate and get a row number very quickly. I'm trying to find alternatives to looping.

Thanks again.

You're welcome and thanks for the rep!

You're thinking of using enumerate - I'll be interested to see how that goes

Originally Posted by xladept

You're welcome and thanks for the rep!

You're thinking of using enumerate - I'll be interested to see how that goes

No I thought I would use Evaluate, but I just ended up doing this.

Please Login or Register  to view this content.

Does that work?