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

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

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

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