|re: "I'm not sure if that will work for what i am trying to do"|
What? You don't trust me? ;-)
re: but vlookup will only return the first person
That's why you need to create a unique entry for each item number.
I started with your original example of:
1 65023 John
2 64101 Alex
3 65023 Robert
I inserted a new column A, entered this in A1 and dragged it down to A3:
That gave me this:
A B C
1 65023 1 65023 John
2 64101 1 64101 Alex
3 65023 2 65023 Robert
You'll notice that each Item Number now has a number appended to it, making it unique so that VLOOKUP can find it.
On Sheet2 I set up this table:
In Sheet2!B1 I put this formula:
=VLOOKUP(INDIRECT("A"&ROW()) & " " &COLUMN()-1,Sheet1!$A$1:$C$3,3,0)
I dragged it down and across to C2.
This formula will use the Row and Column functions to append a number to each Item Number before it looks it up.
Therefore it will lookup 65023 1, not 65023. As you drag it across it will lookup 65023 2 and so on.
The table now looks like this:
A B C
1 65023 John Robert
2 64101 Alex #N/A
The #N/A is there because there is no 64101 2.
To eliminate the #N/A, I changed the formula to check for the #N/A error and return "" if a value isn't found.
(VLOOKUP(INDIRECT("A"&ROW()) & " " &COLUMN()-1,Sheet1!$A$1:$C$3,3,0)),"",
VLOOKUP(INDIRECT("A"&ROW()) & " " &COLUMN()-1,Sheet1!$A$1:$C$3,3,0))
This is exactly the same solution as I posted in the other thread, except that I changed the ranges so that it works in Sheet2 to pull data from Sheet1.
You just need to make sure you drag to formula far enough across each Row to make sure you grab all names associated with each item.
If you read the rest of that thread you'll also see a VBA solution that should work for you also. It too will need to be modified to match your ranges.