It’s an often-overlooked fact that XLOOKUP needn’t work with just a single lookup value or even a single search column. The search value is straight forward, you’d expect that a single value could be a string pieced together from multiple sources.
Before going further let’s look at an example, this first sheet of data represents three work sections (A, B and C) where team members can be loaned from section to section.
To keep track of who is where, a spreadsheet is created to track the team members, where they were loaned from and to. A simple COUNTA totals the number of people loaned in and out for each section
So far so good, but when it comes time to summarise this data it quickly becomes clear it’s not as easy as it at first seems
The first thing to note is that there are twice the number of rows, and in other scenario there could be an undefined number of rows for each day…
Lets look at the XLOOKUP formula for the “In” column.
=XLOOKUP($N3 & $O3 & "Out", $B:$B & $C:$C & $D:$D, $L:$L)
Here column N & O are the Date and Section column we want to find.
Column B C and D are on the first table and are the corresponding Date, Section and the column identifying the row as either load Ins or loan Outs
Finally, the L column (in the first spreadsheet) is the (COUNTA) section total of load outs.
This works just fine, however given a much larger sample of dates you will soon find things lagging, this is due to the amount of concatenation that XLOOKUP is doing, and you don’t really have to help it out too much to make it more optimised.
A hidden column is added to the first table, this uniquely identifies the row in a manner we can use with XLOOKUP the formula now becomes
=XLOOKUP($N2 & $O2 & "In", $E:E, $K:$K)
You could also do the same for the value that is looked up in practice, this isn’t what take up the main of the time.
While this was a somewhat contrived example (for the sake of illustration), and there would likely be better ways to gather and analyse this data, I hope this has given you an insight as to just how powerful XLOOKUP can be and how you can (ab)use it in a wide range of ways to get the answers you are looking for.