Array

[ca1358]

{=TRANSPOSE(INDIRECT(ADDRESS(MIN(IF((Sheet1!A1:A1000=Sheet2!A1)*(ROW(Sheet1!A1:A1000)>=MATCH(Sheet2!A2,Sheet1!B:B,0)),ROW(Sheet1!A1:A1000),"")),3,,,"Sheet1")&":"&ADDRESS(MIN(IF((Sheet1!A1:A1000=Sheet2!A1)*(ROW(Sheet1!A1:A1000)>=MATCH(Sheet2!A2,Sheet1!B:B,0)),ROW(Sheet1!A1:A1000),"")),6)))}



I am trying to input this function has an array, I is not working, what am I
doing wrong.
--
ca1358

[Pete]

You do not type the curly braces at the beginning and end, i.e. you
would enter into the cell:

=3DTRANSPOSE(INDIRECT(ADDRESS(MIN(IF((Sheet1!A1:A1000=3DSheet2!A1)*(ROW(She=
et1=AD!A1:A1000)>=3DMATCH(Sheet2!A2,Sheet1!B:B,0)),ROW(Sheet1!A1:A1000),"")=
),3,,,"S=ADheet1")&":"&ADDRESS(MIN(IF((Sheet1!A1:A1000=3DSheet2!A1)*(ROW(Sh=
eet1!A1:A1000=AD)>=3DMATCH(Sheet2!A2,Sheet1!B:B,0)),ROW(Sheet1!A1:A1000),""=
)),6)))

Then, instead of pressing <enter> as you would with a normal formula,
you must hold down <CTRL> and <SHIFT> and then press <enter> (often
referred to as CSE). If you do it correctly the curly braces will wrap
around the formula.

Hope this helps (I've not checked though the details of the formula).

Pete

[ca1358]

Thank you
--
ca1358


"Pete" wrote:

> You do not type the curly braces at the beginning and end, i.e. you
> would enter into the cell:
>
> =TRANSPOSE(INDIRECT(ADDRESS(MIN(IF((Sheet1!A1:A1000=Sheet2!A1)*(ROW(Sheet1Â*!A1:A1000)>=MATCH(Sheet2!A2,Sheet1!B:B,0)),ROW(Sheet1!A1:A1000),"")),3,,,"SÂ*heet1")&":"&ADDRESS(MIN(IF((Sheet1!A1:A1000=Sheet2!A1)*(ROW(Sheet1!A1:A1000Â*)>=MATCH(Sheet2!A2,Sheet1!B:B,0)),ROW(Sheet1!A1:A1000),"")),6)))
>
> Then, instead of pressing <enter> as you would with a normal formula,
> you must hold down <CTRL> and <SHIFT> and then press <enter> (often
> referred to as CSE). If you do it correctly the curly braces will wrap
> around the formula.
>
> Hope this helps (I've not checked though the details of the formula).
>
> Pete
>
>