Excel Formula Help! Price Lookup Formula.

Hi Everyone!

I am trying to create a sophisticated price lookup in excel and cannot determine how to do it.

I have attached a sample workbook called "sample sheet 2.xlsx"

What I need to do is return a price based on the closest match.

For Example if I input:
Product ID: MOSWWFPLJ
WIDTH: 32
DEPTH: 22
HEIGHT: 29

The closest match in my "sample sheet 2.xlsx" sheet would be:

Product ID: MOSWWFPLJ (WILL ALWAYS AN EXACT MATCH, otherwise no price is returned)
WIDTH: 32 (WOULD RETURN EXACT OR GREATER MATCH, IN THIS CASE GREATER MATCH IS 36)
DEPTH: 22 (WOULD RETURN EXACT OR GREATER MATCH, IN THIS CASE GREATER MATCH IS 24)
HEIGHT: 29 (WOULD RETURN EXACT OR GREATER MATCH, IN THIS CASE EXACT MATCH IS 29)

Therefore, a price of MOSWWFPLJ 36"W x 24"D x 29" High would be returned. In my example that price would be $435

If there is no exact or greater match on any field (Width, Depth, or Height), no price will be returned.

Thank you in advance for your help.

OK, picking the right dimensions is a little more complex than just taking the minimum value greater or equal to each dimension. You need the single part that contains dimensions that are ALL above the minimum dimensions specified. Ex. If I need a part with a minimum {32, 22, 42}, I would not pick the first and second dimensions from part {36, 24, 29} and the third dimension from part {72, 24, 42}.

My understanding is that you are looking for the SINGLE PART that where the sum of the differences from the specified dimensions is smallest while still meeting ALL the minimum dimension requirements?

If this is the case, I redesigned your "Closest matching dimensions" cells, as well as the "price of closest matching part" cell.

The solution is an array formula, so you have to enter it by pressing Ctrl+SHIFT+Enter instead of just Enter.

Please Login or Register  to view this content.

See attached solution. Let me know if this works for you.