Selected Answer

I understand your task as follows.

"Find the *Size* in Sheet2 which matches the first description in column C in Sheet2.Column(Ax)

then concatenate that datum with

the datum found at the intersection of the second description in column C in Sheet2.Column(Bx) and the column specified in column D."

Based on this understanding the desired result in you have in E4 should be wrong, but perhaps I don't understand correctly. I am taken aback by your sample data in column C ("A1,B1") which always specify the same row in Sheet2.

Anyway, to produce a solution to fit my understanding I created 3 named ranges on Sheet2 as follows.

*DataA* =Sheet2!$A$2:$B$5
*DataB* =Sheet2!$C$2:$F$5
*Zones* =Sheet2!$D$1:$F$1

The formula below refers to thesed ranges. You can paste it in E3 of your trial worksheet.

`=VLOOKUP(TRIM(LEFT($C3,FIND(",",$C3)-1)),DataA,2,TRUE)&"_"&VLOOKUP(TRIM(MID($C3,FIND(",",$C3)+1,50)),DataB,MATCH($D3,Zones,0)+1,TRUE)`

The structure of this formula is explained in the attached workbook's Sheet1, columns F:S. Note that the TRIM function is added prophylacticly because I don't know how the data in your column C are entered. If it is done by humans TRIM is a good idea. If it is done by computer it might be superfluous.