XLOOKUP with multiple criteria

[Abraxus]

I have a spreadsheet used to record invoices from suppliers. In it, I record the coding for the accounting department. It looks like XXX-YYY-ZZZZ where XXX = the store number (mine never changes) YYY = the Department number (Gift Shop, Bar, etc...) and ZZZ = the General Ledger Account.

I would like to have my spreadsheet show me the Department name and GL Description after I enter them. I have the Department working with a simple XLOOKUP to the tab that has only departments: =IF(F2="","",XLOOKUP(MID(F2,5,3),Departments!A:A,Departments!B:B))

What I'm struggling with is the GL Description because it requires knowing both the Department number and the GL number to select it out of the list of GLs.

I've tried following the example described here: https://exceljet.net/formulas/xlookup-w ... e-criteria

It isn't working for me, and I am unsure why. My formula is: =XLOOKUP(1,(COA!D2:D450=MID(F2,5,3))*(COA!E2:E450=MID(F2,9,4)),COA!G2:G450)

COA is my tab with the list of GLs. Column D is the Department. Column E is the GL Account. I want the value in column G. All I get is #N/A.

Any pointers?

[hamster]

maybe attach representative example excel file with generic data

[Abraxus]

I believe this should be sufficient of an example.

[hamster]

maybe with Power Query

[Toranaga]

Use this formula:

=XLOOKUP(1,(COA!D2:D450=--MID(F2,5,3))*(COA!E2:E450=MID(F2,9,4)),COA!G2:G450)

[snb]

Array formula:

=INDEX(COA!$G$2:$G$450;MATCH(MID(F2;5;8);COA!$D$2:$D$450&"-"&COA!$E$2:$E$450;0))

[Abraxus]

Use this formula:

=XLOOKUP(1,(COA!D2:D450=--MID(F2,5,3))*(COA!E2:E450=MID(F2,9,4)),COA!G2:G450)

This worked perfectly, thank you!!