Vlookup second appearance

S

starguy

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

starguy

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...
 
A

Ardus Petus

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" <[email protected]> a écrit
dans le message de
news:[email protected]...
 
Top