VLOOK UP or INDEX/MATCH to pick values for duplicated unique ID based on certain criteria

Sam Abd

Hi , I have a main tracker that tracks projects progress, and a separate field tracker that has our field team submissions through a smartsheet form.

The field workers submit completed date for projects using a unique ID. In some cases they may submit future projects with the anticipated date of completion. In the form there is a question that ask the submitter whether project completed or if this submission is only a projections of future work.

Due to the above, in the field tracker we may encounter situations where for the same unique project ID we have two dates (two submissions from form), one is the future projections, and one is the actualized date when the project completed. As mentioned there is also a 3rd column as part of the fields entries that differentiate between which ones is projection which one actualized.

In the main tracker I have set up an index/match that based on the project ID bring in the date. The point is, when we only have projections, I want to use that as the best date. Once the project is actualized I want the index/match (or vlookup) to get the date that's been tagged as actualized date. The actualized date may or may not be greater than the projection date and there could be situations where someone submit an actualized date, and then someone by mistake think project is not done and submit a projections, so the order in which rows are added in the look up (field) table should not matter, only logic should be:

In case of duplicated project ID, look at the column that tags which row is the actualized row, and grab that over the projections, otherwise just show the projections.

Mark.poole

The problem with Vlook up is the ref columns need to be right next to each other. And Index Match only allows one criteria.

Try using an Index(Collect) instead.

What this does is allow you to use multiple criteria in your index(Match) instead of just 1.

=Index(Collect({Range Ref}, {Criteria ref},Criteria,{Criteria ref 2},Criteria2),1)

Sam Abd

Thank you Mark, do you think I need a conditional formula to contain the INDEX. The reason is the current formula I have works perfectly:

Scenario A =IFERROR(INDEX({Project completion date in the field tracker}, MATCH([Project ID in the main tracker]@row, {Project ID in the field or look up tracker}, 0)), "")

This returns the result if there is no duplicate. If there is a duplicated ID and it's an expected duplicate (meaning someone came and made a projection of future work and another person came and actualized it and marked the project as completed) then I need a formula to examine first if there is a duplicate or not, if no duplicate do scenario A formula and done, if it's scenario B and it is the case of two identical unique ID then examine based on additional criteria which row to take. Hope this helps and thank you again.

Scenario B formula = If (Scenario A, then use Scenario A formula, otherwise use ??? )