Matching column and row to get cell data result from different sheet.

[TinoBradica]

Hello,
i have a book full of item numbers for an ecommerce site. The template requires me to segregate the upload data.
I need to Index RAW DATA on sheet10 and place into Sheet4.
Step 1 is to find the matching Item# (Key data) and then read across that entire row to find the matching attribute name then take the next cell data to the right and place into sheet4.

This is where I am now:
=INDEX('Sheet10'!F2:I100,MATCH(Item#,'sheet4'!F2:F100,0)*(MATCH(Attribute Label,'Sheet4'!H2:H100,0))

The attribute labels are not always in same column, which is why i need to read entire column data and find it then take the next cell which is the attribute value and place in the sheet4.

here are the data point.

Sheet4. ColU thrugh DZ1 have all the attribute labels.
Sheet4. Row3 through Row50000 - ColL have all the item#s .

The matching data reside on sheet10.
Col-E has the item #'s and Col G Through DZ has the attribute labels and Attribute Values ACROSS each row with the item number.

any help would be greatlu appreciate.

[Bernie Deitrick]

Try


=INDEX(Sheet10!A:DZ,MATCH(Item#,Sheet10!E:E,FALSE),MATCH(AttributeLAbel,INDEX(Sheet10!A:DZ,MATCH(Item#,Sheet10!E:E,FALSE),0),FALSE)+1)


You may need to add in $ for absolutes, depending on how you are copying the formula to other cells...

[TinoBradica]

FANTESTICLE...

Where do I send the flowers.

THANK YOU... I knew I was close. just needed the push....

[TinoBradica]

It works perfect.

I am getting some bad results due to missing attribute label.

it there a way to return a BLANK value if the formula is N/A or it cannot find the actual attribute label in the raw data.

thanks,

[TinoBradica]

this is actual formula now. based on your input.


=IF(ISERROR((INDEX('Uni-Data1'!$E:$DH,MATCH($L$3,'Uni-Data1'!$E:$E,0),MATCH(U1,INDEX('Uni-Data1'!$E:$DH,MATCH($L$3,'Uni-Data1'!$E:$E,0),0))+1))),"",(INDEX('Uni-Data1'!$E:$DH,MATCH($L$3,'Uni-Data1'!$E:$E,0),MATCH(U1,INDEX('Uni-Data1'!$E:$DH,MATCH($L$3,'Uni-Data1'!$E:$E,0),0))+1)))

[Bernie Deitrick]

Great - but you should learn the IFERROR function:

=INDEX('Uni-Data1'!$E:$DH,MATCH($L$3,'Uni-Data1'!$E:$E,0),MATCH(U1,INDEX('Uni-Data1'!$E:$DH,MATCH($L$3,'Uni-Data1'!$E:$E,0),0))+1)

becomes

=IFERROR(INDEX('Uni-Data1'!$E:$DH,MATCH($L$3,'Uni-Data1'!$E:$E,0),MATCH(U1,INDEX('Uni-Data1'!$E:$DH,MATCH($L$3,'Uni-Data1'!$E:$E,0),0))+1),"")

A little easier to understand and less processor-intesive since the long INDEX formula is not evaluated twice. Maybe not an issue but that can be...

[FDibbins]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

[TinoBradica]

Hi,
Sorry to bother you again. The formula is not working properly when it cannot find a match to the Attribute label.
=IFERROR(INDEX(Fittings!$E:$DH,MATCH($L$4,Fittings!$E:$E,0),MATCH(Z1,INDEX(Fittings!$E:$DH,MATCH($L$4,Fittings!$E:$E,0),0))+1),"")

it is returning a result of the first attribute value in the row.
Z1 is the attribute label in the formula.

Please help once more.

thanks,

[TinoBradica]

the error is when i try to apply the formula across the row to the right. it reads the row fien as far as I can tell, but it will not take the right attribute value from the matching attribute label in Z1.

[TinoBradica]

i attached a sample file to help

[FDibbins]

You will get no further assistance until you have complied with my request in post #7

[TinoBradica]

Done. So sorry. No disrespect intended.

[FDibbins]

None taken, thanks for the title change

[Bernie Deitrick]

Since the labels across the row are not unique, I used a column and a row of match formulas to simplify the function: see the attached.

[TinoBradica]

Sorry I do not see attachment.

[Bernie Deitrick]

Sorry - I will try again...

[TinoBradica]

Hello,
We are so close. I have enclosed a full file of ALL data.
The problem with your approach is the labels are not always in the row in the same place. Or may not appear at all.
Please look at Step 4 sheet ROW 2 - COL AI 1.
The label Finish does not appear in item results for ROW 4 But does for ROW 5 in col 27 on the SHEET FITTINGS.

This is why I was trying to match the item# first and then read the data in the matching row to find the attribute label and take the VALUE in next COLUMN.

Your approach would work great if the labels were constant in same location and for all products.

Thank you again for all your assistance.

[TinoBradica]

file attached

[Bernie Deitrick]

Try this one:

[TinoBradica]

OMG.. Perfect. Thank you again so much.

[Bernie Deitrick]

Great - glad that it worked out..... eventually