XLOOKUP/ Adding in Duplicates

Copper Contributor

I have two spreadsheets I am trying to combine. Spreadsheet A has Procedural data and Spreadsheet B has Follow Up data. I need to move the follow up data from Spreadsheet B onto Spreadsheet A and match using patient identifiers. I am able to do this using XLOOKUP. Here is the problem: There are duplicate patient identifiers on Spreadsheet B (for example if someone was seen at 1 month and 3 months there is another row of data with the same identifier). Each patient ID is only listed once on Spreadsheet A, so when I use my XLOOKUP formula it will return the first row of data from Spreadsheet B but not the second. (1) Is there a way it can automatically add rows in?


Some patients from Spreadsheet A are not on Spreadsheet B at all, those I was able to filter out so I am only looking at patients on Spreadsheet A who also appear on Spreadsheet B. 

If I manually add in the row(s) and enter the duplicate ID number(s), then apply my formula, it will still only return the first row of data from spreadsheet B, not the second set of follow up data on that patient. 


I did manually add in rows and simply copy/paste for the first 1K patients, however, there is over 350K rows of data and if it can be avoided I would appreciate any other suggestions. 


EX:    Spreadsheet A           Spreadsheet B

         101                            101           Day 200

         102                            101           Day 450

         103                            103           Day 125

                                           103           Day 275

                                           103           Day 500


1 Reply


An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.


Does this return the intended result?