Next number down in list

[bjenks]

I am sure this has been asked before, but I can't find anything. Here is the data in each cell:

A1: 1
A2: 4
A3: 8
A4: 12
A5: 13

I am looking for a formula that if I put in the following data at B1 I would get the following Row number:

0, A1
1, A1
3, A2
11, A4
13, A5
15, Error or N/A

I was using MATCH(B1, A1:A5, 1) but now realize it was giving me A3 for 11 instead of A4

I am trying to stay away from VBA or Functions, just formulas.

 

[T. Valko]

I am sure this has been asked before, but I can't find anything. Here is the data in each cell:

A1: 1
A2: 4
A3: 8
A4: 12
A5: 13

I am looking for a formula that if I put in the following data at B1 I would get the following Row number:

0, A1
1, A1
3, A2
11, A4
13, A5
15, Error or N/A

I was using MATCH(B1, A1:A5, 1) but now realize it was giving me A3 for 11 instead of A4

I am trying to stay away from VBA or Functions, just formulas.

Try this array formula**:

=INDEX(A1:A5,MATCH(TRUE,A1:A5>=B1,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

If the lookup value in B1 is greater than the max value in the range then you'll get an error.

 

[bjenks]

Try this array formula**:

=INDEX(A1:A5,MATCH(TRUE,A1:A5>=B1,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

If the lookup value in B1 is greater than the max value in the range then you'll get an error.

Just noticed that I only needed this part of your eqation as I was just looking for the row number:

MATCH(TRUE,A1:A5>=B1,0)

Thanks...

 

[T. Valko]

Just noticed that I only needed this part of your eqation as I was just looking for the row number:

MATCH(TRUE,A1:A5>=B1,0)

Thanks...

You're welcome. Thanks for the feedback!