Forum Discussion
Solved
Return Values from Other Tabs
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
=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.
=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.
- 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 adviseAttached 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.
Edited response. Troubleshooting formula later tonight
