Forum Discussion
Look up in table based on 4 values
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?
In general LET() shall be available for all Office365 subscribers except ones on semi-annual channel. For them it shall be available at the beginning of next year. You may start typing in any empty cell =LE
If you see LET in the list you have it
Without LET() formula could be
=XLOOKUP(D25, ($A$2:$A$20&$B$2:$B$20 & IF(ISNA(XMATCH(A25&B25&C25,$A$2:$A$20&$B$2:$B$20&$C$2:$C$20)), "", $C$2:$C$20)= A25&B25 & IF(ISNA(XMATCH(A25&B25&C25,$A$2:$A$20&$B$2:$B$20&$C$2:$C$20)), "", C25))* $D$2:$D$20, $F$2:$F$20, "no such", -1 )Please check in attached file.
SergeiBaklan How can we code it if there is not Start Range?
Meaning the price does not depend on the quantity... see attached Thanks greatly for your help!