Is there any possibility for Index Match to search multiple ranges?

Jessica Suarez

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.

For example,

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.

Screenshot 1:

Screenshot 2:

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.

Paul Newcome

You can use an IF statement to nest them together.

=IF(INDEX({AVP}, .........)) <> "", INDEX({AVP}, .........)), INDEX({VP}, .........)))

Basically you INDEX/MATCH on the AVP and say IF that output "is not blank" then use it. Otherwise use the VP INDEX/MATCH.

Jessica Suarez

Hi @Paul Newcome

Thanks for the recommendation. How would you say an output is not blank in SS? Would you write out "is not blank" or would I need to use an IFERROR. Just making sure I understand for when I attempt this.

Paul Newcome

You would use <> "" the way it is used in the sample formula.

=IF(INDEX({AVP}, .........)) <> "", INDEX({AVP}, .........)), INDEX({VP}, .........)))