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.

bpeltzer said:

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!