Formula not pulling correct data

Hi,

I am using this formula to bring back the corresponding data to the Room:

=INDEX({Rooms/Facility/Org Codes FacilityName}, MATCH([Room #]@row, {Rooms/Facility/Org Codes Room#}, 0))

When I select the Room from a dropdown, I select the first A409 and it pulls the 0100 and 0191 appropriately. However, when I use the second A409, it's pulling the same 0100 and 0191 rather than pulling the 2100 or 2181.

Any idea what might be wrong with my formula?

Thank you!

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

The INDEX/MATCH will only pull from the first match working from the top to the bottom of the reference data. Assuming the above screenshot is of the reference data, can you provide a screenshot of the working sheet as well?

maineL

Absolutely. This is the sheet I'm referencing

This is the worksheet that I'm trying to pull into:

So based on the room selected (and there are a lot, just some are duplicated with different KFS college and Org code), I'd like Facility Code, Facility Name, KFS College and Org code to pull in from the data sheet above.

I can provide more details if needed.

Thank you!

Paul Newcome

IS there a way you are specifying whether it should pull the first entry for that room from the reference data vs the second entry?

maineL

I am not sure if I'm referencing it right: =INDEX({Rooms/Facility/Org Codes FacilityName}, MATCH([Room #]@row, {Rooms/Facility/Org Codes Room#}, 0))

Paul Newcome

That's correct syntax for an INDEX/MATCH, but you are saying you want to pull in the second one. How do you indicate on each row whether it should pull the first or second?

maineL

I am unsure. I assumed I could get away with selecting the whole column, which does work, just not where the duplicates are… Any ideas?

maineL

Paul Newcome

This would need to be an indicator on the main sheet. The one where you are putting the formula. What is the overall goal of this? How do you plan to use this data, how are new rows entered, etc.?

maineL

So, I have a form created that populates into the "main sheet" where I have the formulas. People will complete the form and will be selecting the room from the drop down menu. My intention is for that information to automatically populate into the main sheet. All new data will populate to the top of my main sheet.

Paul Newcome

Ok. So new rows are added at the top of the sheet via form. That brings us back to… How do we decide whether we are pulling the first entry for room A409 or the second entry for room A409 from the reference table?

maineL

So, maybe I could do a conditional format for the duplicated rooms to visually remind me to check those 2 specific fields to be sure both are used. It wouldn't matter to the individuals selecting the room, but for reporting purposes for me, it does matter. But maybe having the visual flag could remind me to reference both to be sure there is 1 code for each…

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.