Conditional Lookup Based on Two Different Tables to Produce Pivot

[Jpalaci1]

I need help making this pivot:

Columns E:G lookup the bottom master lookup (K6:N9). I then thought I would make a second transfer lookup (top one in K1:R4) to fill in columns H:I based on =IFERRORVLOOKUP([@Name],Lookup_Transfer,5,0),[@Original Sector]), which works but then I needed it to be conditional of when it starts. I thought of a IF(AND where if the [@Month} from the Data table is is less than or equal to (I thought this was easiest but gladly take whatever allows my end result) End in column R and less than or equal to YEAR of column R to [@Year] in the Data table that way I get the correct values for columns H:I and then pivot to get the result I'm looking for. I get lost since how would I make it look to column R only for those who are in the transfer table.

Also, is there a way to display a pivotable with only values looking forward? In my example I want years 2021 and 2022 to show months 5-12 for 2021 and month 1 for 2022 but without showing month 1 in 2021. 

Thank you for any help.

[#2 · Accepted solution]

@Jpalaci1 

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

[#3]

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.

[#4]

@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)