Returning Multiple lines with the same reference (XLOOKUP)? For a quotation look up document

[MattMartin]

Hi All, 

I have been struggling with this for some time and cant quite seem to get over this hump. 

I am trying to build a quotation spreadsheet to rapidly increase what is done manually by copy / paste at the moment (Madness!!!)

I want to be able to take a list of tasks supplied via a customer - this could be 10 lines or up to 1000 lines! I want to return task specific data for each task reference, which will then form the basis of the quote. This data is stored in a separate Tab within the file and will be updated as new tasks are added to the database. (In reality its in a separate document but for this example just say its in another tab). The principle is fairly straightforward in via lookups, but I have hit a stumbling block.

I have had some help before from this forum which was good but not quite what I was after. I have even tried enlisting ChatGPT but I can't quite ask the right question it seems! 

For example, if a customer wishes to quote for several tasks... some of these tasks may only be stored on one line which the XLOOKUP function works fine for. However, some tasks may need to return more than one line (with the same task code) because they have additional requirements.

I have managed to do some very clunky attempts with helper columns and assigning unique IDs to each line, but this is definitely not the preference. (These are not shown in this example) 

I am sure there must be a more efficient way to do this. The whole goal of the exercise is to make this as easy, fast and user friendly as possible.    

Can anyone help please!? Hopefully my example screen shot makes sense

[#2]

@MattMartin 

Load both tables into Power Query and join them with the left outer join.

[#3]

Hi Detlef, thanks for the comment!

I'm afraid I'm pretty basic in my Excel use. No formal training, just self taught from forums and YouTube.

Can you simplify for the uninitiated please? :D

[#4]

@MattMartin 

I uploaded the modified file.

I am not very good in giving a detailed description.

I suggust you look for tutorials. Keywords are "left outer join", "remove columns", "load into worksheet table".

https://www.google.com/search?q=how+to+start+with+power+query

[#5]

@MattMartin 

I tabled your two ranges and used a formula:

=REDUCE(
    Header,
    UNIQUE(Requests[Requested Work TASK CODE]),
    LAMBDA(a, v,
        LET(
            filtered, FILTER(TaskData, TaskData[Task Code] = v, v),
            VSTACK(a, EXPAND(filtered, , 10, "Not Found"))
        )
    )
)

[#6]

Thanks Patrick, this works and I think its REALLY close to what I'm after!

I might be being thick here, but if I add more tasks to the requested list (lets say it goes from 7 task lines to 70, could go up to a couple of hundred) the return box still only shows the original number of lines. I can't drag the box larger, and cant see a number of rows in the formula. Maybe I am missing something obvious - How can I make the number of returns larger?

Thanks again, really helpful!