match/index using multiple values

[perky2go]

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&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*E:E*F:F,0))

Any suggestions?

 

[Bob Phillips]

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,D11000&E1:E1000&F1:F1000,0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[perky2go]

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.)

 

[Ashish Mathur]

Hi,

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

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

Regards,

Ashish

 

[Bob Phillips]

It's what we are here for <vbg>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[perky2go]

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!