Forum Discussion
Conditional Lookup Based on Two Different Tables to Produce Pivot
That's better to restructure data a bit and do everything with data mode, but it takes some time plus entire logic is unclear.
As variant you may use formulas for
(New) Sector
=IFNA( LOOKUP( 2, 1/ ([@Name] & [@Company] &[@[Original Sector]] = Lookup_1st[Name] & Lookup_1st[Company] & Lookup_1st[Original Sector] ) / ( ([@Year]*100+[@Month]) >= (YEAR(Lookup_1st[Start])*100 + MONTH(Lookup_1st[Start]) ) ) / ( ([@Year]*100+[@Month]) <= ( YEAR(Lookup_1st[End])*100 + MONTH(Lookup_1st[End]) ) ), Lookup_1st[New Sector] ), "")(New) Department
=IFNA( LOOKUP( 2, 1/ ([@Name] & [@Company] &[@[Original Sector]] = Lookup_1st[Name] & Lookup_1st[Company] & Lookup_1st[Original Sector] ) / ( ([@Year]*100+[@Month]) >= (YEAR(Lookup_1st[Start])*100 + MONTH(Lookup_1st[Start]) ) ) / ( ([@Year]*100+[@Month]) <= ( YEAR(Lookup_1st[End])*100 + MONTH(Lookup_1st[End]) ) ), Lookup_1st[New Department] ), "")PivotTable doesn't show months without data, nothing special to do
That's better to restructure data a bit and do everything with data mode, but it takes some time plus entire logic is unclear.
As variant you may use formulas for
(New) Sector
=IFNA( LOOKUP( 2, 1/
([@Name] & [@Company] &[@[Original Sector]] = Lookup_1st[Name] & Lookup_1st[Company] & Lookup_1st[Original Sector] ) /
( ([@Year]*100+[@Month]) >= (YEAR(Lookup_1st[Start])*100 + MONTH(Lookup_1st[Start]) ) ) /
( ([@Year]*100+[@Month]) <= ( YEAR(Lookup_1st[End])*100 + MONTH(Lookup_1st[End]) ) ),
Lookup_1st[New Sector] ), "")(New) Department
=IFNA( LOOKUP( 2, 1/
([@Name] & [@Company] &[@[Original Sector]] = Lookup_1st[Name] & Lookup_1st[Company] & Lookup_1st[Original Sector] ) /
( ([@Year]*100+[@Month]) >= (YEAR(Lookup_1st[Start])*100 + MONTH(Lookup_1st[Start]) ) ) /
( ([@Year]*100+[@Month]) <= ( YEAR(Lookup_1st[End])*100 + MONTH(Lookup_1st[End]) ) ),
Lookup_1st[New Department] ), "")
PivotTable doesn't show months without data, nothing special to do
- Thank you so much for the help. This really helped.
I have a few questions to understand this.
1) Why use the LOOKUP rather than another lookup? Even on MS support page on explaining the function tip says use a newer lookup.
2) How did you paste in the formula in those black boxes in your reply? It's really useful.
3) The same way you made the formula in the black boxes, how did you put it within those five lines; that is, how did you add the return/new line after the 1/ in line one and continue in line 2 and so on? I saw this was the same in Excel and makes the formula much easier to read. You also have spaces like in line 3 with (_([@Year].... I used an underscore to show that space. How did you do this without Excel giving you an error?
4) the 1/ in line one, what is it doing? The / or divide sign? It's used in lines 2 and 3 but they look to divide the wholes in parentheses but this 1/ confuses me as to what it's doing.
Thank you so much for the help. I ask these questions so I can learn and improve my skills. Thank you again.Jpalaci1 , you are welcome.
1) On the one hand such pattern with LOOKUP() exists for about 30 years, that's actually for what this function was designed. On the other hand that's my personal preferences, I prefer LOOKUP(), INDEX/MATCH and XLOOKUP(), never used VLOOKUP() and HLOOKUP(). But that's up to you which one to use.
2) Replying on post find this "Insert code" icon in editor bar
click on it, select Excel Formula and paste the formula
3) That's just formatting. Expand formula bar, with long formula insert new line by Alt+Enter, use spaces to separate part of formulas. Space and new lines are ignored during formula evaluation.
In formula bar such formula looks like
Alternatively without such formatting
or
=IFNA(LOOKUP(2,1/([@Name]&[@Company]&[@[Original Sector]]=Lookup_1st[Name]& Lookup_1st[Company]&Lookup_1st[Original Sector])/(([@Year]*100+[@Month])>= (YEAR(Lookup_1st[Start])*100+MONTH(Lookup_1st[Start])))/(([@Year]*100+[@Month])<=( YEAR(Lookup_1st[End])*100+MONTH(Lookup_1st[End]))),Lookup_1st[New Sector]),"")Much harder to read and maintain.
4) Yes, that's division. 1/(divider)
