I am trying to create a home inventory list that will eventually combine a recipe list that will automatically subtract amounts form the inventory. Right now I am stuck on pulling information from the 3 sheets (Freezer, Fridge and Pantry) to the total inventory page. I am using the following XLOOKUP function and it is giving me this error code:
=XLOOKUP($D18,Freezer!$B$5:$B$100,Freezer!$C$5:$F$100,XLOOKUP($D18,Fridge!$B$5:$B$100,Fridge!$C$5:$F$100),XLOOKUP($D18,Pantry!$B$5:$B$100,Pantry!$C$5:$F$100))
Error Function XLOOKUP parameter 5 expects number values. But 'Imperfect Foods' is a text and cannot be coerced to a number.
I am not that great with Sheets/Excel, but I can't seem to find the solution through Google like I normally do.
Here is a link to the sheet I am having problems with:
https://docs.google.com/spreadsheets/d/1iCJHcTckjPgIjP6XzQpknZM3AejhzRmpqTDxaoO9TOY/edit?usp=sharing
I also tried this formula:
=IF(ISNA(VLOOKUP(D16,Freezer!$B$2:$C$100,2,FALSE)),"",VLOOKUP(D16,Freezer!$B$2:$C$100,2,FALSE))+IF(ISNA(VLOOKUP(D16,Fridge!$B$2:$C$100,2,FALSE)),"",VLOOKUP(D16,Fridge!$B$2:$C$100,2,FALSE))+IF(ISNA(VLOOKUP(D16,Pantry!$B$2:$C$100,2,FALSE)),"",VLOOKUP(D16,Pantry!$B$2:$C$100,2,FALSE))
But it will not pull the entire row of information from the corresponding sheet only the quantity column.
You may try:
=xlookup(D18, {Freezer!B:B;Fridge!B:B;Pantry!B:B}, {Freezer!C:F;Fridge!C:F;Pantry!C:F},)