FINDING AN EXACT MATCH ON 2 DIFFERENT SHEETS THEN EXECUTING A FORM

[Morton Detwyler]

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 B
=IF(COLUMNS($B2:B2)<=COUNT('SHEET1'!$B6:$Y6),INDEX('SHEET1'!$B6:$Y6,MATCH(TRUE,ISNUMBER('SHEET1'!$B6:$Y6),0)+COLUMNS($B2:B2)-1),"")

CELL C
=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!

 

[T. Valko]

Not following you on this.

Is this what you have:

Sheet1
...............1.....2.....3.....4
Prod1
Prod3
Prod4

Sheet2
................1.....2.....3.....4
Prod1.....5.....3.....1.....2
Prod2...................2.....3
Prod3..........................4
Prod4............1.....0.....2
Prod5...................1.....1

You want to bring the numbers from sheet2 over to sheet1 for products that
match?

 

[Shane Devenshire]

Hi,

Bracket your formulas with

=IF(ISNA(VLOOKUP($A2,Sheet2!$A$2:$A$1000,1,FALSE)),"",your formula here)

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Morton Detwyler]

Hi Shane,
Thanks so much for the help. This worked very well. Unfortunately, it has
raised one other issue that I didn't realize. The original formula has hard
coded references against SHEET1 (extracted below), and copying the formula
down many rows in SHEET2 simply changes the row refernce; i.e. SHEET2 Row 6
has 'SHEET1'!$B6:$Y6, SHEET2 Row 7 has 'SHEET1'!$B7:$Y7, SHEET2 Row 8 has
'SHEET1'!$B8:$Y8, etc.

The problem is, I need to have those hard coded references on SHEET2 refer
to the actual row number where the Product name was found on SHEET1. Your
formula returns blank if the Product name from SHEET2 is not matched on
SHEET1, and that works great. 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 below
needs to reference SHEET1 Row 5. The original formula is below. Thanks so
much for your help.

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

Hi,

Bracket your formulas with

=IF(ISNA(VLOOKUP($A2,Sheet2!$A$2:$A$1000,1,FALSE)),"",your formula here)

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

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!