How to make part of formula take precedence over another part?

[jordonix]

I have a search tool on excel which essentially searches for the best tool in a database based on sizes inputted in the search fields.
Now I am just optimising this to pick the best tool and I have spotted a flaw with the formula and would like to change how this is calculated - I have tried everything in my knowledge to no avail so hopefully someone may be able to assist?

Basically the formula (cell C7) looks for values in the database which are the closest match (either greater than or equal to) and returns the corresponding tool reference; this works fine but it isn't picking the most optimised tool for the job. What I would like the formula to do is pick the closest match for cell I40 (O/All length) from the values in sheet 'Container Tool', cells AE4:AE400 however, it seems that cell H43 (Bottom OD) takes precedence over this in the formula and then returns a tool that isn't as optimistic as it could be - so my question is how do I change the order in which this is calculated and make 'O/All length' more important than 'Bottom OD' in the formula when finding the best tool?

Looking forward to your answers!

Many thanks in advance!

Jordan

[Kaper]

Your current formula does not prefer match by any column. It just searches top-bottom for first row, where all conditions are met and returns the tool no from this row.

So we have to somehow organize the results (assign weights to rows fulfilling all required conditions) and find the best fit. A proposition could be for instance:

Please Login or Register  to view this content.

Yes, I know it's long :-) but basically its just repeated twice almost the same formula.
we create a reference table with weights (and empty strings if not all conditions are met):

Please Login or Register  to view this content.

and calculate minimum value in this table. Then we take INDEX(ToolNos,MATCH(ourMin, OurTable,0))

I thought it could be in a tricky way (assuming in AE you have only values less than for instance 1000) made a bit shorter.

Please Login or Register  to view this content.

Shows it's not - the same length. Probably also similar computing time. So may be better stay with the first one.

As opposite to original, this formula do not accept presence of errors in Container Tool sheet. There was one caused by a space (not real empty cell) in a cell D294. I deleted it.

See attached file if it works the way you looked for. BTW. In case of 2 or more valuse optimal from the point of view of AE column we will end up again with situation that topmost of the tools meening all conditions and having optimal value of AE column is selected.

[jordonix]

Fantastic! This has solved my problem! Thank you so much for your help!!

[Kaper]

Hi,

Glad to hear that, and thanks for marking thread as Solved.

It would be lovely if you colud also "add me some reputation" by clicking a small star icon in lowerleft corner of my posts. (and confirming in pop-up/dialog window that "you agree").

Best Regards,

Kaper