Optimization and Filtering

[dsdevon06]

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.

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some sample data along with expected output.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[dsdevon06]

Why am I unable to just post in the actual spreadsheet?

 

[Fluff]

We do not allow people to upload workbooks to the site, but you can upload to a share site such as OneDrive, GoogleDrive, mark for sharing & post the link you are given to here.
However a lot of people will not, or cannot, download files for security reasons.

 

[dsdevon06]

Hi & welcome to MrExcel.

Can you post some sample data along with expected output.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

https://emerson-my.sharepoint.com/❌...FFg9olGSs7FN0BYNk3PM2YGUmClQPb2tkAGA?e=kMq5fb

I create this DEMO version

 

[dsdevon06]

https://emerson-my.sharepoint.com/❌...FFg9olGSs7FN0BYNk3PM2YGUmClQPb2tkAGA?e=kMq5fb

I create this DEMO version

https://emerson-my.sharepoint.com/❌...FFg9olGSs7FN0BZe_8Ow3jJJHFXA1uDO84_w?e=qC4RXe

 

[dsdevon06]

https: //emerson-my.sharepoint.com//p/devon_schultz/EbmpiufFRJFFg9olGSs7FN0BZe_8Ow3jJJHFXA1uDO84_w?e=Qeu0ft

Added an extra space between https: and //

 

[dsdevon06]

after the sharepoint.com/ it goes or colon x colon if it keeps making these ridiculous red x's

 

[Fluff]

Those links are asking me to sign in. You need to mark for sharing & post that link

 

[dsdevon06]

Those links are asking me to sign in. You need to mark for sharing & post that link

https://emerson-my.sharepoint.com/❌...FFg9olGSs7FN0BZe_8Ow3jJJHFXA1uDO84_w?e=TGKuYI

the red X is the following: colonxcolon