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,

Answer
Discuss

Answers

0
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.

  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.

Discuss

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: 4) 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: 2165) Dec 6, '18 at 7:59 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login