Add Two More Criteria to Index Match Min Formula Used For Finding Nearest Points

[Glastenbury]

=INDEX('[Example1.xlsx]Utility Connectors'!C$4:C$845,MATCH(MIN(SQRT(('[Example1.xlsx]Utility Connectors'!D$4:D$845-D3)^2+('[Example1.xlsx]Utility Connectors'!E$4:E$845-E3)^2)),SQRT(('[Example1.xlsx]Utility Connectors'!D$4:D$845-D3)^2+('[Example1.xlsx]Utility Connectors'!E$4:E$845-E3)^2),0),1)

This finds and enters the name of the building utility valve that is closest to the machine that needs this service connection.

I need to add two more criteria to the above formula (found in column F of the Machine Connections tab) so that the answer is a valve of the correct utility type instead of the nearest valve without regard to utility type, as it is now. The second need is to distinguish one floor level from the other since the machines and services exist on two floors.

I've tried many things but can't figure it out. Need to add something like MATCH(C7,A$4:A$22) or ,(A$4:A$22=C7) or +(A$4:A$22=C7) . The type of statement and nomenclature that is compatible with the existing MIN statement is what has me stumped.

Thank you.

[XLent]

one option might be to add a large number wherever either Floor or Utility do not match thereby ensuring they will not be the MIN, e.g.:

Please Login or Register  to view this content.

with this type of approach, if possible you may not always get a result, you should add a pre-emptive COUNTIFS test.

[Glastenbury]

Thank you so much! That solves 99% of my issues!
I'll have to digest and test the COUNTIFS suggestion.
Would you mind giving a brief description of what (10^10* is doing? Okay if you'd rather not.
Thanks.

[XLent]

the logic is to add 10bn to the SQRT result wherever, either, a) the Level (A <>A3) or b) Utility (B <> C3) do not match the criteria (if neither match you will add 20bn) -- so, the invalid rows will return meaningless results in context of outer MIN calculation. However, if it's possible to not have any valid results you would still get rogue results with this approach as the MIN would be taken from one of the >10bn results, hence the pre-emptive COUNTIFS test.

You could also adopt a slightly different approach using something like the below:

Please Login or Register  to view this content.

in this instance, both the FILTER and AGGREGATE will only reference valid rows, returning #NUM! where no valid result exists (handled via IFERROR)
the outer INDEX is just to handle possibility of FILTER returning more than 1 record (should 2 valid rows return identical SQRT)