IfError with Index and Match

Hi all,

I am trying to troubleshoot a formula that has worked for me in the past and wondering if anyone can look it over.

Typically this formula is used for a travel grid that denotes when someone is Work/Travel Out (X/T) or just Travel Out (To). Happy to share the sheet itself as well to look at further.

=IFERROR(DATE($[Last Name]$5, $[Last Name]$6, $[Last Name]$7) + INDEX([Day 0]$7:[Day 24]$7, 1, MATCH("X/T", [Day 0]@row:[Day 24]@row, 0)), IFERROR(DATE($[Last Name]$5, $[Last Name]$6, $[Last Name]$7) + INDEX([Day 0]$7:[Day 24]$7, 1, MATCH("To", [Day 0]@row:[Day 24]@row, 0)), "N/A")))

Tags:

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Jen Evans 

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    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"

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Jen Evans

    I can't open your screenshot so forgive the basic question. Your [Last Name] column is a date field?

  • @KDM

    Morning! No, [Last Name] is a text column but the Date formula has worked with it before.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    That's interesting. I've never seen that before. If you remove that DATE function from your formula, is the Index Match still working as expected? Since I have never seen the DATE function used that way, what was the output? Is it possible to insert screenshot of your data that is open to the community - or a mock up?

  • @KDM

    If i remove the Date function, I get an error message that date is expected. I made a mockup sheet with no sensitive info, that's the link above. Is there a better way I can share it with you?

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Jen Evans

    When you removed the DATE function, did you remove the entire (DATE($[Last Name]$5, $[Last Name]$6, $[Last Name]$7) from the formula? I wondered if the INDEX/MATCH portion of the formula was working or if it were only the DATE portion that was causing the error.

    The link is not open -if it went through you would have a sheet request from me. A picture screenshot would be open to the community. Without the screenshot I am unclear what the DATE portion of your formula was returning- joined text? A number? I wondered if there was a different formula that would yield the same outcome.

    Kelly

  • Hi @KDM

    I granted access to you on the sheet.

    Screen shot below.



  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    aah. numbers in the [Last Name] column. Now that makes sense. I was very confused picturing 'Last Names' in that column and trying to figure how in the world you got THAT to work.

    I wonder if one side of your formula or the other is behaving as text instead of a number. The VALUE() function forces text that looks like a number to behave like a number.

    To see if it's one half or the other, in a single cell, I would first test the DATE function

    =DATE($[Last Name]$5, $[Last Name]$6, $[Last Name]$7)

    if it works, great. If it doesn't, try the VALUE()

    =DATE(VALUE($[Last Name]$5), VALUE($[Last Name]$6), VALUE($[Last Name]$7))

    If the DATE side works, test the INDEX/MATCH in a single cell by hard coding a DATE into your formula to see if the INDEX/MATCH adds to it. You can wrap the INDEX/MATCH in VALUE()


    Kelly

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Jen Evans

    Were you able to get your formula working?

  • Hey @KDM

    I did not unfortunately.

    The original formula I had, which worked in an old grid, was:

    =IFERROR(DATE($Name$5, $Name$6, $Name$7) + INDEX([Day 1]$7:[Day 6]$7, 1, MATCH("Ti", [Day 1]@row:[Day 6]@row, 0)), "N/A"))))))

    I tried your Date formula which properly gave me the correct initial date:

    =DATE(VALUE($[Last Name]$5), VALUE($[Last Name]$6), VALUE($[Last Name]$7))


    However, when I combined them...every cell would just give me the initial date and not that date + however many days in addition it would be.

    =IFERROR(DATE(VALUE($[Last Name]$5), VALUE($[Last Name]$6), VALUE($[Last Name]$7)) + INDEX([Day 0]$7:[Day 24]$7, 1, MATCH("Ti", [Day 0]@row:[Day 24]@row, 0)), "N/A").


    I was able to get it to work in my "test grid" - but there must be some underlying issue with the grid I have all built out where it won't work. Next time I build one from scratch though, I will use the formula you gave me instead. Hoping that will work.


    Thanks for all the insight!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!