Index Match Question

CFT

Hello,

I am having some trouble getting a date from 2 different sheets to populate in a 3rd. As a standalone formula, these work just fine. Is there a way to combine so that I am indexing 2 ranges and returning the requested value if there is a match?

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

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

If the above is not possible, I have built helper columns that I can hide to the side but am having trouble getting a formula to return the value if a date is present from either cell. It will only work on the true value, not the if false value. The match would be the cell contents (DATE in Cell) vs. Blank or #No Match. Here are examples of attempts that work 1/2 way:

=IF(ISDATE(Completed@row), Completed@row, IF(ISDATE(Current@row), Current@row))

=IF(ISDATE(Completed@row), Completed@row, OR(ISDATE(Current@row), Current@row))

=IF(ISDATE(Completed@row), Completed@row, Current@row)) (This one didn't work at all.)

Thanks in Advance for any assistance you can provide.

Carson Penticuff

There may very well be a cleaner way to do this, but this should work. Use an IF(ISDATE()) statement to check whether there is a match in the first sheet. Add in an IFERROR() to prevent throwing a #NO MATCH if it is not found. If there is a match in the first sheet, the match is then returned. If there is no match in the first sheet, the second sheet is then evaluated. I did not include the IFERROR() for the second sheet, so you will get #NO MATCH if there is no match in either sheet.

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

Carson Penticuff

This should take care of it:

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

CFT

Thank You!

Carson Penticuff

There may very well be a cleaner way to do this, but this should work. Use an IF(ISDATE()) statement to check whether there is a match in the first sheet. Add in an IFERROR() to prevent throwing a #NO MATCH if it is not found. If there is a match in the first sheet, the match is then returned. If there is no match in the first sheet, the second sheet is then evaluated. I did not include the IFERROR() for the second sheet, so you will get #NO MATCH if there is no match in either sheet.

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

CFT

Carson, I thought it worked great, but realized that if the initial Index [Location ID]@row column was blank, it was returning a blank match. Yes it works, but any way to add something to ignore blank cells in the index and return no result?

CFT

CFT

Carson Penticuff

This should take care of it:

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

CFT

Thank You!