Confused on what to use ????? vlookup with different criteria
 

ok Please can you help me here goes

I have a table showing this data
1 2 3 4 5 6 7
aa gyn c att 23 22 21
aa gyn s att 11 26 31
aa gyn c h 14 00 36
aa vas c att 11 15 28
cb obs c att 10 08 43
cb obs s h 11 12 14


this is an example of the data which goes on for about 500 rows I want to
put a formula into a report which will pull out on certain criteria for
example I would like my report to give me the number in colomn 5 for the
person initials in colomn 1 showing he is a gyn showing he is a c and att

my report does not work because i have more than one set of criteria for
"aa" in the first colomn. i used a vlookup but I am unsure how to do this
with all the filtering needed


please help me
thankyou so much
kate

Confused on what to use ????? vlookup with different criteria
 

try sumproduct it allows multiple criteria. the values i have included
in " " can be replace with a cell containing that value if it is
easier

=sumproduct((b1:b500="gyn")*(c1:c500="c")*(d1:d500 ="att")*(e1:e500))

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=561436

Confused on what to use ????? vlookup with different criteria
 

Kate,

Do you mean something like

=INDEX(E1:E20,MATCH(1,(A1:A20="aa")+(A1:A20="cb"), 0))

or maybe even

=INDEX(E1:E20,MATCH(1,(A1:A20="aa")*(B1:B20="gyn") *(C1:C20="c")*(D1:D20="att
"),0))

which is an array formula (both are), it should be committed with
Ctrl-Shift-Enter, not just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"kate" wrote in message
...
ok Please can you help me here goes

I have a table showing this data
1 2 3 4 5 6 7
aa gyn c att 23 22 21
aa gyn s att 11 26 31
aa gyn c h 14 00 36
aa vas c att 11 15 28
cb obs c att 10 08 43
cb obs s h 11 12 14


this is an example of the data which goes on for about 500 rows I want to
put a formula into a report which will pull out on certain criteria for
example I would like my report to give me the number in colomn 5 for the
person initials in colomn 1 showing he is a gyn showing he is a c and att

my report does not work because i have more than one set of criteria for
"aa" in the first colomn. i used a vlookup but I am unsure how to do this
with all the filtering needed


please help me
thankyou so much
kate

Confused on what to use ????? vlookup with different criteria
 

Thankyou for your help

"Bob Phillips" wrote:

Kate,

Do you mean something like

=INDEX(E1:E20,MATCH(1,(A1:A20="aa")+(A1:A20="cb"), 0))

or maybe even

=INDEX(E1:E20,MATCH(1,(A1:A20="aa")*(B1:B20="gyn") *(C1:C20="c")*(D1:D20="att
"),0))

which is an array formula (both are), it should be committed with
Ctrl-Shift-Enter, not just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"kate" wrote in message
...
ok Please can you help me here goes

I have a table showing this data
1 2 3 4 5 6 7
aa gyn c att 23 22 21
aa gyn s att 11 26 31
aa gyn c h 14 00 36
aa vas c att 11 15 28
cb obs c att 10 08 43
cb obs s h 11 12 14


this is an example of the data which goes on for about 500 rows I want to
put a formula into a report which will pull out on certain criteria for
example I would like my report to give me the number in colomn 5 for the
person initials in colomn 1 showing he is a gyn showing he is a c and att

my report does not work because i have more than one set of criteria for
"aa" in the first colomn. i used a vlookup but I am unsure how to do this
with all the filtering needed


please help me
thankyou so much
kate

Confused on what to use ????? vlookup with different criteria
 

Thankyou for your help

"Dav" wrote:


try sumproduct it allows multiple criteria. the values i have included
in " " can be replace with a cell containing that value if it is
easier

=sumproduct((b1:b500="gyn")*(c1:c500="c")*(d1:d500 ="att")*(e1:e500))

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=561436

Confused on what to use ????? vlookup with different criteria
 

Hello again I am sorry its still not working i am being really thick becasue
I want this to give me a number in colomn 7 and I am unsure how it will do
this. When i put the index in it gives me a number from the several it could
choose from but as soon as I add in match in comes up with N/A#

My index alone is=INDEX('Outpatients New'!A4:H293,4,7)

With MATCH
=INDEX('Outpatients New'!a4:F293,MATCH(A1,'Outpatients
New'!C4:C293="ATT")*MATCH(A1,'Outpatients New'!D4:D293="C"),0)

Please could one of you help me again,
Thankyou

"Bob Phillips" wrote:

Kate,

Do you mean something like

=INDEX(E1:E20,MATCH(1,(A1:A20="aa")+(A1:A20="cb"), 0))

or maybe even

=INDEX(E1:E20,MATCH(1,(A1:A20="aa")*(B1:B20="gyn") *(C1:C20="c")*(D1:D20="att
"),0))

which is an array formula (both are), it should be committed with
Ctrl-Shift-Enter, not just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"kate" wrote in message
...
ok Please can you help me here goes

I have a table showing this data
1 2 3 4 5 6 7
aa gyn c att 23 22 21
aa gyn s att 11 26 31
aa gyn c h 14 00 36
aa vas c att 11 15 28
cb obs c att 10 08 43
cb obs s h 11 12 14


this is an example of the data which goes on for about 500 rows I want to
put a formula into a report which will pull out on certain criteria for
example I would like my report to give me the number in colomn 5 for the
person initials in colomn 1 showing he is a gyn showing he is a c and att

my report does not work because i have more than one set of criteria for
"aa" in the first colomn. i used a vlookup but I am unsure how to do this
with all the filtering needed


please help me
thankyou so much
kate