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

Jessica Suarez
Jessica Suarez ✭✭✭
edited 03/21/23 in Formulas and Functions

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.


Answers

  • Paul Newcome
    Paul Newcome Community Champion

    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.

  • 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
    Paul Newcome Community Champion

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

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

  • Jim B
    Jim B ✭✭✭

    @Paul Newcome Hi Paul, I couldn't get this to work. I think because the match returns #NO MATCH if no match - as opposed to null / "".

    I did get this to work though:

    =
    IF(CONTAINS([IIID_]@row, {MS1ID}), INDEX({MS1Test}, MATCH([IIID_]@row, {MS1ID}, 0)),
    IF(CONTAINS([IIID_]@row, {MS2ID}), INDEX({MS2Test}, MATCH([IIID_]@row, {MS2ID}, 0)),
    IF(CONTAINS([IIID_]@row, {MS3ID}), INDEX({MS3Test}, MATCH([IIID_]@row, {MS3ID}, 0)),
    IF(CONTAINS([IIID_]@row, {MS4ID}), INDEX({MS4Test}, MATCH([IIID_]@row, {MS4ID}, 0)),
    IF(CONTAINS([IIID_]@row, {MS5ID}), INDEX({MS5Test}, MATCH([IIID_]@row, {MS5ID}, 0)),
    IF(CONTAINS([IIID_]@row, {MS6ID}), INDEX({MS6Test}, MATCH([IIID_]@row, {MS6ID}, 0)),
    IF(CONTAINS([IIID_]@row, {MS7ID}), INDEX({MS7Test}, MATCH([IIID_]@row, {MS7ID}, 0)),
    0)))))))

    Where I need to search 7 separate sheets for various matches. Find these a bit easier to write up than using multiple iferrors.

    Just putting this here in case anyone else comes looking for a index/match across multiple sheets solve.

    Jim

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!