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

Account #	Product	Location	Day 1 Balance	Day 2 Balance	Change
12345	10	10	1000.00	1500.00	500.00
67890	30	10	50.00	300.00	250.00
44558	20	10	1000.00	900.00	(100.00)
79135	10	20	1000.00	2000.00	1000.00

<tbody>
</tbody>

Criteria

Product name	Product Codes	Location ID
Personal Accounts	10	10
Personal Accounts	20	10

<tbody>
</tbody>

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.