max(if(range_criteria = criteria, value_criteria) ignoring criteria in the search

[LlebKcir]

to test, I simplified the formula to focus on 1 network with 4 sub-networks. the expected output is 4 different MAX values, one for each sub-network.

=MAX(IF(tOrlando[City/Network] = [Underground Network Name], tOrlando[Group]))

This is run within the tNetworkname table, so [Underground Network Name] Excel is removing the tNetworkName from that portion of the code. I find that irritating, but thats what Excel does.

In column [Underground Network Name] and in column tOrlando[City/Network] there are 4 sub-networks: Orlando - North, Orlando - South, Orlando - West, Orlando - East.

Only Orlando - North is reporting a value, but it is the wrong value. It is the Max for all sub-networks in the tOrlando[Group] column thus the Max(If is ignoring the criteria of [Underground Network Name] for the search.

edit to add: Current output is Orlando - North (wrong value), all others have a 0 as their output. All outputs are drastically off from the correct value.

1. Why is it ignoring the criteria value
2. How do I fix this?

 

[LlebKcir]

See image, I am using indirect, still getting zero as a result.

 

[LlebKcir]

how to enter unique formula for each row in the table column? I have basically given up on Excel performing as expected when provided correct data so I will hard code each row with the correct tNetwork name, but when I change row 2, all rows below are changed to that value, same happens on row 3, etc...

How to stop this insanity?

 

[LlebKcir]

The solution I went with, not the best I am sure, was to generate the formulas (=Concat(...)) for all of the networks to generate tNetwork[City/Network] and tNetwork[Group], copy paste data into the table. Then 1 cell at a time, click inside the formula bar, shft+ctrl+return to turn that cell into an array formula. Poof correct values from each network.

Horrid work-around, but hey until someone can help answer why the codes I posted failed, this is best I can offer to others who might have a similar situation down the road.

 

[RoryA]

INDIRECT doesn't appear anywhere in the formulas you have posted.

 

[LlebKcir]

ahh, DUH, i read your first comment on that to be the [@[column_name]], not the command INDIRECT. Ok, feels like a fool :D