More efficient way to perform multi table lookup?

[Tipsey]

Hi I have a function performing the task I want it to, but I am curious if anyone can think of a better and more efficient way of accomplishing the task.

The idea is to type an employee # into a table, and then attempt to find out if that employee has a scheduled shift today.
Employee shifts are located in 6 separate tables, so I have made a formula like:

=XLOOKUP([@Number],Table1[Emp '#],Table1[Start],XLOOKUP([@Number],Table2[Emp '#],Table2[Start],XLOOKUP([@Number],Table3[Emp '#],Table3[Start],XLOOKUP([@Number],Table4[Emp '#],Table4[Start],XLOOKUP([@Number],Table5[Emp '#],Table5[Start],XLOOKUP([@Number],Table6[Emp '#],Table6[Start],"not in today"))))))

This does achieve the desired outcome, but as mentioned I would like to know if there is a better way.

Thanks

 

[HighAndWilder]

Hi I have a function performing the task I want it to, but I am curious if anyone can think of a better and more efficient way of accomplishing the task.

The idea is to type an employee # into a table, and then attempt to find out if that employee has a scheduled shift today.
Employee shifts are located in 6 separate tables, so I have made a formula like:

=XLOOKUP([@Number],Table1[Emp '#],Table1[Start],XLOOKUP([@Number],Table2[Emp '#],Table2[Start],XLOOKUP([@Number],Table3[Emp '#],Table3[Start],XLOOKUP([@Number],Table4[Emp '#],Table4[Start],XLOOKUP([@Number],Table5[Emp '#],Table5[Start],XLOOKUP([@Number],Table6[Emp '#],Table6[Start],"not in today"))))))

This does achieve the desired outcome, but as mentioned I would like to know if there is a better way.

Thanks

The first question is why are you storing employee shift data in so many different tables?

 

[Fluff]

If the Employee number is the 1st column in those table you could use something like

=VLOOKUP([@Number],VSTACK(table1,table2,table3),2,0)

 

[Tipsey]

The first question is why are you storing employee shift data in so many different tables?

It is a 24 hour operation, with 3 different shifts and 2 different roles. It is what it is :D

 

[Tipsey]

If the Employee number is the 1st column in those table you could use something like

=VLOOKUP([@Number],VSTACK(table1,table2,table3),2,0)

Unfortunately the employee number is in the 4th column and the start time is in the second column.

=HSTACK(VSTACK(DROP(TAKE(Table1,,4),,3),DROP(TAKE(Table2,,4),,3),DROP(TAKE(Table3,,4),,3),DROP(TAKE(Table4,,4),,3),DROP(TAKE(Table5,,4),,3),DROP(TAKE(Table6,,4),,3)),VSTACK(DROP(TAKE(Table1,,2),,1),DROP(TAKE(Table2,,2),,1),DROP(TAKE(Table3,,2),,1),DROP(TAKE(Table4,,2),,1),DROP(TAKE(Table5,,2),,1),DROP(TAKE(Table6,,2),,1)))

works, but is very ugly

 

[Fluff]

In that case you could use

=VLOOKUP([@Number],CHOOSECOLS(VSTACK(table1,table2,table3),4,2),2,0)