Index Match Question

CFT

I am trying to auto populate training status from an active Training Tracking sheet and a Completed Training Tracking sheet into a 3rd sheet (Field Activity Tracker) using Index Match.

Individually, my formulas work great unless the {Location ID}@row column I am Indexing is blank (Still works, just matching a blank column on other side):

=INDEX({Training Requests / Tracking Range 3}, MATCH([Location ID]@row, {Training Requests / Tracking Range 1}, 0))

=INDEX({Completed Training Requests / Tracking Range 3}, MATCH([Location ID]@row, {Completed Training Requests / Tracking Range 2}, 0))

I need a way to combine the formulas to pull back a match from either sheet, deal with errors, and return blanks if the Location ID is blank.

I tried multiple variations of the following with no luck:

=IF(ISBLANK(IFERROR(INDEX({Training Requests / Tracking Range 3}, MATCH([Location ID]@row, {Training Requests / Tracking Range 1}, 0)), 0)), INDEX({Training Requests / Tracking Range 3}, MATCH([Location ID]@row, {Training Requests / Tracking Range 1}, 0)), INDEX({Completed Training Requests / Tracking Range 3}, MATCH([Location ID]@row, {Completed Training Requests / Tracking Range 2}, 0)))

TY!

Kelly Moore

Hey @CFT

Try this

IF([Location ID]@row<>"", IFERROR(INDEX({Training Requests / Tracking Range 3}, MATCH([Location ID]@row, {Training Requests / Tracking Range 1}, 0)), INDEX({Completed Training Requests / Tracking Range 3}, MATCH([Location ID]@row, {Completed Training Requests / Tracking Range 2}, 0))))

Does this do work for you?

Kelly

Kelly Moore

Hey @CFT

Try this

IF([Location ID]@row<>"", IFERROR(INDEX({Training Requests / Tracking Range 3}, MATCH([Location ID]@row, {Training Requests / Tracking Range 1}, 0)), INDEX({Completed Training Requests / Tracking Range 3}, MATCH([Location ID]@row, {Completed Training Requests / Tracking Range 2}, 0))))

Does this do work for you?

Kelly

CFT

Thank you! Works great.