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))
Know an even better way?
Let me know.
|
![Microsoft Office : Excel - Visual Basic for Applications [ VBA ] Software](img/1ptrans.gif){kind=small}
