[Solved] What formula use with INDEX/MATCH?

[Olegs Andrejevskis]

I'm self employed furniture designer. I design a furnitures in 3D software, from where I can get material list (Green). I need some formula to find exact furniture piece by name and extract information from material price list (Red) depending on measurements of this piece. I have already found the INDEX/MATCH formula. But I need a formula that looks at the part name in the correct table. As far as I understand - the VLOOKUP formula will not work here. The list of materials from the 3D software (Green) can also be with more parts with different sizes and quantities.

[Villeroy]

Your data layout is not suitable to be processed easily by any kind of computer program.

[Villeroy]

Anyway. It is just a matter of time that something will get out of sync with this layout.

[Olegs Andrejevskis]

Anyway. It is just a matter of time that something will get out of sync with this layout.

Thank you, Villeroy!
Maybe there is some much better way to layout information from catalog? I can get dimensions and quantities of the panels from 3D software to spreadsheet. I want, somehow, calculate project price with formulas, so I don't have to do it with every detail manually. I add example from price catalog.

[Villeroy]

I don't know anything about your 3D software, how it stores and exports data and why it can't calculate the prices. With the current layout there is one thing you have to fix:

Having 2 columns:
230 39,6
331 49,14
491 56,74
=INDEX(col_2; MATCH(300;col_1)) returns 230
=INDEX(col_2; MATCH(200;col_1)) returns #NA because 200 is smaller than the smallest value 230.
MATCH does not match "up to 230". MATCH matches "230 or higher", "331 or higher", "491 or higher"

Your 2 columns may look like:
1 39,6
231 49,14
332 56,74
Now the formula returns
39,6 for 1 to 230
49,14 for 231 to 331
56,74 for anything above 331

Edit: I may be wrong. You are the one who knows the right match. But you should be aware how MATCH works

[MrProgrammer]

Hi, and welcome to the forum.

Your data layout is not suitable to be processed easily by any kind of computer program.

Agreed. If you intend to use Calc, you will want to arrange your data so that you can use Calc's features. Your 3D data layout (6 by 4 by 4) will be difficult to use in formulas.

Maybe there is some much better way to layout information from catalog?

If the information is entered as one table, the price can be obtained with SUMPRODUCT. The approach used here depends on each name having the same (nine) number of prices. If the sizes vary for different names, a different layout (Name, MinL, MaxL, MinD, MaxD, Price) is needed. The six-column layout would be easier to work with, needing more columns but fewer rows. However, I used the layout here just to experiment with the idea and because that's how the data in your first post is arranged. These are the 16 table entries for BK; the rows for the other five names immediately follow them. The table has 97 rows, one for the header and 96 (6 times 4 times 4) data rows. Sorry, Villeroy, this data is not in normal form but Calc doesn't mind.

202104021625.ods

(22.97 KiB) Downloaded 105 times

[Tutorial] The SUMPRODUCT function, example X16

I began working on my spreadsheet this morning and it is based on your MATCH formulas, which I see now might be incorrect since MATCH looks for EQUAL OR GREATER not UP TO. But the SUMPRODUCT operands can be changed for that.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.