# Referencing

0

Hello there,

I have a little bit complex case here, I'm looking for a formula that will display the data in cell E3:E6 with referencing column C&D from sheet 2 and every time I update column C it will display it in column E based on the second sheet data.

I was thinking about lookup function but didn't know how to do that.

attached is an example please, know if you need any info,

0

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.

1. DataA  =Sheet2!\$A\$2:\$B\$5
2. DataB =Sheet2!\$C\$2:\$F\$5
3. 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.

### Discussion

I'm sorry for the bad short explanation it was so confusing but you got this as well I appreciate that! The only issue is that there are no limits for the data I tried to add if statement but it didn't work for any of these groups
DataA  =Sheet2!\$A\$2:\$B\$5 DataB =Sheet2!\$C\$2:\$F\$5 Zones =Sheet2!\$D\$1:\$F\$1 Would you help me with that?
For example, if this data in the range of these cells then apply it if not apply ***

Thank you so much.
Jana86 (rep: 6) Dec 6, '18 at 8:15 am
First, of course I shall help you. Second, I'm afraid I dont fully understand your problem, especially the last sentence. Third, this isn't the place to discuss it. Ask another question.
In the formulas, you can always replace the name of a named range with the address. Instead of DataA you can write Sheet2!\$A\$2:\$B\$5 and modify that address in whatever way. However, you can also modify the address of a named range in the same ways, and that has the advantage of doing it in only one place instead of changing multiple formulas. If your problem is about making a named range dynamic, please ask a question with that title.
Variatus (rep: 3758) Dec 6, '18 at 7:59 pm