Return Values from Other Tabs

[Adam08780]

I'm trying to return what the values from tab 1 for duty & tab 2 for location in tab 3 log

Tab 1 duty rota

Date	John	Zack
01/02	SDP	NCQ

Tab 2 location

Name	Mon	Tue	Wed	Thu	Fri
John	WFH	Office	Office	WFH	WFH
Zack	Office	WFH	WFH	Office	Office

Tab 3 log

Date	Day	Time	Name	Duty	Location
01/02	Thur	13:00	John	SDP	WFH
01/02	Thur	14:00	Zack	NCQ	Office

I was thinking index match match but couldn't get it to work
I don't want to use VLOOKUP as it'll drain to much processing power
Potentially =IFS with "" somewhere?
Please advise

[#2 · Accepted solution]

@Adam08780 

=INDEX($B$8:$F$9,MATCH(D13,$A$8:$A$9,0),MATCH(B13,$B$7:$F$7,0))

An alternative could be INDEX and MATCH. In cell E7 i've replaced "Thu" by "Thur" to return an exact match.

[#3]

Edited response. Troubleshooting formula later tonight

[#4]

I can't get the formula to work properly for location
It accepts it but doesn't populate the right answer
I've tried changing the names and still doesn't work correctly
What was the formula for duty too?
I've never seen a VERGLIECH function either
Please advise

[#5]

@Adam08780 

Attached is my sample file. VERGLEICH is MATCH in english Excel or COINCIDIR in spanish Excel or..... I've translated the whole formula into english in my first reply. All formulas in the attached file are shown in your language when you open the file.

Formula for duty

=INDEX($B$3:$C$3,MATCH(A13,$A$3,0),MATCH(D13,$B$2:$C$2,0))

Formula for location

=INDEX($B$8:$F$9,MATCH(D13,$A$8:$A$9,0),MATCH(B13,$B$7:$F$7,0))

Both formulas are filled down as shown in the sample file.

[#6]

Thank you very much. I've managed to correct the problem with your guidance