Lookup to return multiple rows

timshaw · 07/19/22

I have a sheet containing invoices for all projects (one invoice per row). I'd like to have a separate sheet which returns all the invoices listed against a single project code (with each invoice still on its own line - basically a filter with the criteria set by a cell reference). Is there a lookup which can do this?

A report would do this but only for all projects, not just one.

Paul Newcome · 07/19/22

You could use a report and set a filter for that specific project code.

timshaw · 07/20/22

We have hundreds of projects - it would mean creating a report for each one. Looking for something more dynamic.

Genevieve P. · 07/29/22

Hi@timshaw

I agree that it sounds like a report is best in this scenario... however what I would do isGroup by the Projectsso you can collapse all those rows, then only expand the one Project you want to see the line items for.

Otherwise, you coulduse a JOIN(COLLECT formulato bring together all the content into one cell, based on the project code listed in another cell (so you could change the code and it would auto-update). The difference here is that the formula can't parse this out byrow,it gathers the information into a cell.

A final alternative would be to have a column with numbers listed (1 - however many). Then you could use this column as a reference in an INDEX(COLLECT formula, to bring back different data per-cell (e.g. if it's the first match, the second match, the third...)

=INDEX(COLLECT({Invoice Column}, {Project Column}, [Project Name]@row),[Number Column]@row)

Let me know if you'd like a more detailed explanation of this last formula and I'd be happy to post some screen captures.

Cheers,

Genevieve

Bwoods113 · 03/08/23

I am trying to do something very similar to create a purchase order from a parts database would you mind sharing a few screenshots of the Index Collect formula you are referencing. I think it would work for my application.

@Genevieve P.

Thank you,

Brandon

Genevieve P. · 03/08/23

Hey@Bwoods113

I have an example of this with a screen capture on this other post, here:

Hi @BrianDN<\/a> <\/p>

You could use the INDEX function to look at the values in your "Raw" column and return the first distinct value, then the second distinct value, and so on, however this would require a helper column to let the formula know if it's looking for the First, Second, Third, etc. <\/p>

For example:<\/p>

https:\/\/us.v-cdn.net\/6031209\/uploads\/SBD1NB8KUP9H\/screenshot-2023-02-27-at-10-25-15.png<\/a><\/p>

=IFERROR(<\/strong>INDEX(DISTINCT(COLLECT(Raw:Raw, Raw:Raw, <>"")), Helper@row), "")<\/strong><\/p>

This collects only distinct values from the Raw column, as long as the cell is not blank. Then the reference to the Helper@row<\/strong> at the end of the formula looks at the number in the Helper column to determine which one to bring back. <\/p>

My formula open is currently looking at the seventh<\/em> value in the Raw list, but there isn't a 7th value, so the IFERROR makes the cell turn Blank instead of giving an error. <\/p>

Let me know if that makes sense and works for you!<\/p>

Cheers,<\/p>

Genevieve<\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"BrianDN\",\"userID\":155667}}},{\"insert\":\" \\nYou could use the INDEX function to look at the values in your \\\"Raw\\\" column and return the first distinct value, then the second distinct value, and so on, however this would require a helper column to let the formula know if it's looking for the First, Second, Third, etc. \\nFor example:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/SBD1NB8KUP9H\\\/screenshot-2023-02-27-at-10-25-15.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/SBD1NB8KUP9H\\\/screenshot-2023-02-27-at-10-25-15.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"=IFERROR\"},{\"attributes\":{\"bold\":true},\"insert\":\"(\"},{\"insert\":\"INDEX(DISTINCT(COLLECT(Raw:Raw, Raw:Raw, <>\\\"\\\")), Helper@row), \\\"\\\"//www.santa-greenland.com/community/discussion/comment/\"},{\"attributes\":{\"bold\":true},\"insert\":\")\"},{\"insert\":\"\\n\\nThis collects only distinct values from the Raw column, as long as the cell is not blank. Then the reference to the \"},{\"attributes\":{\"bold\":true},\"insert\":\"Helper@row\"},{\"insert\":\" at the end of the formula looks at the number in the Helper column to determine which one to bring back. \\nMy formula open is currently looking at the \"},{\"attributes\":{\"italic\":true},\"insert\":\"seventh\"},{\"insert\":\" value in the Raw list, but there isn't a 7th value, so the IFERROR makes the cell turn Blank instead of giving an error. \\nLet me know if that makes sense and works for you!\\nCheers,\\nGenevieve \\n\"}]","format":"rich","dateInserted":"2023-02-27T10:28:00+00:00","insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-25T20:36:29+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/364468#Comment_364468","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/364468#Comment_364468

让我知道帮助!

Cheers,

Genevieve

Bwoods113 · 03/09/23

Good Morning,@Genevieve P.

I feel like I am close on this and if I can get it working it is going to genrate exactly what I want, I am getting an error on the formula. I attached a screenshot of what I have. I am trying to have it look up the equipment ID, then use the helper row, and report back the part number. Then I am hoping to copy that formula to return the rest of the part numbers and quantities. Any help would be greatly appreciated. Thank you for your time.

Bwoods113

Parts List Index Distict Collect 1.JPG

Parts List Index Distict Collect 2.JPG

Genevieve P. · 03/09/23

Hi@Bwoods113

It looks like we just need to rearrange the placement of your {references} and criteria.

The structure should be like this:

=INDEX(DISTINCT(COLLECT({Column to return}, {Column with criteria}, "Criteria")),rownumber)

Try this:

=INDEX(DISTINCT(COLLECT({Parts Database Range 3}, {Parts Database Range 2}, [Primary Column]@row)), Helper@row)

This is assuming Range 3 is your column with "372" listed and Range 2 is the yellow column.

Cheers,

Genevieve

Bwoods113 · 03/09/23

@Genevieve P.

=INDEX(DISTINCT(COLLECT({Parts Database Range 3}, {Parts Database Range 2}, [Primary Column]@row,)) Helper@row)

This gives me #UNPARSABLE

=INDEX(DISTINCT(COLLECT({Parts Database Range 3}, {Parts Database Range 2}, [Primary Column]1)), Helper@row)

This gives me invalid value, I apologize I just cant tell where the error is.

UPDATE: I got it I had the references backwards Thank you so much

Genevieve P. · 03/09/23

Hey@Bwoods113

Glad you got it working!

Lookup to return multiple rows

Answers

Categories