Call list length in formula

[steev_jd]

Hi,

I am using the below formula in several spreadsheets;

=CONCATENATE("F",(MATCH($A4,$E$1:$E$7985,0)))

Currently I have to look at the length of the array in column E in each spreadsheet and input it into the formula.

Is there anyway of having the formula automatcally call the length of the array to save me doing this?

Thanks in advance,
Steve

[Ardus Petus]

The complicated way:
=CONCATENATE("F",(MATCH($A4,OFFSET($E1,,,COUNTA(E:E)),0)))

The simple way:
=CONCATENATE("F",(MATCH($A4,$E:$E,0)))

HTH
--
AP

"steev_jd" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> Hi,
>
> I am using the below formula in several spreadsheets;
>
> =CONCATENATE("F",(MATCH($A4,$E$1:$E$7985,0)))
>
> Currently I have to look at the length of the array in column E in each
> spreadsheet and input it into the formula.
>
> Is there anyway of having the formula automatcally call the length of
> the array to save me doing this?
>
> Thanks in advance,
> Steve
>
>
> --
> steev_jd
> ------------------------------------------------------------------------
> steev_jd's Profile:
> http://www.excelforum.com/member.php...o&userid=33107
> View this thread: http://www.excelforum.com/showthread...hreadid=542820
>

[macropod]

Hi steev_jd,

If you give your array a name (eg MyArray), you could use:
=CONCATENATE("F",(MATCH($A4,MyArray,0)))
or, even simpler:
="F"&MATCH($A4,MyArray,0)

Cheers


"steev_jd" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I am using the below formula in several spreadsheets;
>
> =CONCATENATE("F",(MATCH($A4,$E$1:$E$7985,0)))
>
> Currently I have to look at the length of the array in column E in each
> spreadsheet and input it into the formula.
>
> Is there anyway of having the formula automatcally call the length of
> the array to save me doing this?
>
> Thanks in advance,
> Steve
>
>
> --
> steev_jd
> ------------------------------------------------------------------------
> steev_jd's Profile:
http://www.excelforum.com/member.php...o&userid=33107
> View this thread: http://www.excelforum.com/showthread...hreadid=542820
>