XLookup Replacement

[typod]

Hi have a spreadsheet that is doing a lot of calculations in order to gather data from multile sheets where multiple fields needs to be matched.  I have got most of the calculations performing really well, but there is an XLookup command that is currently taking several minutes to process around 8,000 lines.

=XLOOKUP(A2 & B2 & C2,'Schedule'!A:A &'Schedule'!B:B & 'Schedule'!C:C,'Schedule'!AN:AN) - This is in column F for this example

What this does it match 3 elements in one sheet againsts the same 3 entries in another and then returns the value in AN of the second sheet to the session column in sheet 1.  An example of the data:

Sheet 1

A	B	C	D	E	F
1	Name	Region	Location	Dept	Session
2	Bob	Europe	London	IT
3	Bert	Europe	London	IT
4	Sally	US	New York	Finance
5	Simon	US	San Francisco	Finance

Schedule

Region	Location	Dept	Session
Europe	London	IT	1
Europe	London	IT	1
US	New York	Finance	2
US	San Francisco	Finance	3

Would Power Query be a better way of acheiving the same result and would it be faster.  Happy to look at any other suggestions to speed things up as I may be totally heading in the wrong direction.  

[typod]

Thanks so much.  I will take a look at this now

[BA_Pete · Accepted solution]

Hi @typod ,

Yes, I would generally say PQ is far easier/faster for this type of thing.

In PQ, all you would need to do is:

1) Import your Sheet1 and Schedule tables

2) Select your Sheet1 table query and go to Home tab > Merge Queries

3) Select you Schedule table from the dropdown at the bottom of the dialog that opens

4) Holding down Ctrl, select the three fields in your Sheet1 table that you want to match to the schedule table

5) Do the same again IN THE SAME ORDER on your Schedule table below (you should see little numbers in the column headers of both table previews showing the selections and the order in which they will be matched)

6) Keep the default Left Outer Merge type

7) Expand the resulting nested-table column, selecting your Schedule[Session] column to expand

Pete