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")))
Answers
-
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.
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"
-
Hi Bassam,
Here is a sample of the sheet.
Thanks!
-
Hey @Jen Evans
I can't open your screenshot so forgive the basic question. Your [Last Name] column is a date field?
-
Morning! No, [Last Name] is a text column but the Date formula has worked with it before.
-
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?
-
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?
-
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
-
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!