Lookup to return multiple rows

timshaw

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

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

timshaw

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

Genevieve P.

Hi @timshaw

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

Otherwise, you could use a JOIN(COLLECT formula to 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 by row, 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

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.

Hey @Bwoods113

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

https://community.smartsheet.com/discussion/comment/364468#Comment_364468

Let me know if that helps!

Cheers,

Genevieve

Bwoods113

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.

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

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

Hey @Bwoods113

Glad you got it working! 🙂

LakeWaconia

Genevieve - Trying to do something very similar. I have a sheet (Weldment BOM) with "Stock Code" that appears multiple times and I want to pull over every time that same Stock Code occurs the "Raw P/N" from (Part 3) sheet.

=INDEX(DISTINCT(COLLECT({Part 3 Raw P/N}, {Part 3 Parent P/N}, [Stock Code PMI #]@row)), Helper@row)

I'm getting "#Invalid Value" returned

Paul Newcome

@LakeWaconia Try a JOIN/COLLECT instead.