It feels like you've made this much harder than it should be. My
understanding is that you're trying to pull in info from a row that varies
based on the value in column A. That's really just a vlookup. But since
you're pulling in 12 columns of data, I'd probably use one MATCH function to
get the appropriate row that is the source on SHEET and then a bunch of INDEX
functions to get data from that chosen row.
In Sheet2, cell B12: =match(A12,Sheet1!A:A,0) (I'd probably hide this
column)
In Sheet2, cell C12: =if(isna($B12),"",index(Sheet1!B:B,$B12), and copy
that formula to C12:N12. Then copy B12:N12 to as many rows as needed.
If that's not it, please explain further what you're putting into columns
B:M on Sheet2.

"Morton Detwyler" wrote:

I have 2 sheets in my spreadsheet. I must exactly match a product name from
SHEET2 which contains a full product list, to a product name in SHEET1 which
is only a partial list. The partial list of products in SHEET1 will change
periodically, but the full list of products in SHEET2 will not. In both
sheets, Product names are in column [A] with the heading of "PRODUCT" in cell
A1, and the products listed in cells A2, A3, etc.

If a Product name on SHEET2 is in Row 12, and matches a Product name on
SHEET1 in Row 5, the formula needs to reference SHEET1 Row 5 and retain the
column range of "B" through "Y"; i.e. ('SHEET1'!$B5:$Y5). I only need your
help where it references SHEET1.

=IF(ISNA(VLOOKUP($A3,'SHEET1'!$A$6:$A$50,1,FALSE)) ,"",IF(COLUMNS($B3:B3)<=COUNT('SHEET1'!$B6:$Y6),IN DEX('SHEET1'!$B6:$Y6,MATCH(TRUE,ISNUMBER('SHEET1'! $B6:$Y6),0)+COLUMNS($B3:B3)-1),""))

Thank you for your help!