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

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

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