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?
XLOOKUP with multiple criteria
-
- 3StarLounger
- Posts: 254
- Joined: 01 Mar 2010, 17:34
- Location: Blue Springs, MO
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: XLOOKUP with multiple criteria
maybe attach representative example excel file with generic data
-
- 3StarLounger
- Posts: 254
- Joined: 01 Mar 2010, 17:34
- Location: Blue Springs, MO
Re: XLOOKUP with multiple criteria
I believe this should be sufficient of an example.
You do not have the required permissions to view the files attached to this post.
Morgan
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: XLOOKUP with multiple criteria
maybe with Power Query
You do not have the required permissions to view the files attached to this post.
-
- 3StarLounger
- Posts: 256
- Joined: 15 Aug 2016, 11:23
Re: XLOOKUP with multiple criteria
Use this formula:
=XLOOKUP(1,(COA!D2:D450=--MID(F2,5,3))*(COA!E2:E450=MID(F2,9,4)),COA!G2:G450)
=XLOOKUP(1,(COA!D2:D450=--MID(F2,5,3))*(COA!E2:E450=MID(F2,9,4)),COA!G2:G450)
-
- 4StarLounger
- Posts: 588
- Joined: 14 Nov 2012, 16:06
Re: XLOOKUP with multiple criteria
Array formula:
=INDEX(COA!$G$2:$G$450;MATCH(MID(F2;5;8);COA!$D$2:$D$450&"-"&COA!$E$2:$E$450;0))
=INDEX(COA!$G$2:$G$450;MATCH(MID(F2;5;8);COA!$D$2:$D$450&"-"&COA!$E$2:$E$450;0))
-
- 3StarLounger
- Posts: 254
- Joined: 01 Mar 2010, 17:34
- Location: Blue Springs, MO