Help needed to create a formula for an apparently complex price list

[osvaldmartins]

Hi, guys.

So I have this price list with around 5,000 different combinations. The list is composed by 5 columns: type of stone, finish, thickness, tile sizes, price.

To make things easier for me and my coworkers, I'm trying to create a drop down "form", based on the original list, where according to the chosen options, we get the price automatically at the end.

So, it would work like this:
- one chooses the following options in the drop down form:
Marble, polished, 2 cm, 30x30
So the formula at the price column searches the original price list for this same combination and delivers the corresponding price.

I tried IF function but in my mind the INDEX MATCH with multiple criteria is the right one to go. But I wasnt able to come up with a formula that actually works.



Can anybody help me?

I dont know if I made myself clear about what I want/need, but in any case, feel free to ask.

Thanks in advance.

[jeffreybrown]

A sample workbook with some expected results would help

[osvaldmartins]

Here it is.

- Price List sheet is the full table
- Options sheet is where the array for the drop down options is
- Drop Down sheet is where the form is. The F2 cell is where I wanna insert the formula so the price comes automatically after "reading" the 4 chosen options (B2, C2, D2, E2).

Thank you again.

[xor]

Maybe you can use the attached.

[osvaldmartins]

NICE! Thanks a lot.

PS. is there a way of obtaining the same result without using CONCAT?

[xor]

In Price List H2:

=B2&C2&D2&E2

Similarly in other places where CONCAT has been used.