Lookup with multiple "or"-criteria

[sambanimal2]

Hi,

I have been following the forum and it has provided me with some helpful tips along the years. However, this time I would have a problem I was not able to find an answer to.

I would like to have a lookup or query function which contains multiple criteria which can also be in "or" format. See the the screenshot below:



The idea is that for each product and the dimension it would search from a table the correct price. If the price cannot be found, it would return some sort of an error code (does not really matter which).

How would this work in excel?

 

[Special-K99]

If you reformat the table so you only have ONE length per row and ONE height per row you can just do a straight VLOOKUP.
It will make the table longer obviously

 

[sambanimal2]

If you reformat the table so you only have ONE length per row and ONE height per row you can just do a straight VLOOKUP.
It will make the table longer obviously

It does not help in this case as the dataset is in fact much larger.

 

[sambanimal2]

Explaning this a bit further if unclear:

Row 11 has product ABC and width 10, length 100, height 1000:
*Prices should be looked from column O as product is ABC
*Width is 10 which is allowed for rows 3, 5, 6, 7.
*Length is 100 which is allowed for rows 3, 4, 5, 6.
*Height is 1000 which is allowed for rows 5, 6, 7.
-->Consequently it could return cell O5 or O6. As O6 is empty, this could come with a max function.

A more straightforward is maybe row13:
*Product DEF -> column P
*Width 20 -> rows 4,7.
*Length 120 -> row 4
*Height 200 -> rows 3,4,7.
--> Only row 4 matches the criteria. The formula should return the value of cell P4.