Index Match assign department based on employee and Job

[excelnovice2000]

Hello Guru
Can I get some help in solving my excel problem.

If the employee is working on J366 then the dept should be picked from codes tab. If the employee is working on Management 2021 or PTO 2021 then the dept should be picked from emp tab

Example: if Employee-5 works on J366 the dept is TIP. If Employee-5 works on Management 2021 or PTO 2021 then the dept should be PIE.

Thanks for all your help.

 

[Marcilio]

excelnovive2000, Good morning.

Try to use:
E4 -->

=IF(OR(D4="Management 2021";D4="PTO 2021");INDEX(emp[Depts];MATCH(B4;emp[Employee];0));INDEX(codes[Dept];MATCH(D4;codes[Analytic Account];0)))

Push it down as necessary.

Is this what you want?

I hope it helps.

 

[bosco_yip]

Another option

In "hours" tab E4, formula copied down :

=IF(OR(D4={"Management 2021","PTO 2021"}),VLOOKUP(B4,emp!B:C,2,0),VLOOKUP(D4,codes!C:D,2,0))

Regards

 

[excelnovice2000]

excelnovive2000, Good morning.

Try to use:
E4 -->

=IF(OR(D4="Management 2021";D4="PTO 2021");INDEX(emp[Depts];MATCH(B4;emp[Employee];0));INDEX(codes[Dept];MATCH(D4;codes[Analytic Account];0)))

Push it down as necessary.

Is this what you want?

I hope it helps.

Thanks Marcilio. That worked. Really appreciate your help

 

[excelnovice2000]

Another option

In "hours" tab E4, formula copied down :

=IF(OR(D4={"Management 2021","PTO 2021"}),VLOOKUP(B4,emp!B:C,2,0),VLOOKUP(D4,codes!C:D,2,0))

Regards

Hi Bosco,
Thanks for your help. That worked. really appreciate your help

 

[Marcilio]

excelnovice2000, Good morning

You're welcome.

Thanks for the feedback and glad to help.