match index question..can't get it to work...help!

[ccarruth]

Have used these functions on other areas but can't get it to work on the attached example. Two sheets, one contains array, the other the "results" cells. Notes in worksheets.

 

[Hui]

B2: =INDEX(Sheet2!$D$2:$D$7,MATCH(A2,Sheet2!$A$2:$A$7,0))
C2: =INDEX(Sheet2!$E$2:$E$7,MATCH(A2,Sheet2!$A$2:$A$7,0))
Copy both down

 

[ccarruth]

Thanks Hui...I made a mistake. Said the formula should key off Col A; should be Col C, with Col A and Co B being derived. Changed array to be actual data.

 

[ccarruth]

Anyone?

 

[p45cal]

If column A is somehow dealt with, then you're left with deriving the SKU#; isn't this simply the bit before the 2nd _ (underline) character of the subSKU#?
If so:
=LEFT(C17,FIND("¬",SUBSTITUTE(C17,"_","¬",2))-1)

If instead it's the bit after the last _ character then:

=LEFT(C17,FIND("¬",SUBSTITUTE(C17,"_","¬",LEN(C17)-LEN(SUBSTITUTE(C17,"_",""))))-1)

Otherwise it'd be a case of scanning columns E,I and M of the Sheet2 for the subSKU# and returning the first or 4th columns of the same row? Have I got this right?

Something like

=INDEX(testsku,IFERROR(MATCH(C17,INDEX(testsku,0,5),0),IFERROR(MATCH(C17,INDEX(testsku,0,9),0),MATCH(C17,INDEX(testsku,0,13),0))),1)

and

=INDEX(testsku,IFERROR(MATCH(C17,INDEX(testsku,0,5),0),IFERROR(MATCH(C17,INDEX(testsku,0,9),0),MATCH(C17,INDEX(testsku,0,13),0))),4)

?

They're cumbersome but I fancy there's amore elegant formula way.
There's also a UDF solution (uses vba code), would that be acceptable?