Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Hi
I have a sheet with codes which have several components against each code and on another sheet i have a vlookup formula this just gives me first component i would like to add something to vlookup to find the 2nd or 3rd time code appears and its component my formula so far is =if(countif(sheet1!c1:c2,a1)0,vlookup(a1,sheet1!c 1:c2,2,false)," ") this would return baa0001 from following table on sheet1 on next row i would like to put countif 1 and return caa0001 but do not now how sheet1 A B code component 925001 baa0001 925001 caa0001 925001 daa0001 925002 baa0002 925002 caa0002 sheet2 a1 = 925001 hope this makes sense Thanks Tina |
#2
![]() |
|||
|
|||
![]()
You might want to consider the VLookups function from the freely
downloadable file at http:/home.pacbell.net/beban Alan Beban tina wrote: Hi I have a sheet with codes which have several components against each code and on another sheet i have a vlookup formula this just gives me first component i would like to add something to vlookup to find the 2nd or 3rd time code appears and its component my formula so far is =if(countif(sheet1!c1:c2,a1)0,vlookup(a1,sheet1!c 1:c2,2,false)," ") this would return baa0001 from following table on sheet1 on next row i would like to put countif 1 and return caa0001 but do not now how sheet1 A B code component 925001 baa0001 925001 caa0001 925001 daa0001 925002 baa0002 925002 caa0002 sheet2 a1 = 925001 hope this makes sense Thanks Tina |
#3
![]() |
|||
|
|||
![]()
Try something like:
=INDEX(Sheet1!B1:B10,SMALL(IF(Sheet1!A1:A10=A1,ROW (Sheet1!A1:A10)-MIN(ROW(Sheet1!A1:A10))+1),X)) Array-entered, meaning press ctrl+shift+enter. Change "X" to the nth occurence you want. For example, 2nd occurrence would be 2. HTH Jason Atlanta, GA "tina" wrote: Hi I have a sheet with codes which have several components against each code and on another sheet i have a vlookup formula this just gives me first component i would like to add something to vlookup to find the 2nd or 3rd time code appears and its component my formula so far is =if(countif(sheet1!c1:c2,a1)0,vlookup(a1,sheet1!c 1:c2,2,false)," ") this would return baa0001 from following table on sheet1 on next row i would like to put countif 1 and return caa0001 but do not now how sheet1 A B code component 925001 baa0001 925001 caa0001 925001 daa0001 925002 baa0002 925002 caa0002 sheet2 a1 = 925001 hope this makes sense Thanks Tina |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
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 |