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 eac
spreadsheet and input it into the formula.

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

Thanks in advance,
Stev

 

[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

 

[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