Help with an array formula[Solved]

[lwilt]

Hi,

I'm trying to use a formula I've used before but not it's giving me a reference error. I'm trying to move the data from sheet two over into sheet one so that instead of being on a single row it gets transposed into the column where Column A matches for both sheets.

The formulas I was using is:

=IF(INDEX(Sheet2!$A$2:$K$8,MATCH(Sheet1!A2,Sheet2!$A$2:$A$8,0),SMALL(IFERROR(COLUMN($A$1:$K$1)/(LEFT(Sheet2!$A$1:$K$1,3)="Qty"),100000),COUNTIFS(A$2:A2,A2)))=0,"",INDEX(Sheet2!$A$2:$K$8,MATCH(Sheet1!A2,Sheet2!$A$2:$A$8,0),SMALL(IFERROR(COLUMN($A$1:$K$1)/(LEFT(Sheet2!$A$1:$K$1,3)="Qty"),100000),COUNTIFS(A$2:A2,A2))))

=IF(CV2="","",INDEX(Sheet2!$A$2:$K$8,MATCH(Sheet1!A2,Sheet2!A$2:A$8,0),SMALL(IFERROR(COLUMN($A$1:$K$1)/(LEFT(Sheet2!$A$1:$K$1,3)="Pri"),100000),COUNTIFS(A$2:A2,A2))))

I attached a sample workbook.

I've also posted this at:
https://www.excelforum.com/excel-general/1221268-array-formula-help.html#post4850125

https://www.mrexcel.com/forum/excel-questions/1044688-array-formula-please-help.html#post5014834

 

[lwilt]

I attached a new sample workbook with what I would like the desired output to look like.

 

[lwilt]

solved

=INDEX(Sheet2!$B$2:$K$8,MATCH($A2,Sheet2!$A$2:$A$8,0),SMALL(IF(LEFT(Sheet2!$B$1:$K$1)=T$1,COLUMN(Sheet2!$B$1:$K$1)-1),COUNTIF($A$2:$A2,$A2)))

 

[bosco_yip]

Another option,

I put the result in "Result" Sheet Column E and F

1] In E2, copied down :

=IFERROR(1/(1/INDEX(Sheet2!$B$2:$K$8,MATCH(LOOKUP("zzz",A$2:A2),Sheet2!$A$2:$A$8,0),IF(A2<>"",1,ROW()-MATCH("zz",A$1:A2)+1)*2-1)),"")

2] In F2, copied down :

=IFERROR(1/(1/INDEX(Sheet2!$B$2:$K$8,MATCH(LOOKUP("zzz",A$2:A2),Sheet2!$A$2:$A$8,0),IF(A2<>"",1,ROW()-MATCH("zz",A$1:A2)+1)*2)),"")

Regards
Bosco

 

[bosco_yip]

solved

=INDEX(Sheet2!$B$2:$K$8,MATCH($A2,Sheet2!$A$2:$A$8,0),SMALL(IF(LEFT(Sheet2!$B$1:$K$1)=T$1,COLUMN(Sheet2!$B$1:$K$1)-1),COUNTIF($A$2:$A2,$A2)))

1] I think this formula used in the condition of : In the "SKU" Criteria Column A, all blank cells should be filled back with the upper cell data

2] But, my above post suggested non-array formula all blank cells need not filled back with data

3] If the "SKU" Criteria Column A filled back with data, you can use this non-array/shorter formula instead.

3.1] In B2, copied right to C2 and all copied down :

=IFERROR(1/(1/INDEX(Sheet2!$B$2:$K$8,MATCH($A2,Sheet2!$A$2:$A$8,0),COUNTIF($A$2:$A2,$A2)*2-(B$1="Q"))),"")


Regards
Bosco

 

[NARAYANK991]

Hi Bosco ,

Your formula could do with a change to this portion :

IF(A2<>"",1,ROW()-MATCH("zz",A$1:A2)+1)*2

When rows are inserted above the data , results will no longer be correct when the above is used.

Narayan

 

[bosco_yip]

Hi Bosco ,

Your formula could do with a change to this portion :

IF(A2<>"",1,ROW()-MATCH("zz",A$1:A2)+1)*2

When rows are inserted above the data , results will no longer be correct when the above is used.

Narayan

Hi Narayan,

1] Should it be solved by set-up the result range in Excel Table

2] See attached file.

Regards
Bosco

 

[NARAYANK991]

Hi ,

Sorry , but I have not understood.

See the attached file , where 2 rows have been inserted at the top of the data.

Narayan

 

[bosco_yip]

Hi ,

Sorry , but I have not understood.

See the attached file , where 2 rows have been inserted at the top of the data.

Narayan

Hi Narayan,

1] Further to your attachment, I know what is the problem.

The Match function could be changed to :

(ROW()-MATCH("zzz",INDIRECT("$A$1:A"&ROW($A2)))+1)*2

2] However, I like your Lookup function, it is simpler.

(ROW()-LOOKUP("zzz",$A$2:$A2,ROW($A$2:$A2))+1)*2

3] Appreciate your helpful investigation.

Regards
Bosco