# Excel - VLOOKUP DUPLICATE VALUES

Asked By R on 17-Feb-09 08:15 AM
I am looking for a way to look up duplicate values in a table array. When I
use VLOOKUP it continues to give me the same value (see D3 and D4).This is
the formula I used in
D1:D40...=VLOOKUP(LARGE(\$A\$1:\$A\$40,1),\$A\$1:\$B\$40,2,FALSE)
with the LARGE 'K' value incrementing. I would have liked D4 to come up with
FG04.

A        B               Formulas in C Column       C               D
1    32      FG01          =LARGE(\$A\$1:\$A\$40,1)     41           FG03
2    22      FG02          =LARGE(\$A\$1:\$A\$40,2)     32           FG01
3    41      FG03          =LARGE(\$A\$1:\$A\$40,3)     22           FG02
4    22      FG04          =LARGE(\$A\$1:\$A\$40,4)     22           FG02
5    12      FG05          =LARGE(\$A\$1:\$A\$40,5)     12           FG05

demechani replied on 17-Feb-09 08:33 AM
Source data as posted in cols A & B, from row1 down
In C1: =IF(A1="","",A1-ROW()/10^10)
In D1: =LARGE(C:C,ROWS(\$1:1))
In E1: =INDEX(B:B,MATCH(LARGE(C:C,ROWS(\$1:1)),C:C,0))
Copy C1:E1 down to E5. Minimize col C. Cols D & E returns the required
auto-descending sort of cols A & B, by the values in col A, with ties
accounted for.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
demechani replied on 17-Feb-09 08:40 AM
In C1: =IF(A1="","",A1-ROW()/10^10)
In D1: =INDEX(A:A,MATCH(LARGE(\$C:\$C,ROWS(\$1:1)),\$C:\$C,0))
Copy D1 to E1. Select C1:E1, copy down to E5. Minimize col C. Cols D & E
returns the required auto-descending sort of cols A & B, by the values in col
A, with ties accounted for.
--
Max
Singapore
http://savefile.com/projects/236895