Array formula help

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.

Showing us the desired results is much better than showing us a non-working formula.

Please update your sample workbook to show the desired results, which you can enter manually.

My apologizes. I re-attached the workbook with a sheet that has what I want the desired output to look like.

First, fill in the blank cells in column A with the correct sku's (let us know if you need help with this).

Then you can use this in T2:

=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))) Ctrl Shift Enter

Drag the formula to the right then down.

Originally Posted by 63falcondude

First, fill in the blank cells in column A with the correct sku's (let us know if you need help with this).

Then you can use this in T2:

=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))) Ctrl Shift Enter

Drag the formula to the right then down.

Thank you...that brought over data for column T. Is there a way for it to not bring over 0 for the blanks? If there's not a simple change no worries I can sort the data at the end to remove them. Could you also help bring over data for column U as well?

Is there a way for it to not bring over 0 for the blanks?

Assuming that there are no 0's in your data, you can use this:

=IFERROR(1/(1/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)))),"") Ctrl Shift Enter

Could you also help bring over data for column U as well?

You put the formula into T2 then drag it to the right (into column U) and then down.
This will populate the numbers for columns T and U.

Thank you for all your help!!

You're welcome. Thanks for the rep!