INDEX(XMATCH) return xx:xx

[carsten510]

Trying to figure out how to solve this.

This table contains all figures;

And in this table I want to have the "Time" populated for every day:

What to put in cell K3? I want it to show "06:52", since all 4 conditions are fulfilled (the date is "2023-12-01", the number is "19", it is "in" and it is "office" 

I have tried INDEX(XMATCH), but cannot reach the solution..

[#2 · Accepted solution]

@carsten510 

=IFERROR(INDEX($E$3:$E$18,MATCH(1,($B$3:$B$18=$H5)*($D$3:$D$18=$I5)*($C$3:$C$18=$J5)*($A$3:$A$18=K$4),0)),"")

This formula works in my file. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

[#3]

Hi @carsten510 

Given you seem to have only 1 Time per What/InOut/Who I would do it with a Pivot Table (attached)

[#4]

Thanks a lot! Works perfect!

[#5]

Thanks! I used the other solution, since I added more elements, and couldn´t find a nice table with Pivot.

[#6]

You're welcome & Thanks for providing feedback
Feel free to click on 'Mark as solution' (at the bottom of each reply you get here) to help people who Search - Thanks