Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP DUPLICATE VALUES
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP DUPLICATE VALUES
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 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Rj" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP DUPLICATE VALUES
Corrections, it should have read:
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 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP DUPLICATE VALUES
Try this:
In C1: =RANK(A1,$A$1:$A$5)+COUNTIF($A$1:A1,A1)-1 copy down In D1: =INDEX($B$1:$B$5,MATCH(ROW(A1),$C$1:$C$5,0)) copy down "Rj" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup function returns duplicate values | Excel Discussion (Misc queries) | |||
Vlookup for multiple duplicate numerical values | Excel Worksheet Functions | |||
Duplicate values | Excel Discussion (Misc queries) | |||
problems with displaying "duplicate vlookup values" in same column | Excel Discussion (Misc queries) | |||
DUPLICATE VALUES | Excel Worksheet Functions |