Find the closest option by comparing 3 variables

[Said Marin]

Given the following data:

[TABLE="width: 562"]
<colgroup><col span="6"><col></colgroup><tbody>[TR]
[TD]Material[/TD]
[TD]Class[/TD]
[TD]Y_ID[/TD]
[TD]Y_OD[/TD]
[TD]Y_L[/TD]
[TD]MatlCode[/TD]
[TD]STD[/TD]
[/TR]
[TR]
[TD]109.02253[/TD]
[TD]PR09[/TD]
[TD]152.4[/TD]
[TD]172.9[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.02635[/TD]
[TD]PR09[/TD]
[TD]228.6[/TD]
[TD]244.3[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.02754[/TD]
[TD]PR09[/TD]
[TD]238.13[/TD]
[TD]259.31[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.03071[/TD]
[TD]PR09[/TD]
[TD]95.1738[/TD]
[TD]109.982[/TD]
[TD]6.2992[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.03287[/TD]
[TD]PR09[/TD]
[TD]254[/TD]
[TD]274.5[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.03295[/TD]
[TD]PR09[/TD]
[TD]304.8[/TD]
[TD]326.06[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.03342[/TD]
[TD]PR09[/TD]
[TD]280[/TD]
[TD]304[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.03466[/TD]
[TD]PR09[/TD]
[TD]250[/TD]
[TD]270.5[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.03889[/TD]
[TD]PR09[/TD]
[TD]419.1[/TD]
[TD]440.3344[/TD]
[TD]8.1026[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.04023[/TD]
[TD]PR09[/TD]
[TD]88.9[/TD]
[TD]104[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.05289[/TD]
[TD]PR09[/TD]
[TD]20[/TD]
[TD]30.7[/TD]
[TD]4.2[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.05445[/TD]
[TD]PR09[/TD]
[TD]36.53[/TD]
[TD]43.79[/TD]
[TD]3.2[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.05958[/TD]
[TD]PR09[/TD]
[TD]114.3[/TD]
[TD]129.39[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.06389[/TD]
[TD]PR09[/TD]
[TD]501.65[/TD]
[TD]525.66[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.06653[/TD]
[TD]PR09[/TD]
[TD]523.88[/TD]
[TD]545.13[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.07057[/TD]
[TD]PR09[/TD]
[TD]350[/TD]
[TD]374[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.07317[/TD]
[TD]PR09[/TD]
[TD]190[/TD]
[TD]210.5[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.07518[/TD]
[TD]PR09[/TD]
[TD]22.23[/TD]
[TD]29.51[/TD]
[TD]3.2[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.08527[/TD]
[TD]PR09[/TD]
[TD]215.9[/TD]
[TD]236.4[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.08631[/TD]
[TD]PR09[/TD]
[TD]558.8[/TD]
[TD]580.06[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.08646[/TD]
[TD]PR09[/TD]
[TD]228.6[/TD]
[TD]249.1[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.08776[/TD]
[TD]PR09[/TD]
[TD]406.4[/TD]
[TD]430.4[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.09047[/TD]
[TD]PR09[/TD]
[TD]57.15[/TD]
[TD]67.89[/TD]
[TD]4.29[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.09280[/TD]
[TD]PR09[/TD]
[TD]17.45[/TD]
[TD]24.74[/TD]
[TD]3.2[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.09283[/TD]
[TD]PR09[/TD]
[TD]20[/TD]
[TD]26.75[/TD]
[TD]3.2[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09289[/TD]
[TD]PR09[/TD]
[TD]22[/TD]
[TD]30[/TD]
[TD]3.2[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09293[/TD]
[TD]PR09[/TD]
[TD]28[/TD]
[TD]38.5[/TD]
[TD]4.2[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09305[/TD]
[TD]PR09[/TD]
[TD]34.93[/TD]
[TD]42.24[/TD]
[TD]3.2[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09313[/TD]
[TD]PR09[/TD]
[TD]40[/TD]
[TD]55.5[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09314[/TD]
[TD]PR09[/TD]
[TD]44[/TD]
[TD]51.5[/TD]
[TD]3.2[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09316[/TD]
[TD]PR09[/TD]
[TD]44.958[/TD]
[TD]55.7276[/TD]
[TD]4.2672[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.09317[/TD]
[TD]PR09[/TD]
[TD]45[/TD]
[TD]55.7[/TD]
[TD]4.2[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09322[/TD]
[TD]PR09[/TD]
[TD]50[/TD]
[TD]65.5[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09330[/TD]
[TD]PR09[/TD]
[TD]60[/TD]
[TD]75.5[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09332[/TD]
[TD]PR09[/TD]
[TD]65[/TD]
[TD]80.5[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09334[/TD]
[TD]PR09[/TD]
[TD]70[/TD]
[TD]85[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09336[/TD]
[TD]PR09[/TD]
[TD]75[/TD]
[TD]85.7[/TD]
[TD]4.2[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.09339[/TD]
[TD]PR09[/TD]
[TD]82.55[/TD]
[TD]97.64[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.09343[/TD]
[TD]PR09[/TD]
[TD]88.9[/TD]
[TD]104.5464[/TD]
[TD]6.2738[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09349[/TD]
[TD]PR09[/TD]
[TD]101.6[/TD]
[TD]117.25[/TD]
[TD]6.27[/TD]
[TD]T23[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

The materials with the letter S in the last column of the data are considered standard sizes.

What i need to do is compare Y_ID, Y_OD and Y_L of every material and find the closest standard part number and give it in an additional column.

I've been struggling with nested if's and index match and can't figure out how to do it. I'll appreciate some help or guidance.

Thanks.

 

[Said Marin]

Can you show me what you did on columns N, O, P, Q and R please?

 

[sheetspread]

I just separated the standards for easier comparison.

 

[Said Marin]

Sorry to bother you again. Can you explain me step by step what you did? I tried to export this procedure for a longer list and it's not working. I also tried to replicate your work here just to understand the logic and couldn't figure out what you did.

 

[sheetspread]

I took your original table, divided it in two (standards and everything else) using the filter, but you can also sort then cut/paste. Next, add the formulas that choose 1 row of standard dimensions per row of nonstandards and calculates the difference. Third, use solver with the stated parameters to find the combination of standards that gives you the smallest difference, i.e. the closest dimensions per row.

 

[Said Marin]

I took your original table, divided it in two (standards and everything else) using the filter, but you can also sort then cut/paste. Next, add the formulas that choose 1 row of standard dimensions per row of nonstandards and calculates the difference. Third, use solver with the stated parameters to find the combination of standards that gives you the smallest difference, i.e. the closest dimensions per row.

Do you have any suggestions for a much bigger data set. I know solver has a limit of 200 variables and i have more than 500 different part numbers (variables) to match.

 

[sheetspread]

I'm not sure the limits apply here if you just expand the range, but if they do, then try running just ~200 rows at a time. Or maybe look at premium solver which is much more flexible and might be available as a free trial download.