Forum Discussion
Look up in table based on 4 values
NikolinoDE Thank you for your reply and tips... Attached is a sample. The gray fields are the input fields. If a customer shopped at Publix and bought 7 rolls the cost per roll would be $.50 as indicated by the orange F3 cell. Hope this helps...
As variant
in E14
=XLOOKUP(
D14,
($A$2:$A$9&$B$2:$B$9&$C$2:$C$9=A14&B14&C14)*$D$2:$D$9,
$F$2:$F$9,
"no such",
-1
)SergeiBaklan Love this simple formula. But what if the table value is spaces where the Dept does not apply... meaning the price is not dictate by Dept? For example, Farmer Jacks; If the Customer enters Store = Farmer Jack, Product = Rolls, Dept = Baked Goods and if you look up in the tables Stores, Product and the Dept is spaces in the table, skip it and check Starting Range. Otherwise, lookup the Stores, Product, Dept and Starting Range.
If I understood correctly result shall be
Hope you have LET function, when it could be
=LET( Quantity, D25, Store, A25, Product, B25, Dept, C25, Stores, $A$2:$A$20, Products, $B$2:$B$20, Depts, $C$2:$C$20, startRange, $D$2:$D$20, priceEach, $F$2:$F$20, shortCriteria, Store&Product, shortRange, Stores&Products, noDpts, ISNA(XMATCH(Store&Product&Dept,Stores&Products&Depts)), criteria, Store&Product & IF(noDpts, "", Dept), lookupRange, Stores&Products & IF(noDpts, "", Depts), PricePerRoll, XLOOKUP(Quantity,(lookupRange=criteria)*startRange,priceEach,"no such", -1), PricePerRoll)IT could be without LET() - formula will be shorter but harder in maintenance
SergeiBaklan Interesting.... the below spreadsheet doesnt work. How do I know if I have the LET function?