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

Lauren Hughes

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??

Kleerfyre

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.

Lauren Hughes

@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

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.

Lauren Hughes

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

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

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

Paul Newcome

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