Index & Array Formula

Hi,

I am trying to use Index and Array formula to bring back multiple values by looking at a specific ID.

Sheet1- Has Two columns ( ID & Type)
Sheet 2 - Has two columns ( ID and Request ID)

Both sheets have the ID column that match, but an ID can have more than 1 "Type" and more than 1 "Request ID".

In a different sheet, I am calling it the Master sheet, I would like to bring back the "Type" & "Request ID" values that match the ID.

I have attached an example of a spreadsheet.

Test Workbook.xlsx


Thanks!

For column B

Please Login or Register  to view this content.

For column C

Please Login or Register  to view this content.

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.

Both of the answers worked. thank you so much for the prompt reply!

I have another question...

On the first sheet (sheet1) I have a list of Unique IDs and on Sheet2 I have data that shows Activities along with the IDs. I did a vlookup to see if any of the IDs on sheet1 exist on sheet2 =if(vlookup, A2, Sheet2! A:A,1,false), "Yes","No"), which gave me back Yes or no answers.

If I want to bring back the activity type on sheet1 but the trick is that on sheet1 I only have unique IDs and on sheet2 there could be multiple activities associated for 1 ID.

The first example I asked for it worked because I had multiple IDs of the same page which we associated a unique value to it that brought back the data.

But on this example i only have 1 unique ID on sheet 1 and i would like to bring back multiple values associated with it. what formula would I use to bring back multiple values for an ID without having to insert rows or duplicate IDs on sheet1?

Does it make sense?

I appreciate all your feedback.