Vlookup only returns 1st match!

Hi, I would really appreciate if someone helped me with vlookup limitation. I work with part numbers and more than often the same part # appears several times in the column with several different corresponding values in the column to the right of it. It seems to be a common problem that vlookup only returns the corresponding value for the 1st match only, while ignoring other matches of that same part number down the column. Is there a solution that would return all of the matches at once?

Do you want to list the values separately or sum them?

If you want to sum them use SUMIF, otherwise can you post the vLOOKUP formula?

Originally Posted by daddylonglegs

Do you want to list the values separately or sum them?

If you want to sum them use SUMIF, otherwise can you post the vLOOKUP formula?

I am not sure if any of the sumif formulas will work because I wante the values returned separately, not summed. Please see below my example.

Part # Price
P551670 5.00
P154600 3.00
P125400 4.00
P551670 5.00

As you will see, part number P551670 appears twice in this sample pricelist. So when I use Vlookup it only returns corresponding price for P551670 in the 1st row and instead of the price it shows, I think, N/A or value, for P551670 in the 4th row. I dont want them appear summed but instead shown separately.

Another example would when the same part number, oil filter in this case, can be used on by different manufacturers on various equipment and each manufacturer uses different part number for their equipment, while the all cross to P551670. When I do vlookup, it only gives me only the first cross reference.

Can you help?
Thank you!