Is there any possibility for Index Match to search multiple ranges?
We currently are doing a reorg in my department and now AVPs (where once only having managers as direct reports) have both admin and managers and in turn, will affect certain columns and reports that they view.
Is there any possibility for Index Match to search multiple ranges if first range found nothing? or suggestions to pull accurate data.
Scenario 1: admin Shelby Randall, is reporting to a manager, Jolene. Jolene is then reporting to an AVP, Jeff. Jeff is then reporting to a VP, Kristin. This is a normal structure.
Scenario 2: In a more unique scenario, we have an admin, Siobhan who is reporting to an AVP, Jeff. Jeff is then reporting to a VP, Kristin.
Because we were at a more consistent structure, where admins were always reporting to managers and managers were always reporting to AVPs, we were always able to bring in their current next in line because they were always coming from one column. (Refer to screenshot 1)
Now I am seeing if there are any recommendations for screenshot two/Scenario #2. Specifically line item #2 where an admin is reporting to an AVP.
The last 3 columns will be held on a different source sheet but the idea is when I input Jeff's name in mgmt level 1, that it will look through master list - level 1 (Mgr) and if not found then search master list level 2 (AVP) column and bring back Kristin's name. Is this a possibility? Do we have any recommendations? Anything is appreciated.
Help Article Resources
Check out the Formula Handbook template!