IF(INDEX(MATCH Function - For Resource Tracking

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

IF(INDEX(MATCH Function - For Resource Tracking

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

Best Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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?

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

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

  • Hi Paul,

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

    Thanks for your help,

    David

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

Sign In or Register to comment.