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!
L
Answers
-
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?
-
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!
L
-
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?
-
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))
L
-
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?
-
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?
-
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.?
-
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.
-
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?
-
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…
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!