URGENT HELP! Match function with multiple criteria

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

=index(E1:E37,min(if((B2<=B5:B37)*(C2<=C5:C37)*(D2<=D5:D37),row(E5:E37)))) as CSE
in range(b5:d37) must be numbers

Hi Tim.

Thank you for your reply. I tried this formula and it returned the value 435 which is great! But it did not seem to take into the account the product ID of MOSWWFPLJ.

Since I have over 20,000 product ID's how can I ensure it pulls the price based on the product ID as well. See attached updated example "Sample Sheet 2.xlsx".

Thank you for all your help!

=index(E1:E37,min(if((a2=a5:a37)*(B2<=B5:B37)*(C2<=C5:C37)*(D2<=D5:D37),row(E5:E37))))

I suggest such a formula.

Formula:

Please Login or Register  to view this content.

Hi Czeslaw.

Placed the formula in my excel sheet but got an error.

This should work:

=INDEX(E5:E51,MIN(IF((A5:A51=A2)*(B5:B51>=B2)*(C5:C51>=C2)*(D5:D51>=D2),ROW(E5:E51))))

Hi ua003.

Your formula works. However, if I try to change any of my input values it is not finding any other price than $435.

For example if I change 32 Width to 72 Width I am still getting 435 but it should be returning 562

Bring all the data in the columns to the number format. (38_MOSWHFPRR 24 20 29,00 850,00)

Hi Czeslaw.

When i try to change any of the input fields i just get #NAME? error

You enter values that do not match the values of your data.
See another modified example.