Match and show the data from 2 sheets

[Nattarinee]

Hi

I have a lot of data in 2 sheets
Sheet1 having Name, Surname, and Ref No.
Sheet2 having Ref No., Group and data

I would like to match the Ref No. with both sheets and take the data from sheet2 shown in sheet1

Eg as in the picture
> Sheet1 Column C (64) match to Sheet2 I14 and I15
> Take the data show to Sheet1 (D3,E3) and (F3,G3)

The Ref No. possible to have a multiple of details in Sheet2 and also possible to have nothing match (In this case, I would like to show N/A)

PS As in the pictures, just sample data and I'm using Excel v2016

Could you please help ? Thank you!

 

[Skybluekid]

In your actual data, how many times would a number repeat?

 

[kvsrinivasamurthy]

IN D3 copied to full range.

=IFERROR(INDEX(Sheet2!$A$11:$B$16,AGGREGATE(15,6,ROW(Sheet2!$C$11:$C$16)/(Sheet2!$C$11:$U$16=$C3),1+INT((COLUMNS($D3:D3)-1)/2))-ROW(Sheet2!$A$10),1+MOD(COLUMNS($D3:D3)-1,2)),"")

 

[kvsrinivasamurthy]

Better formula.

=IFERROR(IF($C3="","",INDEX(Sheet2!$A$11:$B$16,AGGREGATE(15,6,ROW(Sheet2!$C$11:$C$16)/(Sheet2!$C$11:$U$16=$C3),1+INT((COLUMNS($D3:D3)-1)/2))-ROW(Sheet2!$A$10),1+MOD(COLUMNS($D3:D3)-1,2))),"")