Lookup function

cell ranges B7:B21 must be set to time format, please refer to alternative solutions

worksheet or tab name : Sheet1

Cell C7 formula , Drag down and across

Formula:

Please Login or Register  to view this content.

So there is no solution to keep it in the same format?

As the zeros might not work with my requirements, and will give me more manual work which im trying to avoid.

Hi czerwonywilk , Thank You for the feedback

According to the attachment of POST#2, at this time you can use the formula of your 1st floor

Formula:

Please Login or Register  to view this content.

Yes. But the problem is that number 10, 11, 12 are being picked normally, then if there is nothing after it, instead of showing error it shows 22:00.

So if I have 12 at 22:00, and there is no 13 to be found - it will also show 22:00 and continue for every next number.

with Power Query

Time	Monday	Tuesday	Wednesday	Thursday	Friday
16:30:00	6	6	7	7	6
17:00:00	7	7	8	8
17:30:00			9
18:00:00	8	8		9	7
18:30:00	9		10	10
19:00:00		9			8
19:30:00			11
20:00:00	10	10
20:30:00				11	9
21:00:00	11	11	12
21:30:00			13	12	10
22:00:00	12	12			11
22:30:00		13	14	13

Try this formula in C7 and copy across the whole table and down:

=XLOOKUP($B7,INDEX(Sheet2!$E$9:$Q$21,,MATCH(C$6,Sheet2!$C$8:$O$8,0)),INDEX(Sheet2!$C$9:$O$21,,MATCH(C$6,Sheet2!$C$8:$O$8,0)),"Error",0)

That one looks promising Gregb11, I'll try it on original file.

Thanks all for help.