match/index using multiple values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I found a few earlier posts, but I get error msgs when I try any of the
formulas suggested. I'm trying to use the values in 2 or 3 cells to match to
the values in 2 or 3 columns to return a single value from another column.
In my ex. below, I want to return count from the last column that matches the
program value in A1 ("ProgramA", the date value in A2 and the value of
"Denied"--so B2 would be 12. The number of rows in my lookup (D:G will vary).

A B C D E F G
ProgramA Total ProgramA Approved 4/1/04 25
6/1/04 ProgramB Approved 6/1/04 326
ProgramA Denied 6/1/04 12
ProgramB Denied 8/1/04 1

I have tried the following as array formulas and get either a #NUM! ("There
is a problem with a number in your formula"):
=index(G:G,match(A1&"Denied"&A2,D:D&E:E&F:F,0))

or #VALUE! error ("A value used in your formula is of the wrong data type"):
=index(G:G,match(A1*"Denied"*A2,D:D*E:E*F:F,0))

Any suggestions?
 
When you are using array formulae, you cannot use complete columns, you must
use defined ranges, so try

=index(G1:G1000,match(A1&"Denied"&A2,D1:D1000&E1:E1000&F1:F1000,0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Thank you so much! That did the trick! (And thanks for the earlier post on
the same subject you had in November that got me started. I haven't used
arrays much but I can see where I'm going to have to change that tactic.)
 
Hi,

You may also try the following array formula (Ctrl+Shift+Enter):

=sum(if((D1:D4=A1)*(E1:E4="Denied")*(F1:F4=A2),G1:G4))

Regards,

Ashish
 
It's what we are here for <vbg>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Thanks--I haven't plugged this version in, but it looks like a good
alternative where I have a lot of "missing" month values from my data set
that result in error messages that have to be cleaned up in the formula. The
resulting cell formula will be a lot easier to follow for the next person
looking at my template who is trying to figure out what I did!
 
Back
Top