Look up Function - Multiple Matches

Saimir Qalliu

I'm having trouble coming up with the correct function and am hopping someone can help.

I have Sheet A that contains the data show in screenshot below.

I also have a Sheet B, where I am trying to pull data from Sheet A, when I reference a 'Project #'.

I want the name of the Salesmen & corresponding Amounts to show in rows in Sheet B.

VLookup; Index/Match, will not work as there are multiple matchings. I have attempted combining these with other functions, to no avail.

Any help is much appreciated. Thank you in advance!

Kelly Moore

Hi @Saimir Qalliu

The JOIN/COLLECT function should work for you. In both column in your destination sheet (B), make sure that the columns are text-wrapped. As part of the JOIN function, you can specify the delimiter. In the formula below, it uses a line break - CHAR(10). However, this is only apparent if the column is formatted as wrapped text.

=JOIN(COLLECT({sheet A Salesman column}, {sheet A Project #}, $[Column2]$whateverRowNumber), CHAR(10))

=JOIN(COLLECT({sheet A Amount column}, {sheet A Project #}, $[Column2]$whateverRowNumber), CHAR(10))

A report would also give you this information in the layout you need.

cheers

Saimir Qalliu

Hi @KDM ,

Thank you for the response to my question. Unfortunately I am not getting the results. Maybe I am doing something wrong. I put in the function you wrote and I get no results returned by it. The function is not malfunctioning - it simply returns nothing. I am including a screenshot here, maybe that sheds some light into why this outcome. Any suggestions?

Kelly Moore

Hi @Saimir Qalliu

I notice you have multiple rows with the formula (notice the blue arrows). I assumed this was a type of data entry lookup form where you put in the project number you wanted and it would bring the search results forward. Because we're using a JOIN, all of the results will be in a single cell, but with a line break ,CHAR (10), separating the results. If you change the Project #, the results will change as they are based on the project number.

Did you create the cross sheet ranges by clicking the blue link in the formula window, or did you copy paste my original formula into your cell? Each cross reference needs to be created and should be the entire column in the source sheet. Also, as a good practice, you can rename the range to be the column name instead of the generic range number that smartsheet generates - that way both you and the community can understand what a range refers to.

Let me know

Saimir Qalliu

Hi @KDM

The blue links are due to the formula pasted/entered in them. However, as you say, this will not work because the results will be in one cell, albeit with a line break.

I am trying to accomplish what is detailed in the link below, under this heading: How to Use a Lookup to Return Multiple Matches

https://www.smartsheet.com/advanced-vlookup-multiple-criteria?_ga=2.138209520.1726473778.1614200668-1575052407.1614200668

However, I am not having any luck translating that to a Smart Sheet function.

Does this clarify the objective @KDM ?

Thank you!

Kelly Moore

The formula provided will pull all your salesperson's names into a cell and the respective dollar amounts will be pulled into the other cell. I believe that is what you requested

Did you create the cross sheet references or did you copy paste from the formula I provided?

Kelly

Saimir Qalliu

Hi @KDM

I typed in the function you suggested - referencing the other sheet (Sheet A) where applicable, and it does not render results.

Regardless, even if it produced results, that solution would not work.

What I need is for each name & amount to show up in Sheet B in its own row. Below is a screenshot of the desired outcome.

Kelly Moore

Would a report work for you? Or, if you have access to the pivot app, a pivot report?

You can mark the question as unresolved to alert others we still need ideas.

Sorry this doesn't work you

cheers,

Kelly

Genevieve P.

Hi @Saimir Qalliu

I agree with @KDM that the Pivot App would be an ideal solution for this.

However if you do not have this premium application, then what you'll need to do is set up the table in your second sheet with each sales person's name. Once we have this reference, you can use a SUMIFS formula to look in the source sheet and SUM the data in the Amount column, based on the criteria of the Sales person and the Project Number.

Something like this:

=SUMIFS({Sheet A Amount column}, {Sheet A Salesman Column}, [Primary Column]@row, {Sheet A Project Number Column}, [Column 2]$3)

The reference to [Column 2]$3 is where your project number is housed in Sheet B. Does this make sense?

Cheers!

Genevieve