Formula to look up matching columns and return populated fields
I have two Smartsheet's.
 The First Smartsheet contains all the original data.
 The Second Smartsheet will contain unique values from one of the columns (the column with a 1 in the IDUn... column).
In the Second Smartsheet, I need formulas in the individual column row fields to look up and match the value in the IDUn... column/row and pull information from multiple other columns (L1Q1; L2Q2; etc) that have populated fields.
First Smartsheet:
Second Smartsheet:
Problem I am running into:
The following formula works only on the first row that contained matching values:
=INDEX({First Smartsheet Range 11}, MATCH(IDUn...@row, {First Smartsheet Range 1}, 0))
I need a formula that will look to the next matching row to get the next column/row of data.
Best Answer

I finally got it to work: =IFERROR(INDEX(COLLECT({First Smartsheet 11}, {First Smartsheet Range 1}, Cours...@row, {First Smartsheet Range 11}, "<>"), 1), "")
@jason tarpinian  Thank you so much for your help!!
Answers

You are looking to use COLLECT, the next level in the wonderful world of INDEX/MATCH! COLLECT can be used with INDEX to create a custom range, then you can return the row within that collected range to pick the nth value.
=INDEX(COLLECT({First Smartsheet Range 11}, {First Smartsheet Range 1}, IDUn...@row), n)
Where the "n" is the row to return within your INDEX function. So you can collect all 3 rows in the first Smartsheet where IDUn... is 1, then return either the 1st, 2nd, 3rd... whatever match you want to return.
INDEX(COLLECT()) can also entirely replace INDEX(MATCH()) in all scenarios if you'd like. Where "n" would always just be 1, for the first match.
Jason Tarpinian  Sevan Technology
Smartsheet Aligned Partner

@Jason Tarpinian  Thank you for your input. I have a flaw in my scenario, so the formula didn't work.
The IDUn... is not helpful because it limits the collection of data from the First Smartsheet.
I still need to collect the First Smartsheet data from multiple different columns and rows and insert all the column data into one consolidated row in the Second Smartsheet.
The other matching column is the Cours... column/rows for both Smartsheet's. My formulas can't get past that first match to pick up any additional rows containing the column data that I need to transfer.

I finally got it to work: =IFERROR(INDEX(COLLECT({First Smartsheet 11}, {First Smartsheet Range 1}, Cours...@row, {First Smartsheet Range 11}, "<>"), 1), "")
@jason tarpinian  Thank you so much for your help!!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!