IfError with Index and Match

Jen Evans

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

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.

Jen Evans

Hi Bassam,

https://app.smartsheet.com/sheets/72QVj3FCpv49CQcgC3WrRCRRQj4WV5vp3V8XgJR1

Here is a sample of the sheet.

Thanks!

Kelly Moore

Hey @Jen Evans

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

Jen Evans

@KDM

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

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?

Jen Evans

@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

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

Jen Evans

Hi @KDM

I granted access to you on the sheet.

Screen shot below.

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

Hey @Jen Evans

Were you able to get your formula working?

Jen Evans

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!