Offset/Match when there is more than 1 result
 

I am working on a spreadsheet where I have a list of names and I need to
return the corresponding dates to the names. There are instances where I
will have the same name multiple times with different dates. I have no
problem getting the information when the name is only shown once, is there an
easy way to retrieve this information when there are multiple results?
Example below:

A B C D
Susan Miller NO YES 11/01/09
Susan Miller NO YES 04/09/05
Todd Smith NO YES 10/01/09

I only need the name and the dates. Thanks!

Offset/Match when there is more than 1 result
 

Are the names grouped together as is shown in your sample data?

--
Biff
Microsoft Excel MVP


"kpotg24" wrote in message
...
I am working on a spreadsheet where I have a list of names and I need to
return the corresponding dates to the names. There are instances where I
will have the same name multiple times with different dates. I have no
problem getting the information when the name is only shown once, is there
an
easy way to retrieve this information when there are multiple results?
Example below:

A B C D
Susan Miller NO YES 11/01/09
Susan Miller NO YES 04/09/05
Todd Smith NO YES 10/01/09

I only need the name and the dates. Thanks!

Offset/Match when there is more than 1 result
 

With the query name incell F1; try the below formula and copy down. Please
note that this is an array formula. You create array formulas in the same way
that you create other formulas, except you press CTRL+SHIFT+ENTER to enter
the formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"

=IF(COUNTIF(Sheet1!$A$1:$A$1000,$F$1)<ROW(A1),"",
INDEX(Sheet1!D$1:D$1000,SMALL(IF(Sheet1!$A$1:$A$10 00=$F$1,
ROW($A$1:$A$1000)),ROW(A1))))

If this post helps click Yes
---------------
Jacob Skaria


"kpotg24" wrote:

I am working on a spreadsheet where I have a list of names and I need to
return the corresponding dates to the names. There are instances where I
will have the same name multiple times with different dates. I have no
problem getting the information when the name is only shown once, is there an
easy way to retrieve this information when there are multiple results?
Example below:

A B C D
Susan Miller NO YES 11/01/09
Susan Miller NO YES 04/09/05
Todd Smith NO YES 10/01/09

I only need the name and the dates. Thanks!

Offset/Match when there is more than 1 result
 

Something like this should work for you:
=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"")

However, I don't know exactly how to set it up because I don't really know
what results you are looking for.

Anyway, play with that and see if you can make it work.

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"T. Valko" wrote:

Are the names grouped together as is shown in your sample data?

--
Biff
Microsoft Excel MVP


"kpotg24" wrote in message
...
I am working on a spreadsheet where I have a list of names and I need to
return the corresponding dates to the names. There are instances where I
will have the same name multiple times with different dates. I have no
problem getting the information when the name is only shown once, is there
an
easy way to retrieve this information when there are multiple results?
Example below:

A B C D
Susan Miller NO YES 11/01/09
Susan Miller NO YES 04/09/05
Todd Smith NO YES 10/01/09

I only need the name and the dates. Thanks!


.