Changing from Vlookup to Index match

GillianMcGovern

I am looking to change the formula below so that when I add columns it doesnt upset connected data. Was looking to change to index match but going round in circles. I can send further screen shots if necessary. Thanks for looking ❤

This is the formula currently in the sheet.

=IFERROR(IF(ISBLANK(VLOOKUP(Country@row + " TT", {SR FORECASTER Range 4}, 8, false)), "", IF(VLOOKUP(Country@row + " TT", {SR FORECASTER Range 4}, 8, false) < {Todays Date}, VLOOKUP(Country@row + " TT", {SR FORECASTER Range 4}, 15, false), "")), "")

I essentially am trying to return actual days column from sheet name SR forecaster matching JIRA ID but also when meets certain criteria i.e country@row = " TT" and it is after todays date

Paul Newcome

I am assuming the first screenshot is where the formula will live and the second screenshot is the source data?

You would first need a helper column on the source sheet that pulls in the Jira ID from the parent row.

=PARENT([JIRA ID]@row)

Then in the metrics sheet you would use something along the lines of

=IFERROR(INDEX(COLLECT({Source Sheet Actual Days}, {Source Sheet Dates}, @cell> TODAY(), {Source Sheet Country Column}, @cell = Country@row + " TT", {Source Sheet Helper Column}, @cell = [JIRA ID]@row), 1), "")

GillianMcGovern

Yes thats correct. Thanks Paul almost there I think but not sure about the date part think thats maybe where I am going wrong? Getting an incorrect argument. Have selected end date but not sure if this is right? Only want to populate if days if date is after today.

=IFERROR(INDEX(COLLECT({Source Sheet Actual Days}, {Source Sheet Dates}, @cell> TODAY(), {Source Sheet Country Column}, @cell = Country@row + " TT", {Source Sheet Helper Column}, @cell = [JIRA ID]@row), 1), "")

=IFERROR(INDEX(COLLECT({SR FORECASTER Actual Days}, {SR FORECASTER End Date}, @cell > TODAY(), {SR FORECASTER Country}, @cell = Country@row + " TT", {SR FORECASTER Jira Helper}, @cell = [JIRA ID]@row), 1), "")

Paul Newcome

Double check that your ranges are each only single columns by clicking on the column header when creating the reference.

GillianMcGovern

Thank you so much all sorted ❤ much appreciated

Paul Newcome

Happy to help. 👍️