Losing hair from index:match

Hey All. I've read through every post I can find on index match but my formula is still coming up unparsable no matter what I try.

I am trying to reference columns across 2 sheets. I've created references in the end sheet under Manage References. Here's the latest version of my formula and then a list of things I've tried that still don't work.

These two are on a separate sheet called 360PAS where I created the reference with the following names:

Column Name in Manage Reference for match: [TASK_NAME] - The item being referenced

Column Name for lookup: [TASK_END] - the return data (this is a date column in date format)


in the sheet where I'm trying to get the value to populate....

=index([TASK_END],MATCH([Primary Column]@row,[TASK_NAME],0))

Other solutions i've tried include

=index([TASK_END],MATCH("Opening Date",[TASK_NAME],0))

=index([TASK_END]:[TASK_END],MATCH([Primary Column]@row,[TASK_NAME]:[TASK_NAME],0))

and along with that, adding iferror to every combo of the items above.

I CAN get index match to work if I draft the formula using data in the same sheet that I'm writing the function, but cross sheet reference kills it every time.

Any help is greatly appreciated!

Tags:

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @dwfender,

    Could you please provide a screenshot of your two sheets after removing any sensitive information? This will help me craft the correct formula tailored to your needs.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭

    Hi @dwfender

    Your formula looks sound as does your explanation and the references, only thing i would check is your column on the source sheet where you are entering the formula set up as a date column? as trying to index match a date into a non date column will return that error.

    Hope that helps

    Paul

  • Hey @Bassam Khalil

    Working on it now and here's what I have.

    The date column formatting is set to DATE.

    The task names on the left are linked cell references so I know the naming conventions stay up to date.

    Report where I want to generate the index(match)


    Source Date


    Managed References


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi@dwfe@dwfender

    Ok I will check it and come back to you soon

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @dwfender,

    please try the following formula in the report sheet and convert it to column format formula:

    =IFERROR(INDEX(COLLECT({TASK_END}, {TASK}, [Primary Column]@row), 1), "") 
    

    here is a screenshot shows the result:

    the source sheet

    the report sheet ( the formula )


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!