• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

lookup based on two criteria

Hello experts,

I need your help.  See attached sample file.

On Sprint tab, add formula to column B
Lookup Site ID of Col A from AD tab Col A
Find associated row that matches "office Manager"
then return associated email from column D
See sample entries.

Same for "General Manager" in Column C of Sprint tab.


Gary - Cincinnati
  • 2
  • 2
1 Solution
Since your AD tab has 32,000 + entries, and you are looking up based on multiple criteria, I would recommend that you add a helper column to AD.

in E2:


copied down

This concatenates the Location and Title.

Then formula in Sprint!B2:

=IFERROR(INDEX(AD!C:C,MATCH(A2+0&"_*"&"Office Manager"&"*",AD!E:E,0)),"")

in C2:

=IFERROR(INDEX(AD!C:C,MATCH(A2+0&"_*"&"General Manager"&"*",AD!E:E,0)),"")

both copied down
garyrobbinsAuthor Commented:
Thanks, NBVC.

Formula stops when I get down to Site IDs with letters in them, e.g. 26A.  How could the solution be changed?

FYI, I changed your formula to return the "emails" from Col D instead of "displayNames" from, Col C:

=IFERROR(INDEX(AD!D:D,MATCH(A2+0&"_*"&"Office Manager"&"*",AD!E:E,0)),"")

Try changing the helper formula to:


and then your formula in Sprint sheet to:

=IFERROR(INDEX(AD!D:D,MATCH(TEXT(A2,"000")&"_*"&"Office Manager"&"*",AD!E:E,0)),"")
garyrobbinsAuthor Commented:
Excellent solution, NBVC!

Thank you for the creative and timely solution.

Experts-Exchange ROCKS!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now