How to index match from multiple sheets, but needing it to pull data for EITHER, not for both

Options

I have 2 sheets with employee names and start dates - 1 sheet shows new hires that have not started, and the 2nd sheet shows employees that have already started. There is an automation that moves the row from the 1st sheet to the 2nd sheet once their start date has been reached.

I am needing to index match on a separate page the start dates for employee names we enter in, and it needs to be able to pull from EITHER of these sheets. When i used Index Collect it returned an error value and i noticed the rulle for this formula is both criteria have to be found and met on both pages referenced. I need it to only pull data from one or the other.

Can anyone help??

Answers

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭
    Options

    Could you not drop in an OR statement in your formula? Post a copy of your formula and we can look at it and see what options are possible.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • Lauren Hughes
    Options

    @Kleerfyre below is my formula - I am not sure how or where to add in the OR function, would love an example!



    =IFERROR(COLLECT({Clinician Master Roster start date}, {Clinician Master Roster name}, [Employee Name]@row, {open reqs Start Date}, {Open Reqs name}, [Employee Name]@row), "")

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭
    Options

    I am guessing that your two sheets are Clinician Master Roster and Open Reqs, is that correct? If so, just split those out into their own COLLECT statements. Add the OR before both of the COLLLECT statements.


    =IFERROR(OR(COLLECT({Clinician Master Roster start date}, {Clinician Master Roster name}, [Employee Name]@row),COLLECT({open reqs Start Date}, {Open Reqs}, [Employee Name]@row))," ")


    See if that will work.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • Lauren Hughes
    Options

    Yes those are my 2 sheets.

    When I use that formula, now i am only showing blanks returned as if the content isn't found. below is the exact formula i used.

    =IFERROR(OR(COLLECT({Clinician Master Roster start date}, {Clinician Master Roster name}, [Employee Name]@row), COLLECT({open reqs Start Date}, {Open Reqs name}, [Employee Name]@row)), "")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You are on the right track. Try this instead:

    =IFERROR(INDEX({Clinician Master Roster start date}, MATCH([Employee Name]@row, {Clinician Master Roster name}, 0)), INDEX({open reqs Start Date}, MATCH([Employee Name]@row, {Open Reqs name}, 0)))

  • Lauren Hughes
    Options

    This is still not working - showing as #NO MATCH or #INVALID DATA TYPE

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide any screenshots? Have you verified that at least one of the reference sheets has a match on it?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!