Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FINDING AN EXACT MATCH ON 2 DIFFERENT SHEETS THEN EXECUTING A FORM
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FINDING AN EXACT MATCH ON 2 DIFFERENT SHEETS THEN EXECUTING A FORM
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? -- Biff Microsoft Excel MVP "Morton Detwyler" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FINDING AN EXACT MATCH ON 2 DIFFERENT SHEETS THEN EXECUTING A FORM
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" wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FINDING AN EXACT MATCH ON 2 DIFFERENT SHEETS THEN EXECUTING A
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),"") "Shane Devenshire" wrote: 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" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
Lookup between sheets, finding a match, and returning a letter | Excel Worksheet Functions | |||
Find exact match, same workbook, different sheets | Excel Discussion (Misc queries) | |||
vlookup and finding text string that's not an exact match | Excel Discussion (Misc queries) | |||
finding exact matches | Excel Worksheet Functions |