RELATIVE REFERENCE WHEN MATCHING 2 PRODUCTS

[Morton Detwyler]

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

Thank you for your help!

 

[bpeltzer]

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]

Thanks for your help

SHEET2 contains a full product list, and SHEET1 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 reason for the extended formula is the percentages pulled from SHEET1
must be left justied on SHEET2. On SHEET2, there must be formulas in every
cell adjacent to every product name in columns B thru Y. When the formula on
SHEET2 does not find a match on SHEET1, it must return a blank.

The formula works fine as long as it finds a match where the row numbers on
SHEET1 and SHEET2 are the same. The problem I've run into, is if a product
name on SHEET2 is in Row 12, and the matching product name on SHEET1 is in
Row 5, my current formula returns data from SHEET1 Row 12. I need my formula
to return the product percentages from the row in SHEET1 of the product that
was matched from SHEET2. So, 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). This is why I only need assistance in my original formula
where it references SHEET1.

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

The other aspects of the formula work perfectly.

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.

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

Thank you for your help!

 

[bpeltzer]

Are there 'holes' in the data on Sheet1 (eg could columns B and D be
populated while column C of the same row is blank)? If not, then this
certainly could be simpler. But if so, then to address the immediate issue...
replace the references to 'SHEET1'!$B6:$Y6 with
OFFSET('SHEET1'!$B1:$Y1,MATCH($A3,'SHEET1'!$A$6:$A$50,0)-1,0)

Thanks for your help

SHEET2 contains a full product list, and SHEET1 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 reason for the extended formula is the percentages pulled from SHEET1
must be left justied on SHEET2. On SHEET2, there must be formulas in every
cell adjacent to every product name in columns B thru Y. When the formula on
SHEET2 does not find a match on SHEET1, it must return a blank.

The formula works fine as long as it finds a match where the row numbers on
SHEET1 and SHEET2 are the same. The problem I've run into, is if a product
name on SHEET2 is in Row 12, and the matching product name on SHEET1 is in
Row 5, my current formula returns data from SHEET1 Row 12. I need my formula
to return the product percentages from the row in SHEET1 of the product that
was matched from SHEET2. So, 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). This is why I only need assistance in my original formula
where it references SHEET1.

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

The other aspects of the formula work perfectly.

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.

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

Thank you for your help!

 

[Morton Detwyler]

This worked perfectly! I can't thank you enough for your expert guidance.
I've been struggling with this problem for a few days, and was out of ideas.
Again, thank you....you really helped me out

Are there 'holes' in the data on Sheet1 (eg could columns B and D be
populated while column C of the same row is blank)? If not, then this
certainly could be simpler. But if so, then to address the immediate issue...
replace the references to 'SHEET1'!$B6:$Y6 with
OFFSET('SHEET1'!$B1:$Y1,MATCH($A3,'SHEET1'!$A$6:$A$50,0)-1,0)

Thanks for your help

SHEET2 contains a full product list, and SHEET1 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 reason for the extended formula is the percentages pulled from SHEET1
must be left justied on SHEET2. On SHEET2, there must be formulas in every
cell adjacent to every product name in columns B thru Y. When the formula on
SHEET2 does not find a match on SHEET1, it must return a blank.

The formula works fine as long as it finds a match where the row numbers on
SHEET1 and SHEET2 are the same. The problem I've run into, is if a product
name on SHEET2 is in Row 12, and the matching product name on SHEET1 is in
Row 5, my current formula returns data from SHEET1 Row 12. I need my formula
to return the product percentages from the row in SHEET1 of the product that
was matched from SHEET2. So, 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). This is why I only need assistance in my original formula
where it references SHEET1.

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

The other aspects of the formula work perfectly.

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.

:

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

Thank you for your help!