Return a value if an item matches

robinarndt31

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!

Paul Newcome

You would use an INDEX/MATCH.

=INDEX({Column To Pull From}, MATCH(Response1, {Opportunity Column}, 0))

Paul Newcome

You would use an INDEX/MATCH.

=INDEX({Column To Pull From}, MATCH(Response1, {Opportunity Column}, 0))

robinarndt31

Thank you Paul, this did the trick!!

Paul Newcome

Happy to help. 👍️

robinarndt31

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!

Paul Newcome

Which limit exactly? What is the error message you are getting?

robinarndt31

Smartsheet3.png

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!

Paul Newcome

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.