I have 2 sheets in my spreadsheet. I'm looking for a way to have hard coded
formula references on SHEET2 refer to an actual row number where a Product
name was found on SHEET1 and retain the column range of "B" through "Y".

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, A4, etc.
There are number headings in Row [1] (i.e. the number 1 is in B1, the number
2 is in C1, etc. We do not use month names. There is numeric product data
in each row adjacent to each product name; i.e. Product #1 is in A2, Product
#1 Data is in cells B2, C2, D2, etc.

My formulas are in SHEET2 but have hard coded references against SHEET1, and
copying the formula down many rows in SHEET2 simply changes the row
reference; i.e. SHEET2 Row 6 has 'SHEET1'!$B6:$Y6, SHEET2 Row 7 has
'SHEET1'!$B7:$Y7, SHEET2 Row 8 has 'SHEET1'!$B8:$Y8, etc.

I'm looking for a way to have those hard coded references on SHEET2 refer
to the actual row number where the Product name was found on SHEET1 and
retain the column range of "B" through "Y". My current formula returns blank
if the Product name from SHEET2 is not matched on SHEET1, but executes if a
match is found. But if a Product name on SHEET2 is in Row 12, and it matches
a Product name on SHEET1 that is 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).

ENTIRE FORMULA
=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),""))

SECTION OF FORMULA WHERE I NEED HELP
<=COUNT('SHEET1'!$B6:$Y6),INDEX('SHEET1'!$B6:$Y6,M ATCH(TRUE,ISNUMBER('SHEET1'!$B6:$Y6)

Thank you so much for your help! Many thanks to Shane and Biff for their
help so far.