Spreadsheet: 876892d1723609748-xlookup-table-references-and-different-worksheets-matt-vb-xlookup-table-references-365-aligw
Sheet: Task Tracking

Task Tracking Table |  | The problem I'm having is the formulas in Table1, [NAME]. I need to use table references because the Project Info Table  (Table2) must be a table. In the actual document it is a power query table so depending on the project, it could be different sizes. In the actual document, I've made the data types and formats "Text" for all the Task Number and Subtask Number fields to allow leading zeroes because that's the output from the query. Please help find a formula that works. I would be happy with XLOOKUP, MATCH/INDEX, or any other solution. Thanks!
Table1 | Deliverable Name | Lead | Task Number | Task Name | Subtask Number | [NAME] | [NAME] don't work with table references, whether on this sheet or on different sheets.
 | Deliverable 1 | Name 1 | 0010 |  | 0010 |  | xlookup with &'s for multiple criteria, different sheet |  |  |  |  |  |  |  |  | =XLOOKUP([@[Task Number]]&[@[Subtask Number]],Table2[Task Number]&Table2[Subtask Number],Table2[Subtask Name],"NF",0,1)
 | Deliverable 2 | Name 2 | 0010 |  | 0010 |  | xlookup with &'s for multiple criteria, same sheet |  |  |  |  |  |  |  |  | =XLOOKUP([@[Task Number]]&[@[Subtask Number]],Table3[Task Number]&Table3[Subtask Number],Table3[Subtask Name],"NF",0,1)
 | Deliverable 3 | Name 3 | 0010 |  | 0020
 | Deliverable 4 | Name 4 | 0020 |  | 0010 |  | [NAME] with boolean, different sheet |  |  |  |  |  |  |  |  | =XLOOKUP(1,([@[Task Number]]=Table2[Task Number])*([@[Subtask Number]]=Table2[Subtask Number]),Table2[Subtask Name],"NF",0,1)
 | Deliverable 5 | Name 5 | 0020 |  | 0020 |  | [NAME] with boolean, same sheet |  |  |  |  |  |  |  |  | =XLOOKUP(1,([@[Task Number]]=Table3[Task Number])*([@[Subtask Number]]=Table3[Subtask Number]),Table3[Subtask Name],"NF",0,1)
 | Deliverable 6 | Name 6 | 0020 |  | 0030
 | Deliverable 7 | Name 7 | 0020 |  | 0030
 | Same sheet copy of Project Info Table
Table3 | Task Number | Task Name | Subtask Number | [NAME]
 | 0010 | Task 1 | 0010 | [NAME]
 | 0010 | Task 1 | 0020 | [NAME]
 | 0010 | Task 1 | 0030 | [NAME]
 | 0020 | Task 2 | 0010 | [NAME]
 | 0020 | Task 2 | 0020 | [NAME]
 | 0020 | Task 2 | 0030 | [NAME]
 | Same sheet copy of Task Tracking (Not a Table)
Not a table | Deliverable Name | Lead | Task Number | Task Name | Subtask Number | [NAME] | [NAME] work when not using table references. But I need this to work with table references because the query will produce tables of different sizes depending on inputs.
 | Deliverable 1 | Name 1 | 0010 | Task 1 | 0010 |  | xlookup with &'s for multiple criteria, same sheet
 | Deliverable 2 | Name 2 | 0010 | Task 1 | 0010 |  | xlookup with &'s for multiple criteria, same sheet
 | Deliverable 3 | Name 3 | 0010 | Task 1 | 0020 |  | xlookup with &'s for multiple criteria, different sheet
 | Deliverable 4 | Name 4 | 0020 | Task 2 | 0010 |  | [NAME] with boolean, same sheet
 | Deliverable 5 | Name 5 | 0020 | Task 2 | 0020 |  | [NAME] with boolean, same sheet
 | Deliverable 6 | Name 6 | 0020 | Task 2 | 0030 |  | [NAME] with boolean, different sheet
 | Deliverable 7 | Name 7 | 0020 | Task 2 | 0030 |  | [NAME] with boolean, different sheet
 | Same sheet copy of [NAME] (Not a Table)
Not a table | Task Number | Task Name | Subtask Number | [NAME]
 | 0010 | Task 1 | 0010 | [NAME]
 | 0010 | Task 1 | 0020 | [NAME]
 | 0010 | Task 1 | 0030 | [NAME]
 | 0020 | Task 2 | 0010 | [NAME]
 | 0020 | Task 2 | 0020 | [NAME]
 | 0020 | Task 2 | 0030 | [NAME]