Find the first lowest value in a column that exceeds $foo

bugmenot · 03-21-2009, 04:33 PM

I've got a column with a list of ordered values like this:

And a user enters a value, which could be anything up to the highest value in this column. I need a method of finding the lowest number in the column that is larger than the user entered value.

for example, if the user entered 4500000, then the value I'd need would be 4545455.

Any ideas on how I can accomplish this?

**Note: you'll notice that this list of numbers has a pattern. It is 1/N*100000000

**daddylonglegs** · 03-21-2009, 04:44 PM

Assuming your list is in A1:A24 and lookup value in C1 you can use this formula

=INDEX(A1:A24,MATCH(C1,A1:A24,-1))

bugmenot · 03-21-2009, 09:18 PM

Wow, that's easier than I had thought. I was trying arrays and all kinds of funky things.

Thanks so much for the help, this works nicely!

**daddylonglegs** · 03-21-2009, 09:37 PM

Given you particular list I suppose you could actually calculate without the table, i.e.

=100000000/INT(100000000/C1)

although you might want to set the minimum value.....

In general you could also use an array formula to get the lowest number that's larger than C1, i.e.

=MIN(IF(A1:A24>=C1,A1:A24))

confirmed with CTRL+SHIFT+ENTER