VLookup none unique data

[josephliverpool]

Hi

I have a list of reference numbers and need to display all data which relates to this reference. The data set set looks like this

Sheet 1

References:
1
2
3
4
5

Sheet 2

References: Name:
1
1
2
2
2
3
3
3
3
4
4

so I need to link all of the references in sheet 1 and able to display the corresponding data in sheet 2,column 2.

i have only ever done a VLOOKUP against a unique field rather than fields with duplicating data.

[Bernie Deitrick]

I have assumed that your values as shown start in cell A1 of each sheet.

Try an array formula like, entered using Ctrl-Shift-Enter into cell B2 of Sheet1, then copy it to the right until it returns blanks. Then copy B2:??2 down to match your list.

=IFERROR(INDEX(Sheet2!$B:$B,SMALL(IF(Sheet2!$A$1:$A$1000=$A2,ROW($A$1:$A$1000)),COLUMN(A1))),"")

[josephliverpool]

Perfect, I owe you one, thanks!