I am using XLOOKUP to basically replace INDEX-MATCH array formula combos in my worksheet.
Hoping to make the workflow faster with this. And I have been successful when using the boolean method for multiple criteria, as long as I reference the whole column for the lookup as ranges.
But I am wondering if it is possible to reference instead the Table and column names Table[Column] so that I have a more dynamic lookup. Whenever I try this, the formula does not work.
Any ideas?
Example:
Data table for columns A-C:
Table name: Inventory
| Item | Colour | Quantity |
|---|---|---|
| Umbrella | Red | 10 |
| Ball | Blue | 20 |
Imagine I declare the item type in cell F1 and Colour in F2, then I want the quantity on F3.
Formula working:
=XLOOKUP(1,(F1=A:A)*(F2=B:B),C:C)
Formula not working:
=XLOOKUP(1,(F1=Inventory[Item])*(F2=Inventory[Colour]),Inventory[Quantity])
