Monday, March 06, 2006

Constructing multi conditional array formulas in Excel

That heading might sound like this is way over your head, but I bet you've probably needed to write formulas like this before and never knew how to do it.

Let me illustrate with an example.
Today I needed to use an array formula to find the largest value in one column, after conditionally filtering by two other fields.

OK now in English!

Say you have a table of payroll data and you need to find who got paid the most from one particular state where the monthly salary paid was over a certain threshold.

Column A = State
Column B = Monthly Salary
Column C = Weekly Pay
Column D = Name

The formula I wrote was {=VLOOKUP(MAX((A3:A100="WA")*(B3:B100>2500)*C3:C100),C3:D100,2,FALSE)}
This formula is committed (and edited if necessary) with CTRL SHIFT ENTER and that is how you get the curly brackets.
This returned the name of the employee who was paid the most from WA state from the list of employees in that state who earned over $2500 per month.

However while this formula works fine you will generally find that with this sort of data the employee name is in the first column. Regardless, often the results you need are not able to be found using VLOOKUP.
Here is the way to find the same result where the columns are as follows:
Column A = Name
Column B = State
Column C = Monthly Salary
Column D = Weekly Pay

{=INDIRECT(ADDRESS(MATCH(MAX((B3:B100="WA")*(C3:C100>2500)*D3:D100),D3:D100,0)+2,1),TRUE)}
Once again the formula needs to be committed with a CTRL SHIFT ENTER.


Posted by: Jethro | 1:06 PM |