Offset/Match when there is more than 1 result

  • Thread starter Thread starter kpotg24
  • Start date Start date
K

kpotg24

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!
 
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$1000=$F$1,
ROW($A$1:$A$1000)),ROW(A1))))

If this post helps click Yes
 
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---
 
Back
Top