SO Close and yet so far. Simple answer Im sure.

[trekker1218]

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.

 

[Chrisdontm]

Hello,

I should be able to help you, but I am not following your description completely.

It looks like you are saying that the Item # to lookup is on Sheet4 and in column " F "

Then you want to look up that item # in Sheet10 Column " E "

Then once you find the row # that the item # is on in Sheet10, you want to slide over to the right and match the (Heading of the Column) the " Attribute Label "
which is on Sheet4 and in Column " H "

Then you want to bring back whatever is in the intersecting cells from Sheet10 over to Sheet4 and put it somewhere over there...???

 

[trekker1218]

Yes and no...

=INDEX('Sheet10'!F2:I100,MATCH(Item#,'sheet4'!F2:F100,0)*(MATCH(Attribute Label,'Sheet4'!H2:H100,0))
raw data for items , find matching item #, match attr. label then find label in row and take next cell value

My problem with this formula so far is returning a bad value if I cannot find a match on second part.

I have raw data on sheet10 which contains item# and all the attribute labels and attribute values across the row.
I need move the raw data from Sheet 10 into a another sheet template.
sample:
____________________________________________________________________________________________________________________________________
Sheet10
COL E COL G COL H COL I COL J COL K COL L COL M COL N
____________________________________________________________________________________________________________________________________
Item # | Attribute label 1 | attribute Value 1 | Attribute label 2 | attribute Value 2 | Attribute label 3 | attribute Value 3 | Attribute label 4 | attribute Value 4 |
12345 Brand Streamline Width 23.5" Color Black Capacity gallon

========================================================================================================

Sheet4 = template blank sheet to fill with above data.
COL L:L COL U1 COL V1
Items attr. label Attrr. label
_____________________________________________________________________________________________________________________________________
Item# | label1 | label 2 | label 3 | label 4 |
12345 Brand Width Color HEIGHT


-===================================================================================================
RESULT:

Item#
12324 Streamline 23.5 black (Blank or 0 Result for no match found)

The labels on sheet 4 vary based on product. Can be upto 50 labels. Some mat not be found on RAW data sheet10.
I need formula to take any item# entered on sheet4 COL L;L and retrieve the correct matching data from raw sheet10.

hope this make it clearer.

thanks for all your help in advance.

 

[Chrisdontm]

Try putting this formula into cell " U3 "

=IFERROR(INDEX(Sheet10!$G$1:$DZ$100000,MATCH($L3,Sheet10!$E:$E,0),MATCH(U$1,OFFSET(Sheet10!$A$1,MATCH($L3,Sheet10!$E:$E,0)-1,6,1,124),0)+1),"")

Then copy it from there over to cell " DZ3

Then copy that row of formulas down as far as you need to.

If it doesn't work, then I'm not following something on the instructions...