vlookup formula pulling duplicates until it comes across start in columb

[liz5818]

I'm trying to create a sheet that generates a report based off of a date typed into a cell. i'm using a vlookup formula which works great except for it duplicates the first match until it scrolls down enough to get to the spot in the data where the matches are located.

=VLOOKUP($B$2,'Sheet1'!C29:L$500,2,FALSE)

b2 is the cell which the date is typed, c29:l500 is the area which i want the data to pull from.

so what it's doing is say the date 2/2/13 is first listed in c50, it will pull that first match and list it 21 times until it gets to the match in c51. how do i fix this??

[daffodil11]

It sounds like you want the last match, as opposed to the first.

In this case, you can use old school LOOKUP(lookup variable, lookup vector, return vector)

We use some fanciness, and come up with:

=LOOKUP(2,1/($B$2='Sheet1'!C29:C$500),'Sheet1'!D29:D$500)

[liz5818]

well, i want all the matches but i don't need 30 duplicates of the 1st match. so if there are 20 matches for 2/2/13, i want them to list, which it does after it duplicates all the way until that spot in the original data.

right now that formula will find 2/2/13 and then list a time, which for each of the 20 matches will be different.

[daffodil11]

So you need the Nth vlookup?

[liz5818]

yea. i don't want to mess with the array because the user won't know to change it. so i need the first vlookup match to be what's in d50 and the second to be in d51 and so on by vlookup starting c29.. make sense?

[liz5818]

heres an example. put in 1/2/13 and see what i mean.

[ChemistB]

Here's how I would do it.
In G3 of sheet1 copied down

=C3&"_"&COUNTIF($C$3:C3,C3)
You can hide this column after you create it. This is a key column giving unique ID's to each entry

In sheet2, in C3 (copied across and down)
=IFERROR(INDEX(Sheet1!D$3:D$49,MATCH(Sheet2!$B$2&"_"&ROWS($A$1:$A1),Sheet1!$G$3:$G$49,0)),"")
See attachment.
Does that work for you?

[liz5818]

yes that could work. thanks ChemistB

[ChemistB]

No problem. Thanks for the Rep.

[sktneer]

Another way if you don't want to use the helper column, this regular formula will return all the records that matches with the date in B2...

Please Login or Register  to view this content.

and then drag down.
See if you get the desired output with the above formula.

[liz5818]

that worked excellent sktneer. Thanks!

[sktneer]

Glad, I could help. Thanks for the feedback.