Index max formula with multiple criteria

[kgardner]

I am comparing 2 sets of account balances and want to find the account with the largest change in balance from day to day for a specific product category (Personal Accounts) and location (10). The accounts are separated into groups by a product code and location ID. Below is a data sample.

Sample Balance Data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account #[/TD]
[TD]Product[/TD]
[TD]Location[/TD]
[TD]Day 1 Balance[/TD]
[TD]Day 2 Balance[/TD]
[TD]Change[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]1000.00[/TD]
[TD]1500.00[/TD]
[TD]500.00[/TD]
[/TR]
[TR]
[TD]67890[/TD]
[TD]30[/TD]
[TD]10[/TD]
[TD]50.00[/TD]
[TD]300.00[/TD]
[TD]250.00[/TD]
[/TR]
[TR]
[TD]44558[/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD]1000.00[/TD]
[TD]900.00[/TD]
[TD](100.00)[/TD]
[/TR]
[TR]
[TD]79135[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]1000.00[/TD]
[TD]2000.00[/TD]
[TD]1000.00[/TD]
[/TR]
</tbody>[/TABLE]

Criteria
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product name[/TD]
[TD]Product Codes[/TD]
[TD]Location ID[/TD]
[/TR]
[TR]
[TD]Personal Accounts[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Personal Accounts[/TD]
[TD]20[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

The answer should return 500.00 for location 10 and 1000.00 for location 20. I have tried the formula:

{=index(account #,max(if((location = location id)*(product = product codes), row(change)))}

and some other combinations of this formula, but it fails to recognize the list of possible product codes in the criteria table. If I set the formula for just one product code such as ...product = "10".. then it returns a value, but not if I try to use the "product codes" list.

Any ideas on what I can do? How can I make sure to include negative changes as well?

 

[kgardner]

I've been pondering how I phrased my question and this may be simpler to understand.

How do I make the following formula work?

{=MAX(IF(C:C = G1, IF(B:B = H1:H5, F:F)))}

Currently it only works as:

{=MAX(IF(C:C = G1, IF(B:B = H1, F:F)))}

Where:
B:B = product codes
C:C = location codes
F:F = change in account value
G1 = location ID I'm searching for
H1:H5 = product codes associated with the product group I am searching for

I want to show the largest change in the product group (i.e. Fruit or Meat) based on the associated product codes (i.e. apples, oranges, strawberries or Pork, Chicken, Beef).

 

[Marcelo Branco]

Could you post a small data sample and expected result?

M.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=MAX(IF($C$2:$C$400=G1,IF(ISNUMBER(MATCH($B$2:$B$400,H1:H5,0)),$F$2:$F$400)))

 

[kgardner]

Just as I was about to post sample data, a solution appears! Thank you Aladin! How does the ISNUMBER() function work in this formula?

 

[Aladin Akyurek]

Just as I was about to post sample data, a solution appears! Thank you Aladin! How does the ISNUMBER() function work in this formula?

You are welcome.

MATCH is capable of matching a range against (a) itself or (b) against another range. In the 2nd case you might expect #N/A's to occur among some integers indicating successful matches. Testing these results whether they are numbers using ISNUMBER delivers TRUE/FALSE evaluations. The surrounding IF maps the TRUE values into the F-values which are then evaluated by MAX for an ultimate result.