Array
 

{=TRANSPOSE(INDIRECT(ADDRESS(MIN(IF((Sheet1!A1:A10 00=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)))}



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

Array
 

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:A100 0=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=Sheet 2!A1)*(ROW(Sheet1!A1:A1000*)=MATCH(Sheet2!A2,Shee t1!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

Array
 

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:A100 0=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=She et2!A1)*(ROW(Sheet1!A1:A1000Â*)=MATCH(Sheet2!A2,S heet1!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