Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |