Search using a value that can return multiple matches

1st worksheet - a1:a5 looks like this with results to end up in b1:b5 & c1:c5(imagine these as columns)
abc ??? ???
def ??? ???
ghi ??? ???
jkk ??? ???
agh ??? ???

2nd worksheet - a1:a5 and b1:b5 looks like this

abc 100
def 100
def 200
ghi 300
ghi 400

I want to be able to show on 1st worksheet that abc has found a match and that the next column Sheet2!b1 shows a value of 100 placed back up in Sheet1!b1.

Lookup fails to find more than first value and I can't figure a way to get more than one value. I apologize I am not an expert in the array or index formulas but I did look for about 2 hours to try and find my answer prior to this post.

End result on 1st worksheet to be this:
abc 100 blank
def 100 200
ghi 300 400
jkk blank blank
agh blank blank

Thank you in advance.

Hi

1) your source data is in the range sheet2!A1:B5
2) sheet1!A1:A5 contains your id data (abc, def.....)
3) sheet1!B1: =IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$5,2,FALSE)),"",VLOOKUP(A1,Sheet2!$A$1:$B$5,2,FALSE)). Copy down to B5
4) C1: Array enter (ctrl, shift enter) =IF(COUNTIF(Sheet2!$A$1:$A$5,Sheet1!A1)<2,"",INDEX(Sheet2!$B:$B,SMALL(IF(Sheet2!$A$1:$A$5=Sheet1!$A1,ROW(Sheet2!$A$1:$A$5),""),COLUMN()-1))). This can be copied down / across as required.

Adapt any ranges to suit.

HTH

rylo

Sheet1 C1 is blank and not finding the 2nd value for either duplicate of def or ghi. Any thoughts? Also, when copying into C1, Excel tells me there is an error.

B1 formula works great though.

Hi

Did you array enter the formula? Hold down the ctrl and shift buttons, then press enter.


rylo

I double checked it.

I appreciate the help.