lookup values query

[MG100]

Hi

I am using vlookup to return information.

However, the reference column has more than 1 value and yet vlookup will only return the 1 value; e.g

spreadsheet 1
column 1 colum 2
bob cat
bill dog
ben sheep
bob elephant

spreadsheet 2
when I vlookup search on bob, I want to bring back all values in column 2, i.e. cat and elephant as above. At the only moment I am only getting cat.

Any help would be much appreciated.

Many thanks

MG

 

[fairwinds]

Welcome!

Here you have 2 options. One with native formulas and one with a UDF.

http://www.mrexcel.com/board2/viewtopic.php?t=121494

 

[MG100]

Hi there

Many thanks for your reply. Regretfully, as the data is text, the 'nth' is not accepted.

In my real spreadsheet, spreadsheet 1, column A has surnames and column B has dates of birth.
The surnames in spreadsheet 2 that I am searching on doesn't like the nth suggestion.

Many thanks

Matt

 

[fairwinds]

It does not matter if it is dates or names or whatever:

Book1.xls
	A	B	C	D
1	Name	Date		John
2	Steve	2004-10-10		2004-10-11
3	John	2004-10-11		2004-10-12
4	John	2004-10-12		2004-10-13
5	John	2004-10-13		2004-10-15
6	Steve	2004-10-14		2004-10-16
7	John	2004-10-15
8	John	2004-10-16
9	Steve	2004-10-17
10	Steve	2004-10-18
Sheet2