IF(INDEX(MATCH Function - For Resource Tracking

davidmartin95826

Hello,

I have an Procurement Engagement Form (PEF) and a Resource Tracker. I'm trying to pull the Contract End Date into the Resource Tracker using the Function below.

Its working perfectly in Excel, although unfortunately I'm struggling to get it to work in Smartsheet.

=IF({Procurement Engagement Form Range 3} = "Complete", INDEX({Procurement Engagement Form Range 2}, MATCH([Resource Name]24, {Procurement Engagement Form Range 1}, 0)), "N/A")

Thanks for your help,

David

Paul Newcome

Ok. The problem is most likely stemming from the logical statement portion of your IF formula. You are basically stating that if the entire range equals "Complete". If you are trying to narrow it down to a specific cell, you would need to adjust this portion.

Can you explain your process in more detail?

Paul Newcome

Happy to help! 👍️

Paul Newcome

What is in the ranges of each of your cross sheet references?

davidmartin95826

Hi Paul,

Please find updated below.

PEF = Procurement Engagement Form Sheet

RT= Resource Tracker Sheet

=IF(PEF_Status Column - Drop down = "Complete", INDEX(PEF_End Date Column - Date restricted, MATCH(RT_ Resource Name, PEF_Resource Name Column - freetext, 0)), "N/A")

Paul Newcome

Ok. The problem is most likely stemming from the logical statement portion of your IF formula. You are basically stating that if the entire range equals "Complete". If you are trying to narrow it down to a specific cell, you would need to adjust this portion.

Can you explain your process in more detail?

davidmartin95826

Hi Paul,

Perfect :) I can't believe I didn't spot that.

Thanks for your help,

David

Paul Newcome

Happy to help! 👍️

Paul Newcome

@davidmartin95826 Please don't forget to mark the most appropriate answer as "helpful" so that other know a solution has been found.