Array

C

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.
 
P

Pete

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
 
Top