I have two sets of data on the first set there is one column that has a list of IDs like below.

enter image description here

I have another set which could have several rows matching that same ID like below

enter image description here

I am trying to get all the 'Values' shown in the second worksheet for each ID so I end up with something like

enter image description here

How do I write the equation in the B column to do the cross matching?


There aren't any Excel functions that return a range of cells. There are two possible other ways I can think of:

  1. Write a VBA macro. You could build the first column using the Remove Duplicates feature. Then the code could loop over the range looking for matching values that match the ID and build a comma separate string.

  2. Use a Pivot Table to do that:

    1. Select the ID, Year and Value range of cells and select Pivot Table on the Insert tab.

    2. Add the ID and Value fields to the Row Labels

    3. Configure Field Settings to No Subtotals and 'Show Items labels in tabular form'

    4. Get a layout something like this. You might be able to get it to layout in a single row but I couldn't find the option for that.


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.