Cross-Reference Formula in Sheet 1 with Multiple Rows that MATCH in Sheet 2

aschneiderheinze1025
edited 01/28/25 in Smartsheet Basics

RESOLVED: Scroll down to my second reply to see how I handled this.

In Sheet 1, I have 5 columns that are for required documents to be submitted as part of a development process. In Sheet 2, I collect submissions that people can use to submit a form with a URL to one of those documents. Each form asks for a program code (e.g., 5000).

In Sheet 1, I create a cross-reference formula in each of the 5 columns that looks in Sheet 2 to see if a link was submitted for each of the documents, by matching the Program Code in that row in Sheet 1.

So, Smartsheet should look in the corresponding column (e.g., one of the documents is Budget), if there is a link, and the Program Code in that row in 5000, it should return that link in Sheet 1.

It is likely the documents are submitted at different times, so Sheet 2 might look like this:

Sheet 2 (collects information from form submission)

In this screenshot, there have been three submissions, each with Program Code 5000, but each with a link (and submission date) for the Market Scan, Business Canvas Model, and Budget documents.

PROBLEM:

In Sheet 1, it is only returning the link and the date in the row of the first instance of the Program Code. In the screenshot, since the link to the Business Canvas Model is in the top-most row for Program 5000, it only shows the link for the Business Canvas Model in Sheet 1.

Sheet 1 with Cell Reference Formulas to Sheet 2

There should be a link and date for Market Scan and Budget, but those are both blank.

Here is the formula in the H-Business Model Canvas (Link):

=IFERROR(INDEX({DocumentSubmission-BusinessModelCanvas}, MATCH([Program Code]@row, {DocumentSubmission-ProgramCode}, 0)), "")

{DocumentSubmission-BusinessModelCanvas} refers to the column in Sheet 2 where links would appear if submitted in a form. [Program Code}@row is "5000" in this case, a column in Sheet 1, and {DocumentSubmission-ProgramCode} is a reference to the "Program Code" column in Sheet 2.

As a side note, [Business Model Canvas (Link)]@row just copies whatever is in the column to the left. I had hoped to be able to create a Text-hyperlink to avoid showing the users full URLs. But that functionality (like =HYPERLINK) isn't available yet in Smartsheet.

Any idea why it's only paying attention to the top-most row with the 5000 Program Code (or stops after the first row only? If for some reasons it's only looking at the first (or top-most) instance, how can I get around this so I can look at multiple submissions (given these links are submitted by different people at different times in the development process?

Answers

  • I asked ChatGPT and it suggested using a format like this:

    =INDEX({Sheet2 Column A}, MATCH(1, ({Sheet1 Column 2} = {Sheet2 Column B}) * (ISBLANK({Sheet2 Column A}) = FALSE), 0))
    

    So I adapted that for my Market Scan column:

    =INDEX({DocumentSubmission-MarketScan}, MATCH(1, ([Program Code]@row = {DocumentSubmission-ProgramCode}) * ("Market Scan" = {DocumentSubmission-DocumentType}), 0))
    

    The references to Sheet 2 are the same, but I added {DocumentSubmission-DocumentType} which is a reference to a column in Sheet 2 in which the user selected the document type from a drop-down list.

    So, there are two MATCH criteria (for the Market Scan link):

    1. The [Program Code]@row in Sheet 1 matches the program code in the Sheet 2 reference: {DocumentSubmission-ProgramCode}
    2. The Sheet 2 reference {DocumentSubmission-DocumentType} is "Market Scan".

    Unfortunately, I m getting an #INVALIDOPERATION. Any confirm I can include two MATCH criteria, and if this is the right format to use?

  • Sorry, was really eager to resolve this —- and came across a solution proposed by @Paul Newcome back in 2020 when someone posted similar question, and it worked perfectly in my situation.

    =INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)
    

    My updated formula:

    =IFERROR(INDEX(COLLECT({DocumentSubmission-Budget}, {DocumentSubmission-ProgramCode}, [Program Code]@row, {DocumentSubmission-DocumentType}, "Budget"), 1), "")
    


  • Georgie
    Georgie Employee

    Hey @aschneiderheinze1025,

    Thank you for posting your solution. I'm glad you got this resolved!

    Georgie

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions