MATCH function after SUBTOTAL (hidden rows count)

[LPAM]

Good morning to everyone,

first time posting, I kindy ask to admins to tell me if this is the wrong section or if I'm missing some steps prior first post, thank you.

I attach the file in exam trying to explain as better as I can my issue, hoping that someone could help me. SCRAP_CALCULATION.xlsx

I'm trying to create an excel that calculate the sheet metal scrap. To do so, given the foil data and the part dimensions, the file should give the foil which minimize the scrap. I'm having issues using the function SUBTOTAL, MATCH and INDEX.

I created a table with all the foil data (referenceID, length and so on). Once I select the material and the thickness through filters, the functions correctly calculates the % scrap in the last column (column X), and with the function SUBTOTAL in the cell O3 it gives me correctley the value which has the lowest % of scrap. So in a defined cell in my table there is a right value which identifies a complete row which has some data I want to display.

The next step is: found this value, simply view the foil reference which the scrap refers to (which is of course in the same row). I created an intermediate step with the MATCH function to identify the ROW which the correct scrap value is, but the file is not working correctley since the row output does not consider all the rows hidden by the filter.

For istance, selecting in the filters thickness 1,5 and material GALV, the table correclty identified that the lowest value in the column X is 28,00% but with the MATCH function in cell P3 it says the the ROW which has this value is the nuber 24, but actually prior filters the ROW should be row 145.

Using the 24th row instead the 145th row in cell J3 with the INDEX function, of course will led to a wrong reference material (reference00014 instead of reference00143)

Strangely, setting the thickness s "1" and the material "IRON" in the filters, the MATCH function in cell O3 will output ROW114, which is right and somehow "counts" also the hidden rows.

Can anyone help me with this issue please?

Best regards,

[JeteMc]

This proposal adds a column (Include) to the table: =SUBTOTAL(104,[@[TOT WEIGHT]])
The formula in P3 is: =AGGREGATE(15,6,ROW($X$1:$X$1629)/($X$1:$X$1629=O3)/(Y1:Y1629>0),1)
Let us know if you have any questions.

[LPAM]

Thank you very much for your help!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.