1st and last date

[kylefoley76]

I'm trying to build a formula where I have the name in column b but the same appears several time, each with a different date in column a. I want a formula which in column c will return the date where the name appears first, then column d it returns the date where the name appears last.

maybe i can just get ride of column c and d and make a formula where the data appears first then a dash, then the last date.

 

[West Man]

If the name of interest is in Ei, then for the first date try: =LOOKUP(E1,$B$1:$B$10,$A$1:A10)

For the last try: =LOOKUP(2,1/($B$1:$B$10=E1),$A$1:$A$10) Adjust ranges to suit.

 

[kylefoley76]

The second one is working, but not the first. For the first I'm getting numbers that are way off.

 

[kylefoley76]

help

 

[kylefoley76]

help

 

[jim may]

Here's an example, adapt to your needs...

Excel Workbook
	A	B	C	D
1			mary	<< Look up Who?
2	Dates	Employees	Earliest	Latest
3	1/15/2011	jim	11/20/10	03/14/11
4	2/15/2011	paul
5	3/14/2011	Mary
6	3/15/2011	paul
7	12/31/2010	jim
8	11/20/2010	Mary
9	10/31/2010	paul
10	12/31/2010	paul
11	2/15/2011	Mary
12	11/27/2010	jim
Sheet1

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[T. Valko]

If the name of interest is in Ei, then for the first date try: =LOOKUP(E1,$B$1:$B$10,$A$1:A10)

For the last try: =LOOKUP(2,1/($B$1:$B$10=E1),$A$1:$A$10) Adjust ranges to suit.

The second one is working, but not the first. For the first I'm getting numbers that are way off.

Try this for the first date...

=INDEX(A1:A10,MATCH(E1,B1:B10,0))

Format as Date