Return a value if an item matches
Hello - new to formulas & struggling to get one to work.
I have two sheets: One with the values in column format (Payer CSS Responses by Initiative) - this was done so specific drop down options can be chosen; and one that contains those same columns but in row format so that it easy to view (Payer CSS Template).
What I'm looking to do is: Return the values in the columns from the first sheet into the row on the second sheet, but ONLY if the opportunity name matches. For example: if the opportunity name Empire (O123456) is on the Payer CSS Template, return the values from that row in from the Payer CSS Responses by Initiative.
I've figured out how to return the value by creating this formula, but I don't want to have to do this for every Payer CSS Template sheet once its created.
=INDEX({Payer CSS Responses by Initiative Range 1}, 1)
Thank you all for your help with this!
Best Answer
-
You would use an INDEX/MATCH.
=INDEX({Column To Pull From}, MATCH(Response1, {Opportunity Column}, 0))
Answers
-
You would use an INDEX/MATCH.
=INDEX({Column To Pull From}, MATCH(Response1, {Opportunity Column}, 0))
-
Thank you Paul, this did the trick!!
-
Happy to help. 👍️
-
Hello again - now I'm running into the issue of having maxed out the number of cross sheet references, what would be the easiest way to get the information transferred over from the 'Payer CSS Responses by Initiative' sheet to the 'Payer CSS Template'?
Thank you!
-
Which limit exactly? What is the error message you are getting?
-
See the screenshot. This is for the exact same sheets listed above.
I have approx. 475 unique columns that need to be filled out in the 'Payer CSS Responses by Initiative' sheet, then transferred over to the Payer CSS Template', but I've hit the limit per the error message.
I looked into DataTable or DataMesh opts, but those just transfer information from one column to the next, not to a specific row like I need here.
Thank you!
-
You can only have 400 columns max in a single sheet, so you may need to rethink your overall structure.
There are a number of posts here in the Community that outline the details for getting around the cross sheet reference limit. You should be able to find them with a search. The basic idea is that you use a JOIN function (or series of JOIN functions depending on character counts) to create a string of data containing multiple columns that need to be brought over. Then you can bring this one string over and parse it out as needed.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!