Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple criteria for lookup table
I am currently using VLOOKUP to grab values. I now realize that there are
multiple criteria involved before a decision can be made. My data looks like this: New Role Name BU Region Recommended Role Owners 50003281 Service North America Marnie Perez/Wendy Synol/Jackie Howson 50003281 Service EMEA Francis Hopwood/Jackie Howson 50003281 Service Asia Pacific Andy McDougall/Wendy Synol/Jackie Howson 50003281 All North America Cathy Parker 50003281 All EMEA Guido Moresi/Stephen Lovass/Axel Terlinden 50003281 All Asia Pacific Guido Moresi/Stephen Lovass/Axel Terlinden My lookup is using Role Name but I also need to consider BU and Region before I can grab Recommended Role Owners. Any solutions I hope? This is my current formla: =IF(ISNA(VLOOKUP(B14,'All Roles'!$A$1:$F$3485,6,FALSE)),"",VLOOKUP(B14,'All Roles'!$A$1:$F$3485,6,FALSE)) Thank you in advance for your assistance. Mark |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple criteria for lookup table
=INDEX('All Roles'!$F$1:$F$3485,MATCH(1,('All Roles'!$A$1:$A$3485=B14)*('All
Roles'!$B$1:$B$3485=C14)*('All Roles'!$C$1:$C$3485=D14),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Mark" wrote in message . net... I am currently using VLOOKUP to grab values. I now realize that there are multiple criteria involved before a decision can be made. My data looks like this: New Role Name BU Region Recommended Role Owners 50003281 Service North America Marnie Perez/Wendy Synol/Jackie Howson 50003281 Service EMEA Francis Hopwood/Jackie Howson 50003281 Service Asia Pacific Andy McDougall/Wendy Synol/Jackie Howson 50003281 All North America Cathy Parker 50003281 All EMEA Guido Moresi/Stephen Lovass/Axel Terlinden 50003281 All Asia Pacific Guido Moresi/Stephen Lovass/Axel Terlinden My lookup is using Role Name but I also need to consider BU and Region before I can grab Recommended Role Owners. Any solutions I hope? This is my current formla: =IF(ISNA(VLOOKUP(B14,'All Roles'!$A$1:$F$3485,6,FALSE)),"",VLOOKUP(B14,'All Roles'!$A$1:$F$3485,6,FALSE)) Thank you in advance for your assistance. Mark |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple criteria for lookup table
I assume B14 contains the Role Name - would C14 contain BU and D14 the
Region? If so, you need to amend your table in the All Roles sheet to insert a new column F and to enter a formula in F1 which concatenates the Role Name with BU and Region. Something like: =B1&C1&D1 and copied down to the bottom of the table. Then your formula becomes: =IF(ISNA(VLOOKUP(B14&C14&D14,'All Roles'!$F$1:$G$3485,2,FALSE))," ",VLOOKUP(B14&C14&D14,'All Roles'!$F$1:$G$3485,2,FALSE)) I've split the formula in the middle of the "" to avoid awkward line-wrap. Hope this helps. Pete Mark wrote: I am currently using VLOOKUP to grab values. I now realize that there are multiple criteria involved before a decision can be made. My data looks like this: New Role Name BU Region Recommended Role Owners 50003281 Service North America Marnie Perez/Wendy Synol/Jackie Howson 50003281 Service EMEA Francis Hopwood/Jackie Howson 50003281 Service Asia Pacific Andy McDougall/Wendy Synol/Jackie Howson 50003281 All North America Cathy Parker 50003281 All EMEA Guido Moresi/Stephen Lovass/Axel Terlinden 50003281 All Asia Pacific Guido Moresi/Stephen Lovass/Axel Terlinden My lookup is using Role Name but I also need to consider BU and Region before I can grab Recommended Role Owners. Any solutions I hope? This is my current formla: =IF(ISNA(VLOOKUP(B14,'All Roles'!$A$1:$F$3485,6,FALSE)),"",VLOOKUP(B14,'All Roles'!$A$1:$F$3485,6,FALSE)) Thank you in advance for your assistance. Mark |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple criteria for lookup table
Pete, I understand the concatenation will make for unique values. I get what
you are trying to do. I guess on my form I need the user to input the value for BU and Region in order to get the correct Approver Name. So on my form someone would put the value lets say of Service for BU and EMEA for region and these two in combination with the New Role value they key in will give them the Approver Value. So my input fields would be: New Role + BU+Region = Approver Name (I keep forgetting this is a text format and when I cut and paste it loses the spacing.) Thanks again. Mark "Pete_UK" wrote in message oups.com... I assume B14 contains the Role Name - would C14 contain BU and D14 the Region? If so, you need to amend your table in the All Roles sheet to insert a new column F and to enter a formula in F1 which concatenates the Role Name with BU and Region. Something like: =B1&C1&D1 and copied down to the bottom of the table. Then your formula becomes: =IF(ISNA(VLOOKUP(B14&C14&D14,'All Roles'!$F$1:$G$3485,2,FALSE))," ",VLOOKUP(B14&C14&D14,'All Roles'!$F$1:$G$3485,2,FALSE)) I've split the formula in the middle of the "" to avoid awkward line-wrap. Hope this helps. Pete Mark wrote: I am currently using VLOOKUP to grab values. I now realize that there are multiple criteria involved before a decision can be made. My data looks like this: New Role Name BU Region Recommended Role Owners 50003281 Service North America Marnie Perez/Wendy Synol/Jackie Howson 50003281 Service EMEA Francis Hopwood/Jackie Howson 50003281 Service Asia Pacific Andy McDougall/Wendy Synol/Jackie Howson 50003281 All North America Cathy Parker 50003281 All EMEA Guido Moresi/Stephen Lovass/Axel Terlinden 50003281 All Asia Pacific Guido Moresi/Stephen Lovass/Axel Terlinden My lookup is using Role Name but I also need to consider BU and Region before I can grab Recommended Role Owners. Any solutions I hope? This is my current formla: =IF(ISNA(VLOOKUP(B14,'All Roles'!$A$1:$F$3485,6,FALSE)),"",VLOOKUP(B14,'All Roles'!$A$1:$F$3485,6,FALSE)) Thank you in advance for your assistance. Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum column information based on multiple criteria | Excel Worksheet Functions | |||
Counting total for multiple criteria | Excel Worksheet Functions | |||
how do I create a pivot table based on multiple sheets | Excel Discussion (Misc queries) | |||
counting using multiple criteria | Excel Discussion (Misc queries) | |||
Select by multiple criteria (Excel 2003) | Excel Worksheet Functions |