# Index Match Question

Options
✭✭

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

Thank You!

• ✭✭✭✭✭✭
Options

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

• ✭✭
edited 08/03/23
Options

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?

• ✭✭
edited 08/03/23
Options
• ✭✭
edited 08/03/23
Options
• ✭✭✭✭✭✭
Options

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

• ✭✭