Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DCOUNTA Complex Criteria Question | Excel Worksheet Functions | |||
vlookup with two criteria | Excel Worksheet Functions | |||
vlookup with two criteria | Excel Worksheet Functions | |||
Sumproduct with a vLookup, multiple criteria | Excel Worksheet Functions | |||
VlookUp with Multiple Criteria? | Excel Worksheet Functions |