I have two different data sets with similar data but different model numbers. I want to find a discus model that is closest to the reed model based on a certain criteria. That is: Uses the same refrigerant, has the same application, and then the closest capacity. I want this to be robust enough that if the criteria can change. The person who set up this excel previously did not do this. Then once this model is found, it needs outputted in the table to the right with specific data correlating to that discus model.
This was the formula used previously that works but is flawed in its filtering and picking.
=IFERROR(INDEX(A$21:A$25,MATCH(MIN(IF(($E$21:$E$25=$E9)*($C$21:$C$25=$C9)*($I$21:$I$25=$I9)*($J$1=$J$21:$J$25),ABS($G9-$G$21:$G$25))),IF(($E$21:$E$25=$E9)*($J$1=$J$21:$J$25)*($C$21:$C$25=$C9)*($I$21:$I$25=$I9),ABS($G9-$G$21:$G$25)),0)),"-")
Columns M through U and V have similar equations that look through the data set. I tried a pivot table but it gets screwed up with not having exact answers for example, Capacity.
This was the formula used previously that works but is flawed in its filtering and picking.
=IFERROR(INDEX(A$21:A$25,MATCH(MIN(IF(($E$21:$E$25=$E9)*($C$21:$C$25=$C9)*($I$21:$I$25=$I9)*($J$1=$J$21:$J$25),ABS($G9-$G$21:$G$25))),IF(($E$21:$E$25=$E9)*($J$1=$J$21:$J$25)*($C$21:$C$25=$C9)*($I$21:$I$25=$I9),ABS($G9-$G$21:$G$25)),0)),"-")
Columns M through U and V have similar equations that look through the data set. I tried a pivot table but it gets screwed up with not having exact answers for example, Capacity.