I have values in col D which I want to lookup. some values appear two
times in that col. I want to lookup data against second appearance of
values if it appear two times.
how can this be done.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=536707

data is in following form:

D E

1201 sdfasdf
1206 wersdf
0875 ghjfgjhfgh
0016 werwer
0875 zxcvcv
0775 rthr
1201 rtre

data in col D is not sorted and I cannot sort it for some reasons.
0875 appears two times and I want to Lookup against its second
appearance that is zxcvcv. same is the case with 1201 and so on. I have
many values in col D which appear two times and I want to take value of
col E against second appearance of any value in col D and if col D
contains any value only one time then I want values against it with the
same formula.

thanks heaps in anticipation of quicker reply...


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=536707

Hi Starguy,

Check out Chip Pearson's Arbitrary Lookup where you can specify which
occurrence to get

http://cpearson.com/excel/lookups.htm

Ken Johnson

Assuming you have 0875 in A1

=INDEX(E$1:E$7,MAX(ROW(E$1:E$7)*(D$1:D$7=$A$1),1), 1)
(Array formula: validate with Ctrl+Shift+Enter)

will return the LAST matching result from col E

HTH
--
AP

"starguy" a écrit
dans le message de
...

data is in following form:

D E

1201 sdfasdf
1206 wersdf
0875 ghjfgjhfgh
0016 werwer
0875 zxcvcv
0775 rthr
1201 rtre

data in col D is not sorted and I cannot sort it for some reasons.
0875 appears two times and I want to Lookup against its second
appearance that is zxcvcv. same is the case with 1201 and so on. I have
many values in col D which appear two times and I want to take value of
col E against second appearance of any value in col D and if col D
contains any value only one time then I want values against it with the
same formula.

thanks heaps in anticipation of quicker reply...


--
starguy
------------------------------------------------------------------------
starguy's Profile:
http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=536707