I have an excel sheet that looks like this:

65023 John

64101 Alex

65023 RobertI need to find a function or VBA language for a macro to have all of the values for one column to return. Vlookup will only give me John for 65023 and I need both names to return. How can I do this?

Look at Response #7 of this thread. The first part explains how to add a column that will "number" each value in the first column to make it unique so you'll get:

A B C 1 65023 1 65023 John 2 64101 1 64101 Alex 3 65023 2 65023 RobertYou can then construct your VLOOKUP to find the unique values in Column A.

I hope that helps.

I'm not sure if that will work for what i am trying to do. Basically I have 700 item numbers and multiple customers own some items and I am trying to find out who those customers are for a list of items. I have a seperate tab that has 70 item numbers and the vlookup is referring to tab one to return the names of the people who own the items. I need to know all the names attached to my item number but vlookup stops the first time it comes to my item number. How do I get it to keep going? Basically on Tab 2 I have:

0097

0087

3456

4533

and on Tab 1 I have every single item number with the owner name next to it. I have 3 people who have 0087 but vlookup will only return the first person.

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 personThat's why you need to create a unique entry for each item number.

I started with your original example of:

A B 1 65023 John 2 64101 Alex 3 65023 RobertI inserted a new column A, entered this in A1 and dragged it down to A3:

=B1&" "&COUNTIF($B$1:$B1,B1)

That gave me this:

A B C 1 65023 1 65023 John 2 64101 1 64101 Alex 3 65023 2 65023 RobertYou'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:

A 1 65023 2 64101In 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/AThe #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.

=IF(ISNA

(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.

Thanks, ha no, I apparently can't read today and read my result incorrectly when following the directions. Thanks, I think this should work. :)

Let us know if you need any more help.

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History