match/index using multiple values

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)

"perky2go" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>

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

"Bob Phillips" wrote:

> 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)
>
> "perky2go" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
> >
>
>
>

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

"perky2go" wrote:

> 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?
>
>

It's what we are here for <vbg>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"perky2go" <[email protected]> wrote in message
news:[email protected]...
> 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.)
>
> "Bob Phillips" wrote:
>
> > 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)
> >
> > "perky2go" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> > >
> > >
> >
> >
> >

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!

"Ashish Mathur" wrote:

> 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
>
> "perky2go" wrote:
>
> > 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?
> >
> >