vlookup help...

I have a formula setup to look up a value in another sheet and bring back a
value. Here is my formula in cell J2
=VLOOKUP("PADRES",Scores!K:N,2,0)

It works perfectly. However, there will be multiple times Padres shows up
in the scores worksheet. How do I setup the formula so in cells J3 it skips
the first occurance of Padres and finds the next one. Then J4 finds the 3rd
occurance and so on. Thanks

Put this in J3, press ctrl + shift + enter, and fill down:

=INDEX(Scores!$L$1:$L$500,SMALL(IF(Scores!
$K$1:$K$500="Padres",ROW(INDIRECT("1:500"))),ROW()-1))

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I have a formula setup to look up a value in another
sheet and bring back a
>value. Here is my formula in cell J2
>=VLOOKUP("PADRES",Scores!K:N,2,0)
>
>It works perfectly. However, there will be multiple
times Padres shows up
>in the scores worksheet. How do I setup the formula so
in cells J3 it skips
>the first occurance of Padres and finds the next one.
Then J4 finds the 3rd
>occurance and so on. Thanks
>
>
>.
>

Jason,
that worked great. thanks. However, I would like the formula to work in
cell J3 and then not again until J8. If I copy the formula down through
J4:J8 I get the value in J4 that I want in J8 and J5 down is #NUM!. Any
ideas? Thanks

"Jason Morin" wrote:

> Put this in J3, press ctrl + shift + enter, and fill down:
>
> =INDEX(Scores!$L$1:$L$500,SMALL(IF(Scores!
> $K$1:$K$500="Padres",ROW(INDIRECT("1:500"))),ROW()-1))
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >I have a formula setup to look up a value in another
> sheet and bring back a
> >value. Here is my formula in cell J2
> >=VLOOKUP("PADRES",Scores!K:N,2,0)
> >
> >It works perfectly. However, there will be multiple
> times Padres shows up
> >in the scores worksheet. How do I setup the formula so
> in cells J3 it skips
> >the first occurance of Padres and finds the next one.
> Then J4 finds the 3rd
> >occurance and so on. Thanks
> >
> >
> >.
> >
>

What you're asking for now is a little more complicated.
The original formula works well because you can easily
drag it down. For now you can use:

=INDEX(Scores!$L$1:$L$500,SMALL(IF(Scores!
$K$1:$K$500="Padres",ROW(INDIRECT("1:500"))),X))

where X = the nth occurrence of "Padre". For example, use
2 in place of X to get the 2nd occurrence.

Don't forget - ctrl + shift + enter.

Jason

>-----Original Message-----
>Jason,
> that worked great. thanks. However, I would like the
formula to work in
>cell J3 and then not again until J8. If I copy the
formula down through
>J4:J8 I get the value in J4 that I want in J8 and J5
down is #NUM!. Any
>ideas? Thanks
>
>"Jason Morin" wrote:
>
>> Put this in J3, press ctrl + shift + enter, and fill
down:
>>
>> =INDEX(Scores!$L$1:$L$500,SMALL(IF(Scores!
>> $K$1:$K$500="Padres",ROW(INDIRECT("1:500"))),ROW()-1))
>>
>> HTH
>> Jason
>> Atlanta, GA
>>
>> >-----Original Message-----
>> >I have a formula setup to look up a value in another
>> sheet and bring back a
>> >value. Here is my formula in cell J2
>> >=VLOOKUP("PADRES",Scores!K:N,2,0)
>> >
>> >It works perfectly. However, there will be multiple
>> times Padres shows up
>> >in the scores worksheet. How do I setup the formula
so
>> in cells J3 it skips
>> >the first occurance of Padres and finds the next
one.
>> Then J4 finds the 3rd
>> >occurance and so on. Thanks
>> >
>> >
>> >.
>> >
>>
>.
>