Look up in table based on 4 values

Question

I have a table of values that I need to look up using 4 values?So it would first search / drill down on Stores, then Dept, then Product, then Start Range..&nbsp;&nbsp;&nbsp;

nikolinode · Answer

JanStewart&nbsp;&nbsp;If I may ask, where do you get values from?what result would you like to achieve with your project?&nbsp;With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases. This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.* Knowing the Excel version and operating system would also be an advantage.&nbsp;Thank you for your understanding and patience&nbsp;NikolinoI know I don't know anything (Socrates)

janstewart · Answer

NikolinoDE&nbsp;Thank you for your reply and tips...&nbsp; Attached is a sample.&nbsp; The gray fields are the input fields.&nbsp; If a customer shopped at Publix and bought 7 rolls the cost per roll would be $.50 as indicated by the orange F3 cell.&nbsp; Hope this helps...&nbsp;&nbsp;

sergeibaklan · Answer

JanStewart&nbsp;
As variant

in E14
=XLOOKUP(
  D14,
  ($A$2:$A$9&amp;$B$2:$B$9&amp;$C$2:$C$9=A14&amp;B14&amp;C14)*$D$2:$D$9,
  $F$2:$F$9,
  "no such",
   -1
)

janstewart · Answer

SergeiBaklan&nbsp;Love this simple formula.&nbsp; But what if the table value is spaces where the Dept does not apply... meaning the price is not dictate by Dept?&nbsp; &nbsp;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.&nbsp; &nbsp;Otherwise, lookup the Stores, Product, Dept and Starting Range.

sergeibaklan · Answer

JanStewart&nbsp;
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&amp;Product, shortRange, Stores&amp;Products,
 noDpts, ISNA(XMATCH(Store&amp;Product&amp;Dept,Stores&amp;Products&amp;Depts)),
 criteria, Store&amp;Product &amp; IF(noDpts, "", Dept),
 lookupRange, Stores&amp;Products &amp;  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

Forum Discussion

Look up in table based on 4 values

9 Replies

Resources