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

[kate]

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

[Dav]

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

[Bob Phillips]

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

[kate]

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

[kate]

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

[kate]

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