I have a table with three columns: City, State, Rate. Each city could be included multiple times with different seasonal rates. To look up the maximum rate, I need to use both the state and the city as criteria (because a city name may not be unique - like Portland, OR and Portland, ME). If the cities were unique I would use an array formula (Excel 2016) to get the max rate for that city (criteria city and state in A1 and A2):
{=MAX(IF(Table1[Column1]=A1, Table1[Column3]))}
I want to add the state (criteria in A2, and in Table1 Column2) as an additional criteria.
{=MAX(IF(Table1[Column1]=A1, Table1[Column3]))}
I want to add the state (criteria in A2, and in Table1 Column2) as an additional criteria.
