nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

INDEX/MATCH ERRORS

Emily2267

I have used the index/match formula successfully , but in my current project, for some reason it isn't working.

I have my two sheets (Open Adp) and (A/N Referrals).

In Open Adp, there is a Case ID and then a person's name associated with that ID.

I'm trying to have the person's name from Open Adp sheet be pulled over into my A/N Referrals sheet using a Case ID as a match. The Case ID is a distinct number in the Open Adp and in the A/N Referrals sheets.

Also, there can be more than one name match (though typically no more than 3) for a Case ID.

The formula I'm using is:

=INDEX({Open Adp Range 1}, [Case ID]@row , {Open Adp Range 2})

I've gone through multiple processes and get different errors from "No Match" to "Invalid Data Type". And when I did manage to get it to return any data, it returned the same name over and over despite the Case IDs being different. I tried unsuccessfully to use the Collect function.

My columns match in terms of Properties. I've tried changing the Case ID to ensure they are numbers and then tried ensuring they are all text on both sheets. I can't find any difference between properties of the given columns.

I'd appreciate any help!!

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Are you able to provide a screenshot fo the formula open in the sheet as if you are baout to edit it? The formula in your post seems to be missing the MATCH function.

Assuming the MATCH and associated parenthesis are all that's missing, try using a zero in the third position of the MATCH function.

=INDEX(……….., MATCH(……….., …………., 0))

Emily2267

Sorry - that was just my bad when I retyped it. It's this:

Just now when I tried it, it returned the same name for every line.

hello_aj

Can you try to add ,0 after the {Open Adoption Range 2}, just like Paul said.

=index({Open Adoptions Range 1},Match([REF ID]@row,{Open Adoptions Range 2},0))

Emily2267

So when I enter this, I get the #unpareseable error.

=index({Open Adoptions Range 1},Match([REF ID]@row ,{Open Adoptions Range 2},0))

Emily2267

I feel like I'm going insane because I've used this formula many times with no problems.

Paul Newcome

Can you provide a new screenshot with the new formula open in a cell as if you are about to edit it?

Emily2267

OMG it just worked!! I have no idea why it didn't work the last 100 times I entered it!! But thank you!!!

Paul Newcome

Happy to help. 👍️

Emily2267

So, problem #2. Same two sheets. I'm matching the Case ID number to pull in the ch names. However, there may be more than one child (each on it's own row) with the same Case ID. How to get the formula to return all matches rather than just the first one? I did try the collect formula, but wasn't getting any results.

Paul Newcome

You would use a JOIN/COLLECT combo.

=JOIN(COLLECT({Range To Pull In}, {Criteria Range}, @cell = "criteria"), "delimiter of choice")

Emily2267

Worked perfectly!! Thanks!!

Emily2267

Worked perfectly!! Thanks!!

Emily2267

Last question, I promise!! In the Join Collect formula, is there a way to have it stay blank if there is no match rather than putting in the delimiter over and over?

Paul Newcome

Give this a try:

=JOIN(COLLECT({Range To Pull In}, {Range To Pull In}, @cell <> "", {Criteria Range}, @cell = "criteria"), "delimiter of choice")

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.