VLookup - Multiple Return Values

[ragnarok83]

Hi, i've looked around the web for hours and hours and have as yet not found a solution that fits my requirements!

When using VLookup, obviously it will return the first value it finds in the lookup range, however when I have duplicate rows, I require the various values to be returned, not just the first one repeated.

Example Data:

Sheet 1.

A B C
1 Liverpool
2 Liverpool
3 Liverpool
4 Aldershot
5 Aldershot

Sheet 2.

A B C
1 Liverpool 123456
2 Liverpool 234567
3 Liverpool 345678
4 Aldershot 456789
5 Aldershot 567890

On Sheet 1 I need column B to return the values from column B on Sheet 2 without repeating the first value found...

Hope this makes sense and any help would be much appreciated.

 

[sanrv1f]

Hello and welcome to the board,

see if these hep,

http://www.mrexcel.com/forum/showthread.php?t=394260

http://www.mrexcel.com/forum/showthread.php?t=395912

 

[ragnarok83]

Thanks for you quick response, although these solutions seem to be loong at additional columns to identify return values, I simply need to look up one value in column A on sheet 1 and look up against column A on sheet 2 and return the various values from column B on sheet 2 and copy the formula down. Therefore I think this is a simpler (!) requirement...

 

[sanrv1f]

ok, here is a simpler solution

Excel Workbook
	A	B
1	Liverpool	123456
2	Liverpool	234567
3	Liverpool	345678
4	Aldershot	456789
5	Aldershot	567890
Sheet14

Excel Workbook
	A	B
1	Liverpool	123456
2	Liverpool	234567
3	Liverpool	345678
4	Aldershot	123456
5	Aldershot	234567
Sheet15

#VALUE!

 

[Patience]

I am supposing this isn't a prediction for next season's football league, is it? I know Aldershot are on the up, but really...!

Anyway - I use Zack Baresse's UDF:

http://www.mrexcel.com/forum/showthread.php?t=112275

 

[ragnarok83]

Excellent, that seems to have done the trick!

Thanks very much for your help Sankar!!