Autofiltered List; cross-row formula

Question

I have a large Autofiltered list (~600 rows), with some of the rows being summary rows that I want to use a UDF to display the lowest priority listed in any of the 'child' cells. I can pass to my formula the right cells, but they are no longer correct if the list is re-ordered in any way. Is there a way to give the formula the right cell and have it recognise that I want that row and only ever that row?

I can do it with a VLOOKUP to look at a hidden column that lists wether the 'child' row matches the right criteria, but with 600 rows and each parent row requiring about a dozen 'child' cells each, it's too slow.

Answer 1

Assuming you have data that looks like this:

Age     Gender    Priority
52      M         521
53      F         631
78      M         12
81      F         632

then if you wanted to get, say, the lowest priority for males, you could do something like:

={MIN(IF(B2:B5="M", C2:C5, 9999999999999999))}

Note: the {} brackets just indicate that you need to enter the formula as an array formula.

Not 100% sure that answers your question, but hopefully it helps point in a direction that works.