Multiple Criteria Lookup
Let's say you need to return a text value from a list, in which there are multiple criteria used to determine which record provides that data.
There are various options available for the task. Filtering could be the quick and easy way. And, developing a VBA procedure for the job would be another alternative.
Nonetheless, we really just want a formula for this -- and, sometimes that makes the most appropriate solution.
First, let's look at the data in the example I've set up.
There are three all-TEXT fields: Company, Name, and E-Mail. We want to write a formula to return an e-mail address from the table, based on a combination of the Company and Name field values as criteria.
Notice, the table has records for two companies, ABC CORP. and XYZ CORP., and both have someone named Charles Smith.
Step 1 will be to create two named ranges - one encompasing the Company field and the other for the Name field. Dynamic Named Ranges would be ideal, but for the sake of a simple illustration we'll create a couple of conventional named ranges.
From the worksheet's Insert menu choose Names then the Define... command. Enter a name for your new range. We'll use Sheet1!rngCompany and Sheet1!rngName. Next, in the Refers to: box, enter or select the following to be rngCompany :
=Sheet1!$A$1:$A$7
... and, respectively, for rngName in our second field :
=Sheet1!$B$1:$B$7
Step 2 will be for us to set-up another range, for entering the lookup criteria and our formula. We'll just move over a few columns for this.
In cells E2 and F2 I've entered a Company and a person's Name. The combination of both of these will be our lookup criteria.
In cell G2 I've entered the following as an array formula:
=INDEX(C:C,MATCH(E2 & F2,rngCompany&rngName,0))
Now [ IMPORTANT before you hit the enter key] , you will need to use Ctrl + Shift + Enter instead. This creates an ARRAY FORMULA, recognizable by the curly brackets " { } " that automatically appear around it. You never type these curly brackets when entering an array formula.
Here's how this works -
· The key to the formula is concatenation of the criteria variables, also the lookup ranges. To concatenate means to join together. There is a CONCATENATE function for this purpose, but an ampersand ( & ) will work just the same.
Therefore, instead of looking at two distinct fields, the formula will be treating the Company and Name as though it is a single field.
The criteria will be the combination of cells E2 and F2. Concatenated, it looks like "ABC CORP.Charles Smith", stuck together.
· The MATCH function returns the index number within the lookup array of the item matching the given criteria.
We needed to get the MATCH function to treat the Company and Name fields as though they are just one field. We've done so by concatenating the named ranges - rngCompany & rngName.
By the way, the MATCH function in this example gives us the value of 3.
· Finally, the INDEX function with a zero as the 3rd argument, returns the item, from an array, corresponding to the index value argument (that will be the row number of the matching record).
Our formula returns the 3rd item in range C:C, the E-Mail field.
Note: We're counting the header (row 1), since it was also included in the criteria ranges.
Finally, we can improve this formula by accomodating the possibility that there is no match in the lookup table. This formula is really one line, but it's shown as wrapped onto a second line so it will fit on this page.
=IF(ISNA(INDEX(C:C,MATCH(E2 & F2,rngCompany&rngName,0))),"MISSING",
INDEX(C:C,MATCH(E2 & F2,rngCompany&rngName,0)))
Translating this to plain English, it says -
"If a match is NOT found in the table, then display the word 'MISSING' instead of a function error."
Another Approach
It is unlikely, however possible, that a combination of the criteria could be the same when concatenated.
In this example, the multiple criteria could be A & BC = ABC just as AB & C = ABC.
Therefore, another approach would be in order. Referring to our original example, the array formula in range G2 could be:
=INDEX(C:C,MATCH(1,(E2=rngCompany)*(F2=rngName),0))
HOPE THIS HELPS
PLEASE NOTE THE SAMPLE TABLES DO NOT SHOW UP HERE. IF YOU WOULD LIKE THE FULL DOCUMENT PLEASE EMAIL ME AT
JOHN@LITEWEB.COM
