Hello, I am trying to use INDEX MATCH and the OR function. I'm referencing 3 other sheets try to say if we find x in any of these 3 sheets I want y from those sheets. x and y will only be present in 1 of the 3 other sheets. However every I do this I either get an error code or unparsable. These are a few of the formulas I've tried: * INDEX({Project Intake (Clinical Research) Range 2}, MATCH([Row ID]@row, {Project Intake (Clinical Research) Range 1}) OR INDEX({Abandoned Projects Range 2}, MATCH([Row ID]@row, {Abandoned Projects Range 1}) OR INDEX({Project Planning (Completed Setups) Range 2}, MATCH([Row ID]@row, {Project Planning (Completed Setups) Range 1})) * OR(INDEX({Project Intake (Clinical Research) Range 2}, MATCH([Row ID]@row, {Project Intake (Clinical Research) Range 1}), INDEX({Abandoned Projects Range 2}, MATCH([Row ID]@row, {Abandoned Projects Range 1}), INDEX({Project Planning (Completed Setups) Range 2}, MATCH([Row ID]@row, {Project Planning (Completed Setups) Range 1}))) * I also tried doing OR for INDEX and OR for Match separately Please help me! If I shouldn't be using INDEX, MATCH, or OR please educate me.

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, and OR Function

cerway

Hello, I am trying to use INDEX MATCH and the OR function. I'm referencing 3 other sheets try to say if we find x in any of these 3 sheets I want y from those sheets. x and y will only be present in 1 of the 3 other sheets. However every I do this I either get an error code or unparsable.

These are a few of the formulas I've tried:

INDEX({Project Intake (Clinical Research) Range 2}, MATCH([Row ID]@row, {Project Intake (Clinical Research) Range 1}) OR INDEX({Abandoned Projects Range 2}, MATCH([Row ID]@row, {Abandoned Projects Range 1}) OR INDEX({Project Planning (Completed Setups) Range 2}, MATCH([Row ID]@row, {Project Planning (Completed Setups) Range 1}))
OR(INDEX({Project Intake (Clinical Research) Range 2}, MATCH([Row ID]@row, {Project Intake (Clinical Research) Range 1}), INDEX({Abandoned Projects Range 2}, MATCH([Row ID]@row, {Abandoned Projects Range 1}), INDEX({Project Planning (Completed Setups) Range 2}, MATCH([Row ID]@row, {Project Planning (Completed Setups) Range 1})))
I also tried doing OR for INDEX and OR for Match separately

Please help me! If I shouldn't be using INDEX, MATCH, or OR please educate me.

Find more posts tagged with

Forms

Sheets

Formulas

Accepted answers

cerway

For people in the future this worked:

=IFERROR(IFERROR(INDEX({Project Intake (Clinical Research) Range 2}, MATCH([Row ID]@row, {Project Intake (Clinical Research) Range 1}, 0)), INDEX({Abandoned Projects Range 1}, MATCH([Row ID]@row, {Abandoned Projects Range 2}, 0))), INDEX({Project Planning (Completed Setups) Range 3}, MATCH([Row ID]@row, {Project Planning (Completed Setups) Range 2}, 0)))

I was missing the ", 0" at the end of the index/match. Thank you Paul for your assistance!

All comments

Paul Newcome

You would write separate INDEX/MATCH formulas for each of the three sheets then nest them together with IFERROR statements.

=IFERROR(IFERROR(INDEX/MATCH Sheet 1, INDEX/MATCH Sheet 2), INDEX/MATCH Sheet 3)

cerway

Is there any way I can do this all in one column as a single formula? It doesn't have to be with index/match. I know in Excel it can be done in 1 formula so I was hoping I could do that here as well.

I also am okay with creating a formula that pulls from 1 of the sheets (I have this formula already: "INDEX({Project Intake (Clinical Research) Range 2}, MATCH([Row ID]@row, {Project Intake (Clinical Research) Range 1})") and then says "error" if it doesn't match anything from that sheet. Right now, the formula will pull the information and if it isn't there it will pull information from a nearby cell (example: supposed to pull info for #302, pulls info for #304 if #302 doesn't exist).

Paul Newcome

That would be exactly what I outlined. You have 3 different INDEX/MATCH formulas (one for each sheet) that then get combined into a single formula using the IFERROR statements.

cerway

I think I'm confused. To confirm, it's not one single formula that can do all of the above in one cell in one column. I'd have to split it up into 4 total formulas in different columns and cells on one sheet. Correct?

Paul Newcome

No. That is not correct. It is one single formula that does everything in one single cell.

You would have one single formula that follows a logic similar to the IFERROR in my first comment.

=IFERROR(IFERROR(INDEX/MATCH Sheet 1, INDEX/MATCH Sheet 2), INDEX/MATCH Sheet 3)

Each "INDEX/MATCH Sheet #" gets replaced with a working INDEX/MATCH designed to pull from the sheet mentioned.

cerway

Okay, thank you for clarifying. It works for Sheet 1 and Sheet 3 but doesn't work properly for Sheet 2. I double checked the references and am not sure how to get it working properly. Thoughts?

Edit to add, that this is the exact formula I'm using:

=IFERROR(IFERROR(INDEX({Project Intake (Clinical Research) Range 2}, MATCH([Row ID]@row, {Project Intake (Clinical Research) Range 1})), INDEX({Abandoned Projects Range 1}, MATCH({Abandoned Projects Range 2}))), INDEX({Project Planning (Completed Setups) Range 3}, MATCH({Project Planning (Completed Setups) Range 2})))

cerway

For people in the future this worked:

I was missing the ", 0" at the end of the index/match. Thank you Paul for your assistance!

heyjay

Do you think your second and third MATCH is incomplete? Your missing the SEARCH_VALUE before the RANGE. And make sure to add or use zero (0) for the SEARCH TYPE.

=IFERROR(
IFERROR(

INDEX({Project Intake (Clinical Research) Range 2},

MATCH([Row ID]@row, {Project Intake (Clinical Research) Range 1})),

INDEX({Abandoned Projects Range 1},
MATCH({Abandoned Projects Range 2}))),

INDEX({Project Planning (Completed Setups) Range 3},
MATCH({Project Planning (Completed Setups) Range 2})))

eg:

MATCH(xyz@row, {Abandoned Projects Range 2},0)

If you are still having issues, try to break it down. Test each of your INDEX/MATCH formula one by one to identify the problematic syntax.

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.