Return multiple matchng VLOOKUP values in one cell separated by commas?

Dear Excel Forum Community,

I am using excel to do my invoicing.
In one sheet I have all invoices ("invoices" sheet) and in another sheet I have the details of these invoices ("details" sheet). The invoices have numbers and the projects of which sometimes many make up one invoice have PO numbers.

What I am trying to do is list the PO numbers included in an invoice (information from the details sheet) in a cell next to the invoice number (on the invoices sheet), separated by commas.

So there is invoice PEPSI-JULY-2015. It contains the projects PO20058, PO24824, PO28992, PO298477, PO299338, and so on. (sometimes up to 25, 30 POs in one invoice)
In the "details" sheet I have the invoice number in a column on the left, the items and price and other information including the PO-number in their respective columns next to the invoice number on the left.
VLOOKUP will only look at the first match it finds and return it. I have found ways to look at the 2nd, 3rd and 4th match but I could not replicate it so far. But the real trick here seems to list all those matches in one cell (otherwise it would require way to much space and would clutter the whole invoice sheet of which the purpose is to get an overview, not to have to scroll around forever to see which PO-numbers are linked to an invoice number.

Maybe there is another simpler solution of tweaking my tables than using VLOOKUP or INDEX MATCH to find all matches and list them in one cell - any help is appreciated.


I will attach a generic version of my excel file. I have googled and run my brain until insanity and back to somehow make this work but only had weird or partial (only one PO) results. Mostly just errors.

Thank you very much for looking at it. It seems like a common thing to want so I think it might help others as well. And I would absolutely prefer a function to a VBS as I would like to be able to understand how it works and I have no clue about macros and other things...


(sheets other than the "invoices" and "details" sheet can be ignored - only the sheets "invoices" and "details" are required for this. There are some mistakes in the other sheets from deleting stuff.)

Kind regards

Patrick

Put this formula in P4 of the Details sheet:

=IF(InvoiceDetails[[#This Row],[Invoice '#]]="","",InvoiceDetails[[#This Row],[Invoice '#]]&"_"&COUNTIF(C$4:C4,InvoiceDetails[[#This Row],[Invoice '#]]))

and put the word Record in P3. The formula should automatically copy down, and it tags on a sequential number to the invoice number, thus creating a unique reference number for each record.

Then in F4 of the Invoices sheet you can have this formula:

=IFERROR(INDEX(InvoiceDetails[[#All],[PO]],MATCH(InvoicesMain[[#This Row],[Invoice '#]]&"_1",InvoiceDetails[[#All],[record]],0)),"")
&IFERROR(", "&INDEX(InvoiceDetails[[#All],[PO]],MATCH(InvoicesMain[[#This Row],[Invoice '#]]&"_2",InvoiceDetails[[#All],[record]],0)),"")
&IFERROR(", "&INDEX(InvoiceDetails[[#All],[PO]],MATCH(InvoicesMain[[#This Row],[Invoice '#]]&"_3",InvoiceDetails[[#All],[record]],0)),"")
&IFERROR(", "&INDEX(InvoiceDetails[[#All],[PO]],MATCH(InvoicesMain[[#This Row],[Invoice '#]]&"_4",InvoiceDetails[[#All],[record]],0)),"")

(I've manually put line-breaks in there to make it easier to follow). Again, this should copy down automatically, and will give you up to 4 PO numbers that are associated with the Invoice number. If you need more, then in edit mode on the cell you should <copy> the bottom part of the formula (shown in blue) and paste it to the end and change the sequence number (shown in red) as appropriate.

Hope this helps.

Pete

Hi guys, since no-one had answered this beforehand, I decided I'd have a go.
I kind of had success, but when I came back here I saw that Pete beat me
And since I know nothing about pivot tables, his formula kicks but on my clumsy attempt.
I thought I'd place it here anyway so that maybe someone could tell me why it doesn't quite work.

Well for a start, it is limited to 30 PO's and only 50 different invoice numbers, but that aside it totally misses PO 23513 no matter which invoice it is assigned to.

Does it have something to do with me trying to use a pivot table as an array?

Thanks for any input.

@Beamernsw

In E4 of your new sheet, you have the term COLUMN(B:B), which returns the second matching record. However, that term is COLUMN(D:D) in F4, rather than COLUMN(C:C), so it will return the 4th matching record and miss the third - perhaps you deleted a column. Just copy the formula from E4 across, then copy those formulae down.

Hope this helps.

Pete

OMG, what a noob mistake.... Thanks, I looked and looked. No way could I see that

Thanks Pete.

You usually add new data at the bottom of a file, and formulae are usually designed to be copied down a sheet. Post another example showing the problems that you have now, and I'll take a look at it tomorrow (it's 2AM here now).

Pete

Thanks Pete. In the real version of this file there are 290 or so entries so adding lines to the bottom would be very impractical as I have to scroll down every time. It is a possibility but would be nicer to be able to keep my habit of adding to the top .

It is 3:22 AM here now, I seem to not really get to this either ...

Thanks a lot - whenever you find the time to look at it.

I added a few lines to the top. As you can see the numbering doesn't update and there are duplicate invoice entries in the created column "Record" and those do not show in the POs field in the invoice sheet.


Pat

Okay, change the formula in P4 to this and copy it down:

=IF(InvoiceDetails[[#This Row],[Invoice '#]]="","",InvoiceDetails[[#This Row],[Invoice '#]]&"_"&COUNTIF(C$3:C4,InvoiceDetails[[#This Row],[Invoice '#]]))

to allow you to insert your invoices at the top of the table. When you have inserted a new row 4, you must copy the formula from P5 into P4, and then the numbering will carry on automatically. Note that the only change is in the middle of the formula - C$4 has become C$3, as shown in red.

If that now takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete