Forum Discussion
x Lookup help please
Hi - I am new to X- Lookup and I am trying to use the formula to look up 2 criteria's, and return a value based on these. The data table is as below:
In a separate table, I want to extract values from above table using Name (criteria 1) & DATE (criteria 2) and return LOG ON time. And then copy these across all dates in the separate table. Seems simple but cant figure this one out! Any help would be appreciated.
3 Replies
- So to use XLOOKUP on multiple items you can concat the items together:
=XLOOKUP(Name&Date, Table1[Name]&Table1[Date], Table1[LOG ON])
That said, since you want all the dates anyhow why not use FILTER:
=TRANSPOSE(FILTER(Table1[LOG ON], Table1[Name] = name, ""))
and if you are doing this on the whole table why not use a Pivot Table?mtarler thanks for those suggestions, I will give them a try. I have tried a pivot table, but the issue is that I want the log on times to be horizontal, and the names and dates vertical. The pivot table will only allow all to be vertical e.g.
Whereas I would like it to be displayed like this
21/08/2023 22/08/2023 23/08/2023 Ann 9:00 9:00 9:00 Any suggestions?
