Call list length in formula

S

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
 
A

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
 
M

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
 
Top