The need to look up and match multiple criteria is quite common; however as with most things in Excel there are many ways to ‘skin a cat’, I'll share two options with you here.
Matt Duncan from Florida sent in this a cool INDEX and MATCH array formula that allows you to match two criteria from two separate columns and return the corresponding value.
Thanks for sharing, Matt. I don’t recall ever seeing the MATCH function used this way.
Beware, you may need a Brain-booster (a healthy fruit or vegetable snack they get my 6 year-old’s class to eat at about 9.30am to help them concentrate), or a strong coffee, whichever you prefer.
I’ll show you Matt’s impressive formula, and then I’ll show you how I cheat with a non-array approach using VLOOKUP.
Here’s the data Matt is looking up (Note: this is a simplified version for the purpose of this tutorial):
The above table has the following Named Ranges because it’s quicker to build the formula and easier to follow:
Range H4:H16 = GL_Account
Range I04:I16 = Business_Unit
Range J4:J16 = Amount
Here’s the table Matt wants to populate (also simplified):
So, you can see that Matt needs to match both the GL Account and Business Unit which are in separate columns in the Lookup Data table, and then return the corresponding amount from column J in the lookup data.
Here is Matt’s formula from cell B4 (we’ll refer to cell B4 for the remainder of this tutorial):
Note: this is an array formula so you need to enter it with CTRL+SHIFT+ENTER to get the curly brackets at each end that you see below. This is what you see in the formula bar when you enter it correctly:
In English it reads:
Return the Amount from column J that corresponds with GL Account 5597-10 in column H, and Business Unit B3607 in column I, if you don’t find it return a zero.
Let’s take a closer look at the MATCH part of Matt’s formula because that’s where the magic is happening.
How the MATCH Function Part Works
First, remember the MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range.
The twist is that Matt has two specific items he wants to match, the GL Account and Business Unit.
The syntax for the MATCH function is:
=MATCH(lookup_value, lookup_array, [match_type])
Lookup_value – the value you want to find
Lookup_array – the column/row containing the value you want to find
Match_type – optional argument represented by a -1, 0 or 1.
- -1 finds the smallest value that is greater than or equal to lookup_value,
- 0 finds the first value that is exactly equal to lookup_value, or
- 1 finds the largest value that is less than or equal to lookup_value.
Here’s the MATCH component of Matt’s formula:
Note how he has two lookup_arrays:
1. GL Account column ($A4=GL_Account)
2. Business Unit (B$3=Business_Unit)
And how, instead of using the lookup_value as his criteria to match, he has entered the criteria in with the lookup_array.
I have never seen that before!
So, How Does It All Work?
Array formulas are testing for TRUE or FALSE outcomes and award them a numerical equivalent of 1 for TRUE, and 0 for FALSE.
The MATCH part of the formula evaluates each cell in the GL_account column and if it matches the GL Account code in A4 (5597-10) is awards it a 1, and if it doesn’t it awards it a zero.
It does the same for the Business_Unit column, matching B3607 to give you an array like this:
Or in a formula it looks like this:
Which is the same as doing this calculation:
Since the only row resulting in a 1 is on row 9 of the array, the MATCH component of the formula evaluates to 9, which is used by the INDEX function as the row number argument.
So, let's take a look at the INDEX function part of the formula now.
Remember the INDEX function in the array form returns the value of an element in a table or an array, selected by the row and column number indexes.
And the syntax for the INDEX function in the array form is:
=INDEX(array, row_num, [column_num])
Array – the range of cells containing the data you want to find.
Row_num – the row number your data is on.
Column_num –the column number your data is in.
Matt’s formula evaluates to this:
The column_num argument is optional, and since there is only one column in the Amount Named Range the argument is not required.
For icing on the top Matt then wraps his formula in an IFERROR function so if a match is not found a zero will be entered in the cell instead of an error.
Here is Matt's formula one last time
The VLOOKUP Cheat
Now, if the carrot I gave you didn't help and all that array business still did your head in, I’ve got a simpler option that requires a helper column.
Some would say the helper column is cheating 😉 but I say go with the path of least resistance, unless of course it is worthwhile in the long term, which it is for Matt.
So, an alternative solution is to create a unique value in your lookup data from the two columns you’re trying to match i.e. the GL Account and Business Unit, by joining them together. This is where the helper column comes in.
In the table below I have a new column (G) that CONCATENATES (joins) the GL Account and Business Unit values together using a formula in cell G4 like this:
The result is what you see below in column G.
Now you have your unique GL/Business Unit identifier you can use a VLOOKUP formula like this:
Which evaluates like this:
To eliminate potential errors wrap it in an IFERROR function like this:
Note: whilst array formulas are effective they can also be taxing on your system memory. If you have a lot of data to search through the VLOOKUP option may be more efficient.
Thanks for sharing your cool trick and teaching me something new, Matt.
Matt Duncan is an accountant for a global payroll processing company located in Florida. He has no formal training in Excel but loves to find new ways to use Excel’s vast functionality.
“As you can imagine, a payroll company has limited time to complete all the processes that go into producing a single pay check not to mention a batch of 30,000 checks!
There are numerous ways to make a mistake on a pay check and with only a few hours to produce a check and fund the hundreds of benefits, vendors, governments and garnishments, etc., there is a constant need for process improvement.
One of the most time consuming processes has been funding verification. With global corporations clients sharing a payroll cycle between all its companies the verification process included manual inputs based on a variety of filtering and VLOOKUP’s. The process could take up to 2 hours depending on the number of accounts and the number of companies included in a pay cycle just to input data!
My role is to audit all the pay cycles at the end of the month and report back to the client with any errors and, of course, improve processes that can cause errors, i.e. manual inputs!”
Vote for Matt
If you’d like to vote for Matt's tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment….or all of the above 🙂