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 (L1-Q1; L2-Q2; 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.

Find more posts tagged with

Formulas

help

Accepted answers

CNC

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!!

All comments

Jason Tarpinian

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.

CNC

@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.

CNC

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!!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.