vlookup help...

[Guest]

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 Morin]

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

 

[Guest]

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]

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