INDEX/COLLECT to add Error Statement if no TDL #

Hello, and thanks in advance for your help on my formula. I do not understand why I am getting this error on my formula. I am working on adding my formula to the sheet in the first screenshot. I am referencing the sheet in the second screenshot. The 1 and 2 reference which part is which for the named ranges. I know why I am getting this issue (sort of).I discovered In this case that the TDL # (far left columns) does not exist on the sheet. Now in this instance, I would like to return "No TDL #", and obviously if the TDL does exist, then the result should display like it is. But I am not sure how to convert this formula.

=INDEX(COLLECT({1.0 TDL-ProjectSites_MP}, {1.0 TDL-ProjectSites_TDL}, [TDL #]@row), 1)

Find more posts tagged with

Formulas

Accepted answers

Carson Penticuff

From your screenshot, I see [TDL Number] as the column name. If you substitute this for [TDL #] in either of the formulas above, do you still receive #unparseable?

All comments

Carson Penticuff

@SherryFox Give this a try. I didn't test it, but I believe it should work.

=IF(COUNTIFS({1.0 TDL-ProjectSites_TDL}, [TDL #]@row) > 0, INDEX(COLLECT({1.0 TDL-ProjectSites_MP}, {1.0 TDL-ProjectSites_TDL}, [TDL #]@row), 1), "No TDL #")

Melissa Yamada

Hello @SherryFox

You can also use the IFERROR function.

Try this:

=IFERROR(INDEX(COLLECT({1.0 TDL-ProjectSites_MP}, {1.0 TDL-ProjectSites_TDL}, [TDL #]@row), 1),"No TDL #")

SherryFox

@Carson Penticuff and @Melissa Yamada ,

I am getting errors (Unparsable with both your formulas). These formulas will be converted to column formulas if that matters. Since I can not edit my original post, I took revised screenshots. The first shows the sheet where the formula will live, in the Mission Partner cell. It will access the TDL Number cell as a lookup reference point. It will then use that lookup on the 2nd sheet (1.0 TDL-PrpjectSites) to find the data. The TDL # can be found in the column called Title (the named range is in red), and the results array is in the MissionPartner column (again with the named range for the results array in red). Perhaps this may help clear it up.

Carson Penticuff

From your screenshot, I see [TDL Number] as the column name. If you substitute this for [TDL #] in either of the formulas above, do you still receive #unparseable?

SherryFox

@Carson Penticuff

I did not even notice that difference!!! That was exactly what I needed in order to make the correction to my account. Thanks so very much!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.