![]() |
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 . . |
All times are GMT +1. The time now is 12:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com