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?

match/index using multiple values
 

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:E 1000&F1:F1000,0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"perky2go" wrote in message
...
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?

match/index using multiple values
 

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:E 1000&F1:F1000,0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"perky2go" wrote in message
...
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?

match/index using multiple values
 

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?

match/index using multiple values
 

It's what we are here for <vbg

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"perky2go" wrote in message
...
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:E 1000&F1:F1000,0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"perky2go" wrote in message
...
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?

match/index using multiple values
 

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?