ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   vlookup help... (https://www.excelbanter.com/excel-discussion-misc-queries/21605-vlookup-help.html)

Jambruins

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



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

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


.


Jambruins

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


.



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

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


.


.



All times are GMT +1. The time now is 12:08 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com