Array formula

[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've also posted this at(both postings have a sample workbook you could view):
https://chandoo.org/forum/threads/help-with-an-array-formula.37481/
https://www.excelforum.com/excel-general/1221268-array-formula-help.html#post4850125

 

[cerfani]

Re: Array formula please help

Try stepping through the calculation and seeing which specific part of the formula results in an error then focus on that. You can highlight parts of the formula and press F9 to see what a nested function results in.

 

[lwilt]

Re: Array formula please help

Try stepping through the calculation and seeing which specific part of the formula results in an error then focus on that. You can highlight parts of the formula and press F9 to see what a nested function results in.

Okay...I'll give that a shot. I know it's worked fine before that only change I made as adding two extra columns to sheet2 but I adjusted for that in the formula so it has me confused at the moment.

 

[lwilt]

Re: Array formula please help

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)))