SOLVED

# INDEX(XMATCH) return xx:xx

Copper Contributor

# INDEX(XMATCH) return xx:xx

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..

5 Replies
best response confirmed by carsten510 (Copper Contributor)
Solution

# Re: INDEX(XMATCH) return xx:xx

=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.

# Re: INDEX(XMATCH) return xx:xx

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

# Re: INDEX(XMATCH) return xx:xx

Thanks a lot! Works perfect!

# Re: INDEX(XMATCH) return xx:xx

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

# Re: INDEX(XMATCH) return xx:xx

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
1 best response

Accepted Solutions
best response confirmed by carsten510 (Copper Contributor)
Solution

# Re: INDEX(XMATCH) return xx:xx

=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.