INDEX/COLLECT criteria not pulling correct unique ID

Hi There,

I'm at my wits end and have spent way too many hours on this already. I am trying to pull in a unique ID from another sheet using a matching date, zip code and county. Here is the formula and a screenshot example of results:

=INDEX(COLLECT({2022 ID}, {2022 Event Date}, [Event Date]@row, {2022 Zip Code}, [Zip Code]@row, {2022 County}, County@row), 1)

As you can see, the same ID is being pulled in for rows that have different combinations of date/zip/county. All of the rows shown in the first image have a matching row with a unique ID in the reference sheet. As a note: the first row should be the only 22225 match.

A secondary issue - I am getting an #INVALID VALUE result on other rows where the three criteria actually are unique.

I do not have any errors on the referenced columns. I have played around with 1, 0 orders and combinations at the end formula. I've tried including a match on just the date. I'm so lost at this point.

Thanks in advance for your help!

Find more posts tagged with

Formulas

Accepted answers

All comments

=Chris Palmer

Hello @AshK

You can try creating a helper column that creates a Unique Identifier in the sheets you are extracting data from.

In this example, I am combining data in the row to create the unique identifier:

=ID@row + Country@row + [Zip Code]@row + Date@row

Then use the Unique Identifier Column to get your results.

I hope this helps!

Tomasz Giba

Hi @AshK

In addition to great Mr. Chris idea as an alternative you can try to use this formula:

=JOIN(DISTINCT(COLLECT({2022 ID}, {2022 Event Date}, [Event Date]@row, {2022 Zip Code}, [Zip Code]@row, {2022 County}, County@row)), " / ")

AshK

I appreciate those lightning fast responses! Unfortunately, neither worked for me.

Using @Mr. Chris solution of creating helper columns in both sheets and using =INDEX({2022 ID}, MATCH([ID Fill Helper]@row, {2022 ID Helper})), it returned a #NO MATCH result (even though a manual search on each sheet's helper columns matched a unique ID that aligned).

Using @Tomasz Giba solution formula =JOIN(DISTINCT(COLLECT({2022 ID}, {2022 Event Date}, [Event Date]@row, {2022 Zip Code}, [Zip Code]@row, {2022 County}, County@row)), " / "), I received the same result as were on the example rows I provided showing 22225 with my original formula.

BUT...maybe we're getting close, because this formula using DISTINCT did return multiple IDs for instances where more than one row met the same criteria, and of the two ID options provided, one of them was correct for each row!

Any other suggestions? Y'all are great with thinking outside the box!

Tomasz Giba

Hi @AshK

It works exactly how you said: it returns all ID's that were happened in the same place in the same time. So what result you would expect on the last screenshot you provided?

The last two lines should be:

22236

22223

AshK

Hi @Tomasz Giba

Your solution only works on instances where there are two possible options due to overlapping criteria (i.e. same date and zip). On rows that have all 3 criteria unique, it's still pulling in the same ID for multiple rows - the original problem I am trying to solve.

bsikes

@AshK

If I'm understanding what you're trying to do, your formula seems to be working fine for me.

Sheet with formula:

Referenced Sheet:

Each ID returned matches the id on the reference sheet where all 3 values match. If there isn't a match, an #Invalid Value error is returned which I would have expected. If there's multiple matches, only the 1st ID is returned.

Is that what you're trying to accomplish?

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.