I posted a request for assistance a few months back under the title of
"FINDING THE FIRST ENTERED NUMBER IN A ROW OF DATA AFTER A BLANK CELL" and
received some excellent responses. I ended using a formula from
Biff-Microsoft Excel MVP, that is stated below:

=IF(COLUMNS($B3:B3)<=COUNT('SHEET1'!$B6:$Y6),INDEX ('SHEET1'!$B6:$Y6,MATCH(TRUE,ISNUMBER('SHEET1'!$B6 :$Y6),0)+COLUMNS($B3:B3)-1),"")

This formula worked perfectly, but now there is a new issue that I am
desperately searching for a formula that must incorporate the formula above.

I have a partial list of products in SHEET1 and a complete list of products
in SHEET2. In both sheets, Product names are in column [A] with the heading
of "PRODUCT" in cell A1, and the products listed in cell 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 challenge is to 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.

The formula stated above resides in SHEET2 in rows adjacent to each product
name; i.e. Product #1 is in A2, Product #1 Formula is in cells B2, C2, D2,
etc. The COLUMNS reference does change depending upon the cell it is in, but
that is the only thing that changes in that formula regardless of which cell
it is in - see below for an example.

CELL B2
=IF(COLUMNS($B2:B2)<=COUNT('SHEET1'!$B6:$Y6),INDEX ('SHEET1'!$B6:$Y6,MATCH(TRUE,ISNUMBER('SHEET1'!$B6 :$Y6),0)+COLUMNS($B2:B2)-1),"")

CELL C2
=IF(COLUMNS($B2:C2)<=COUNT('SHEET1'!$B6:$Y6),INDEX ('SHEET1'!$B6:$Y6,MATCH(TRUE,ISNUMBER('SHEET1'!$B6 :$Y6),0)+COLUMNS($B2:C2)-1),"")

Once an exact match of a product from SHEET1 is found on SHEET2, I need the
above formula to execute. If no product match is found, I would like the
cell to remain blank.

I have tried different array and lookup formulas but can not make it work.
Any assistance would be very much appreciated. Thanks for your time and
guidance!