Formula for looking up an exact or greater number...

**Stoo** · 05-18-2009, 03:47 PM

I'm trying to build a spreadsheet to work out some trends from all sorts of data and am looking for a way to automate this process.

I want to be able to lookup an exact or greater than value as specified by one cell, that is then searched for in a range/column of figures. When this number is found to report where it appeared.

Sorry if my explanation is confusing. I'll try it a different way:

Find next number in range (B1:B50) that is greater than or equal to C1, output which row it first appeared in. i.e. A5, A20?

Thanks.

**zbor** · 05-18-2009, 03:54 PM

=match($c$1;b1:b14;1)+1

**daddylonglegs** · 05-18-2009, 03:59 PM

This formula will give the row number (within the range)

=MATCH(TRUE,INDEX(B1:B50>=C1,0),0)

**Stoo** · 05-18-2009, 06:07 PM

Originally Posted by daddylonglegs

This formula will give the row number (within the range)

=MATCH(TRUE,INDEX(B1:B50>=C1,0),0)

Thankyou this does part of it! This tells me how many rows from the start of the range are counted until the value is met or exceeded. Can the Cell ID be displayed rather than the amount of 'moves' down the range?

Or better yet from the row within the range returned, can the cell and it's row be copied to a new worksheet?

Thanks again. Most helpful.

**deadlyduck** · 05-18-2009, 06:14 PM

Try this modification of DLL's code:

=ADDRESS(MATCH(TRUE,INDEX(B1:B50>=C1,0),0),2)

The 2 at the end of the function refers to column B- if you have the data in another column, change as appropriate (c=3, D=4 etc)

Edit: Just noticed a possible problem- if the values in column B aren't in order this could give a wrong result.

**daddylonglegs** · 05-18-2009, 07:40 PM

Originally Posted by deadlyduck

if the values in column B aren't in order this could give a wrong result.

Shouldn't be an issue.....

If you want the formula in another sheet then assuming that your data is on a worksheet called data you can use this formula to get the location, without "hardcoding" the column;

=CELL("address",INDEX(data!B1:B50,MATCH(TRUE,INDEX(data!B1:B50>=data!C1,0),0)))

If you just want the value itself

=INDEX(data!B1:B50,MATCH(TRUE,INDEX(data!B1:B50>=data!C1,0),0))

**zbor** · 05-18-2009, 03:57 PM

hm, match($c$1;b1:b14;-1) should work, but I don't know why it gives me error .-S

In that case this migh be better...

=IF(ISNA(MATCH($C$1;B1:B14;0));MATCH($C$1;B1:B14;1)+1;MATCH($C$1;B1:B14;0))