Look up in table based on 4 values

%3CLINGO-SUB%20id%3D%22lingo-sub-1838626%22%20slang%3D%22en-US%22%3ELook%20up%20in%20table%20based%20on%204%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1838626%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20of%20values%20that%20I%20need%20to%20look%20up%20using%204%20values%3F%3C%2FP%3E%3CP%3ESo%20it%20would%20first%20search%20%2F%20drill%20down%20on%20Stores%2C%20then%20Dept%2C%20then%20Product%2C%20then%20Start%20Range..%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JanStewart_0-1604068388914.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F230426i4C41D10588DB5AEB%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JanStewart_0-1604068388914.png%22%20alt%3D%22JanStewart_0-1604068388914.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1838626%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1839578%22%20slang%3D%22de-DE%22%3ESubject%3A%20Look%20up%20in%20table%20based%20on%204%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1839578%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F816986%22%20target%3D%22_blank%22%3E%40JanStewart%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIf%20I%20may%20ask%2C%20where%20do%20you%20get%20values%20from%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%20%3CSPAN%20class%3D%22%22%3Ewhat%20result%20would%20you%20like%20to%20achieve%20with%20your%20project%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20your%20permission%2C%20if%20I%20can%20recommend.%20It%20can%20help%20us%20all%20if%20you%20upload%20an%20Excel%20file%20(without%20sensitive%20data)%2C%20no%20picture.%20Even%20if%20it%20is%20said%20that%20a%20picture%20can%20say%20a%20thousand%20words%2C%20it%20is%20certainly%20not%20in%20the%20case%20of%20Excel%2C%20on%20the%20contrary%20in%20some%20cases.%20This%20would%20also%20be%20a%20blessing%20for%20all%20of%20us%2C%20as%20we%20can%20understand%20the%20problem%20much%20better%2C%20a%20win-win%20situation%20for%20everyone.%3C%2FP%3E%3CP%3E*%20Knowing%20the%20Excel%20version%20and%20operating%20system%20would%20be%20an%20advantage.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1839645%22%20slang%3D%22en-US%22%3EBetreff%3A%20Look%20up%20in%20table%20based%20on%204%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1839645%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20reply%20and%20tips...%26nbsp%3B%20Attached%20is%20a%20sample.%26nbsp%3B%20The%20gray%20fields%20are%20the%20input%20fields.%26nbsp%3B%20If%20a%20customer%20shopped%20at%20Publix%20and%20bought%207%20rolls%20the%20cost%20per%20roll%20would%20be%20%24.50%20as%20indicated%20by%20the%20orange%20F3%20cell.%26nbsp%3B%20Hope%20this%20helps...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1839794%22%20slang%3D%22en-US%22%3EBetreff%3A%20Look%20up%20in%20table%20based%20on%204%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1839794%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F816986%22%20target%3D%22_blank%22%3E%40JanStewart%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20461px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F230534i00A25916824E82AB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ein%20E14%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(%0A%20%20D14%2C%0A%20%20(%24A%242%3A%24A%249%26amp%3B%24B%242%3A%24B%249%26amp%3B%24C%242%3A%24C%249%3DA14%26amp%3BB14%26amp%3BC14)*%24D%242%3A%24D%249%2C%0A%20%20%24F%242%3A%24F%249%2C%0A%20%20%22no%20such%22%2C%0A%20%20%20-1%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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..  

JanStewart_0-1604068388914.png

 

6 Replies
Highlighted

@JanStewart 

 

If I may ask, where do you get values from?
what result would you like to achieve with your project?

 

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.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

Highlighted

@Nikolino 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...

 

 

Highlighted

@JanStewart 

As variant

image.png

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
)
Highlighted

@Sergei Baklan 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.

Highlighted

@JanStewart 

If I understood correctly result shall be

image.png

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

Highlighted

@Sergei Baklan Interesting.... the below spreadsheet doesnt work.  How do I know if I have the LET function?

JanStewart_0-1606330904636.png