Using Excel functions to return a value within a range

[Tyril_Simmons]

I'm trying to use Excel built in functions to return the first value after the #N/A within my range of data. The data appears in column A1:A24 as shown below:

2
#N/A
3
4
5
6
7
#N/A
#N/A
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

The function should return the value 3.

Any one with any ideas?

 

[Aladin Akyurek]

On 2002-04-18 08:13, Tyril_Simmons wrote:
I'm trying to use Excel built in functions to return the first value after the #N/A within my range of data. The data appears in column A1:A24 as shown below:

2
#N/A
3
4
5
6
7
#N/A
#N/A
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

The function should return the value 3.

Any one with any ideas?

Array-enter:

=INDEX(A1:A24,MATCH(TRUE,ISNA(A1:A24),0)+1)

In order to array-enter a formula hit control+ahift+enter at the same time, not just enter.

 

[Tyril_Simmons]

That function handles cases where there is only one #N/A. How about with multiple #N/A one after the other. For example if my range chages to A2:A24 I would like the function to return the value 8.

 

[Aladin Akyurek]

On 2002-04-18 08:34, Tyril_Simmons wrote:
That function handles cases where there is only one #N/A. How about with multiple #N/A one after the other. For example if my range chages to A2:A24 I would like the function to return the value 8.

Right.

Array-enter:

=INDEX(A1:A24,MATCH(MATCH(TRUE,ISNA(A1:A24),0),ISNUMBER(A1:A24)*ROW(A1:A24))+1)

In order to test the formula, just change the location of the first #N/A, not the range.

BTW, if the range changes frequently, we can create dynamic name range that you can use in this and other formulas.

Aladin