Multiple criteria for lookup table

M

Mark

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
 
B

Bob Phillips

=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)
 
P

Pete_UK

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
 
M

Mark

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
 
Top