Index/Collect function returns #INVALID VALUE only when the formula comes back with an empty value

Hi folks!

I'm trying to reference a cross-sheet cell value using the following formula:

=INDEX(COLLECT({PM Practice Training Completion: Completion Date}, {PM Practice Training Completion data: LXP ID}, [LXP Course ID]@row , {PM Practice Training Completion data: Username}, "Test User"), 1)

This formula is located in a Date type cell. The target cell is a Date type cell.

This formula works fine if there is a row in the target sheet that matches the two criteria in the Collect function - it brings back the date in that cell.

However, if there is NO row that matches, I expect to see a 'blank', but I'm getting the #INVALID VALUE error instead.

This formula is wrapped in an IF formula for other purposes, which needs to know if this Index/Collect formula resulted in a Date or a Blank, so using the IfError function is not helpful to 'hide' the error.

Suggestions on this one?

Thanks,

Tony

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

What is the rest of your formula? You are going to have to wrap it in an IFERROR, but you don't have to have it output a blank if it is an error. You can have it output something like "No Match Found".

twarner

Full formula is below.

=IF(NOT(ISDATE(INDEX(COLLECT({PM Practice Training Completion: Completion Date}, {PM Practice Training Completion data: LXP ID}, [LXP Course ID]@row , {PM Practice Training Completion data: Username}, "Test User"), 1))), INDEX({PM Practice Training Exceptions: Test User}, MATCH([LXP Course ID]@row , {PM Practice Training Exceptions: LXP Course ID}, 0)), INDEX(COLLECT({PM Practice Training Completion: Completion Date}, {PM Practice Training Completion data: LXP ID}, [LXP Course ID]@row , {PM Practice Training Completion data: Username}, "Test User"), 1))

I had first tried an ISBLANK in place of the NOT(ISDATE) in the first part of the IF statement, but that didn't work either. So long as that first INDEX/COLLECT comes back with a value, it displays that value (a date), but if not, it comes back with INVALID VALUE and never does the alternate INDEX/MATCH side of the IF statement.

Paul Newcome

Right. So try an IFERROR to output a text string instead of an error. That way it will work through the NOT(ISDATE()) portion and move along as appropriate.

twarner

That was my first attempt (I tried again, to be sure). When I do that, I still get the results when the INDEX/COLLECT finds something (which is correct), but when it does not find anything, it skips right over the "value if true" INDEX/MATCH part of the IF formula and outputs the IFERROR output.

Paul Newcome

Can you provide that formula that has the IFERRORs in it?

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.