Listing cell contents from multiple cross reference fields in one cell (if statement, index/match?)

Hi,

I am trying to do an if/index/match statement to pull the contents of a cross reference cell if it is not blank into a single cell. I need to be able to list several non-blank values with a paragraph break in between in a single cell and I am drawing a blank.

Right now I have this:

=IF({ParkingLotBlockedComm} <> "", INDEX({ParkingLotBlockedComm}, MATCH([Archibus Bldg ID]@row , {BuildingID}), 0), "")

ParkingLotBLockedComm is my comments field, and I need the value/contents of that cell to be listed for the matching row using the Archibus Building ID number. I have several comments fields that i need to list so it would be:

ParkingLotBlockedComm

Paragraph break

SidewalksBlockedComm

Paragraph break

etc

I hope this makes sense, and someone can help.

Lisa

Find more posts tagged with

INDEX

MATCH

if formula

Accepted answers

All comments

KPH

Hi @Murz

You can't combine an IF with an INDEX in that way but a JOIN COLLECT formula will do what you need.

JOIN Function | Smartsheet Learning Center

COLLECT Function | Smartsheet Learning Center

Your delimiter should be CHAR(10) which will give you a clear line between the paragraphs.

If you need help creating the formula please let us know if you will have multiple rows in your source sheet for each ID, or just one row with multiple columns?

Murz

I have one row with multiple columns

Paul Newcome

I would use this:

=IFERROR(index/match, "")

to individually pull over each of the columns you want pulled over one at a time into separate columns.

Then you would use something like this to join them all together:

=JOIN(COLLECT([First Helper]@row:[Last Helper]@row, [First Helper]@row:[Last Helper]@row, @cell <> ""), CHAR(10))

Murz

I"d prefer to not use helper columns in my main sheet, but if this is the only solution, then I get it

Paul Newcome

Technically you can do this without helper columns, but it turns into a much harder to manage solution. It would basically be a series of IF statements added together.

=IF(IFERROR(INDEX({1st Column To Pull Over}, MATCH(………………………)), "") <> "", INDEX({1st Column To Pull Over}, MATCH(………………………)) + CHAR(10), "") + IF(IFERROR(INDEX({2nd Column To Pull Over}, MATCH(………………………)), "") <> "", INDEX({2nd Column To Pull Over}, MATCH(………………………)) + CHAR(10), "") + IF(IFERROR(INDEX({3rd Column To Pull Over}, MATCH(………………………)), "") <> "", INDEX({3rd Column To Pull Over}, MATCH(………………………)) + CHAR(10), "") + IF(IFERROR(INDEX({4th Column To Pull Over}, MATCH(………………………)), "") <> "", INDEX({4th Column To Pull Over}, MATCH(………………………)) + CHAR(10), "") + IF(IFERROR(INDEX({5th Column To Pull Over}, MATCH(………………………)), "") <> "", INDEX({5th Column To Pull Over}, MATCH(………………………)) + CHAR(10), "") + …………………………………

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.