ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Call list length in formula (https://www.excelbanter.com/excel-discussion-misc-queries/89025-call-list-length-formula.html)

steev_jd

Call list length in formula
 

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


Ardus Petus

Call list length in formula
 
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" a écrit
dans le message de news:
...

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

Call list length in formula
 
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" wrote in
message ...

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





All times are GMT +1. The time now is 02:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com