Hello--

I'm trying to return the second item in a list using an array.

I'm using this to give me the first item in the list. What should I modify
to return the second item in a dynamic list.

=INDEX(A34:A59,MATCH(1,(SUBTOTAL(3,OFFSET(A34:A59, ROW(A34:A59)-MIN(ROW(A34:A59)),0,1)))*(A34:A59<""),0))

Thanks!!!

=INDEX(A34:A59,SMALL(IF((A34:A59<""),ROW(A34:A59) ),2)-ROW(A34)+1)

--
__________________________________
HTH

Bob

"CeciliaPDX" wrote in message
...
Hello--

I'm trying to return the second item in a list using an array.

I'm using this to give me the first item in the list. What should I modify
to return the second item in a dynamic list.

=INDEX(A34:A59,MATCH(1,(SUBTOTAL(3,OFFSET(A34:A59, ROW(A34:A59)-MIN(ROW(A34:A59)),0,1)))*(A34:A59<""),0))

Thanks!!!

Looking at your formula I'm assuming your list is filtered.

Change this portion:

MATCH(1,

To:

MATCH(n,

Where n = the nth item you want. In your case n = 2.

--
Biff
Microsoft Excel MVP


"CeciliaPDX" wrote in message
...
Hello--

I'm trying to return the second item in a list using an array.

I'm using this to give me the first item in the list. What should I modify
to return the second item in a dynamic list.

=INDEX(A34:A59,MATCH(1,(SUBTOTAL(3,OFFSET(A34:A59, ROW(A34:A59)-MIN(ROW(A34:A59)),0,1)))*(A34:A59<""),0))

Thanks!!!

Hi T--

Thanks for catching that. Yes I am using a filtered list, which makes it
dynamic.

I changed the MATCH(1. . . to MATCH(2 but am getting an #NA back. Is there
another portion of the formula that needs to be changed?

=INDEX(A34:A59,MATCH(2,(SUBTOTAL(3,OFFSET(A34:A59, ROW(A34:A59)-MIN(ROW(A34:A59)),0,1)))*(A34:A59<""),0))

Thanks,

Cecilia

"T. Valko" wrote:

Looking at your formula I'm assuming your list is filtered.

Change this portion:

MATCH(1,

To:

MATCH(n,

Where n = the nth item you want. In your case n = 2.

--
Biff
Microsoft Excel MVP


"CeciliaPDX" wrote in message
...
Hello--

I'm trying to return the second item in a list using an array.

I'm using this to give me the first item in the list. What should I modify
to return the second item in a dynamic list.

=INDEX(A34:A59,MATCH(1,(SUBTOTAL(3,OFFSET(A34:A59, ROW(A34:A59)-MIN(ROW(A34:A59)),0,1)))*(A34:A59<""),0))

Thanks!!!

Let's tweak the OFFSET and eliminate the <"" expression:

=INDEX(A34:A59,MATCH(2,SUBTOTAL(3,OFFSET(A34:A59,, ,ROW(A34:A59)-MIN(ROW(A34:A59))+1)),0))

Don't forget, array entered!

Of course, if there *isn't* a 2nd item you'll get an error.

--
Biff
Microsoft Excel MVP


"CeciliaPDX" wrote in message
...
Hi T--

Thanks for catching that. Yes I am using a filtered list, which makes it
dynamic.

I changed the MATCH(1. . . to MATCH(2 but am getting an #NA back. Is
there
another portion of the formula that needs to be changed?

=INDEX(A34:A59,MATCH(2,(SUBTOTAL(3,OFFSET(A34:A59, ROW(A34:A59)-MIN(ROW(A34:A59)),0,1)))*(A34:A59<""),0))

Thanks,

Cecilia

"T. Valko" wrote:

Looking at your formula I'm assuming your list is filtered.

Change this portion:

MATCH(1,

To:

MATCH(n,

Where n = the nth item you want. In your case n = 2.

--
Biff
Microsoft Excel MVP


"CeciliaPDX" wrote in message
...
Hello--

I'm trying to return the second item in a list using an array.

I'm using this to give me the first item in the list. What should I
modify
to return the second item in a dynamic list.

=INDEX(A34:A59,MATCH(1,(SUBTOTAL(3,OFFSET(A34:A59, ROW(A34:A59)-MIN(ROW(A34:A59)),0,1)))*(A34:A59<""),0))

Thanks!!!