Help with 2 index matches based on an isblank cell in originating sheet

Trace Darragh

I've been fighting this one all morning, so really hope you can help.

I have have a sheet that captures changes to employee records which index matches to a company organisation sheet to pull the employee's name based on the employee ID that has been entered in the request form. However, sometimes the rerquest is for multiple employees, so there is no employee ID entered. I have this working fine using formula

=IF(ISBLANK([Affected Spirit ID]@row), "", INDEX({Spirit ORG}, MATCH([Affected Spirit ID]@row, {Employee_ID}, 0), 3))

However, I know need to make it index match to a second orgnisation sheet as well which cover updates to the employee records and this I have working well using formula

=IF(ISBLANK([Affected Spirit ID]@row), "", INDEX({Spirit Org Additions}, MATCH([Affected Spirit ID]@row, {Spirit Org Additions Employee_ID}, 0), 3))

How do I combine these 2, so that if the Affected Spirit Id is blank it index matches to both Spirit Org and Spirit Org additions, I keep getting #incorrect argument with

=IF(ISBLANK([Affected Spirit ID]@row), "", (IFERROR(INDEX({Spirit ORG}, MATCH([Affected Spirit ID]@row, {Employee_ID}, 0), 3) <> "", INDEX({Spirit ORG}, MATCH([Affected Spirit ID]@row, {Employee_ID}, 0), 3), IF(IFERROR(INDEX({Spirit Org Additions}, MATCH([Affected Spirit ID]@row, {Spirit Org Additions Employee_ID}, 0), 3), "") <> "", INDEX({Spirit Org Additions}, MATCH([Affected Spirit ID]@row, {Spirit Org Additions Employee_ID}, 0), 3), ""))))

I'm tearing my hair out

Jgorsich

Looks like you just missed an if() statement right around your first iferror(), consequently you are executing logic with your first <>"" that doesn't belong to any formula. You can see before your second iferror() you've got your if() statement in place.

You are basically trying to say this, correct?

Try this

=IF(ISBLANK([Affected Spirit ID]@row), "", INDEX({Spirit ORG}, MATCH([Affected Spirit ID]@row, {Employee_ID}, 0), 3))

if it fails or pulls a blank, then try this

=IF(ISBLANK([Affected Spirit ID]@row), "", INDEX({Spirit Org Additions}, MATCH([Affected Spirit ID]@row, {Spirit Org Additions Employee_ID}, 0), 3))

If so, I think this will do it for you (not tested though):

=IF(ISBLANK([Affected Spirit ID]@row), "",if(iferror(INDEX({Spirit ORG}, MATCH([Affected Spirit ID]@row, {Employee_ID}, 0), 3),"")<>"",INDEX({Spirit ORG}, MATCH([Affected Spirit ID]@row, {Employee_ID}, 0), 3),iferror(INDEX({Spirit Org Additions}, MATCH([Affected Spirit ID]@row, {Spirit Org Additions Employee_ID}, 0), 3),""))

this should be saying:

if [Affected Spirit ID]@row is blank, throw a blank

else if INDEX({Spirit ORG}, MATCH([Affected Spirit ID]@row, {Employee_ID}, 0), 3) doesn't throw an error and comes back with a result, show that result

else if INDEX({Spirit Org Additions}, MATCH([Affected Spirit ID]@row, {Spirit Org Additions Employee_ID}, 0), 3) doesn't throw an error, show that result

else throw a blank.

Trace Darragh

That's it!! Thank you so much. You are exactly correct in your understanding and now my sheet is working perfect!

Jgorsich

Glad I could help!