Index Join formula issue

Ok, I've been sitting on this for a couple hours and I just can't figure out what I'm missing. I have a column formula that is selectively working on certain rows, but there is no obvious difference between those rows.

The formula is: =INDEX(COLLECT({APStepCode}, {SubcategoryID}, [SAP Subcategory ID]@row, {VBC ID List}, CONTAINS([Title VBC]@row, @cell)), 1)

In essence, we are referencing the same two columns in another sheet - the VBC Title column and the SAP Category ID. I have triple checked the ranges to ensure I have the right columns - everything is correct.

All the other 200 rows and matches work, except for the ones with the Title VBC "WS LEGACY" and SAP Subcategory ID "Admin" or "XX_WS_LEGA.XX". But I can't see any differences in that data as compared to any of the other data in the entire list.

When I split it up, the error appears only when I add the second range and criterion, but for the life of me I can't figure out what's wrong since the error only appears in select matches and the cells I'm referencing are exactly copy-paste variants of each other.

GRRRRRR - going a little crazy! Can anyone spot what my mistake is?

Sheet with the errors:

Reference Sheet:

And in case I'm missing something, here are the range references:

MAIN RANGE:

SUBCATEGORY RANGE: (For criterion1 to match against)

VBC Category Range: (For criterion2 to match against)

Find more posts tagged with

Accepted answers

All comments

Ramzi K

@Dominique WINTHER

Very odd indeed. I re-created your entire solution using one sample row from each sheet and I get the correct result. Usually when you get an INVALID VALUE error it means there is no match. In this case there is.

Try a few things:

Convert your formula to column level formula and see if that resolves.
See if there are any special characters in any of the reference cells. So instead of copy paste, try hand typing.
There is a duplicate value in your {VBC ID List} for these rows. Try removing the duplicate value
Lastly, try troubleshooting the second part of the formula alone and see if you get an error. So try this formula:

=INDEX(COLLECT({APStepCode}, {VBC ID List}, CONTAINS([Title VBC]@row, @cell)), 1)

Feel free to share these sheets with me and I can try to troubleshoot with you.

Cheers,

Ramzi

Dominique Rose Winther

Hi Ramzi, thanks for looking at this. In an odd way its reassuring that you ran into the same issue. I've checked points 1-3 and those don't appear to be the issue. On point 4, the problem is definitely on that side of the formula, but I still can't spot what it is.

I've just shared the two sheets - if you can take a look, I'd be super grateful. :::stumped:::

Ramzi K

@Dominique WINTHER

Hi Dominique, thanks for sharing the sheets. That really helped. I sent you a separate email with the solution. But in case you didn't see it and for the benefit of the community, here it is:

The value in your main sheet for “WS LEGACY” has 2 spaces between WS and LEGACY. The reference sheet value does not. I added a space in the reference sheet and now everything works. Several ways to fix this in the future and this is just one.

The way I discovered is I looked at the length of both values using =LEN( function and one was 9 characters and the other was 10. Then I did a character by character compare and found it.

I hope this helps you out.

Cheers,

Ramzi

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.