Returning Multiple Lines from one cell, but in a list

New Contributor

Hi Excel Folks,


I'm hoping someone more knowledgeable than me may be able to assist here.


I am trying to speed up a quotation spreadsheet and if I can make this work it will hugely decrease what is largely a manual process at the moment. I want to be able to take a list of tasks supplied via a customer (this could be 10 lines to 1000 lines) and return task specific data for each line, which is stored in a seperate Tab within the file. This is fairly straightforward in principal via lookups, but I have hit a stumbling block.


The issue I have is that the data I need to return in the look up may appear with multiple lines, and I need to return all of those lines for each task.


For example, if a customer wishes to quote for task A, B, C, D.... tasks A and B may return 1 line each, C may need to return 4 lines and D may need to return 3 lines. Standard look up will only return the first line for each task.


I almost need to do a return which pulls in any/all tasks with the corresponding task code.


I have managed to do this in a very clunky format with helper columns and assigning unique IDs to each line, but I am sure there must be a better / more efficient way to do this. The whole goal of the exercise is to make this as easy / user friendly as possible.    


Can anyone help please? 

5 Replies
You could create a FILTER formula for this task. If you're able to provide sample data then a formula can be drawn up.



Thanks Patrick - I suppose a picture speaks a thousand words, I should have led with that, apologies!


I am sure there is a way to do it, I just haven't been able to get it to work without stepping it out into several parts, creating unique ID fields which is making it lengthier and more complex than I would like. The whole idea is the input / return section is easy that anyone can pick it up.


Hopefully this will make a little more sense. I have oversimplified it but hopefully gets the idea across. 

Search & Return multiple.JPG

best response confirmed by Sergei Baklan (MVP)



I'll step you through my solution.


Dynamic named item 'Data'.  It expands/contracts based on number of entries in Column A:

=LET(a, Data!$A$2:$G$100000, nonblank, COUNTA(TAKE(a, , 1)), TAKE(a, nonblank))


Dynamic named item 'Tasks'.  Same idea as the above. It will expand/contract based on number of requested tasks entered.

=LET(a, Summary!$A$4:$A$10000, nonblank, COUNTA(a), TAKE(a, nonblank))

An array constant - 'Header'. Nothing fancy here. It will be appended to the data pull.

    "PART QTY",


Lambda 'PullTasks'. It runs each of the requested task numbers through a FILTER w/Data. Results are stacked vertically.

=LET(filtered, FILTER(Data, TAKE(Data, , 1) = v), VSTACK(a, filtered))

The formula you'll see in the sheet:





Thanks Patrick, I will give it a go, this is potentially game changing though!

Thank you so much!!!
You're welcome!