# Solved Lookup problem with same name appearing

February 13, 2014 at 10:05:24
Specs: Windows 7
 QZ4:QZ51 is a list of valuesSO4:SO51 is a list of namesQX61 = 3 (contains the formula =IF(LARGE(\$QZ\$4:\$QZ\$51,1)>0,LARGE(\$QZ\$4:\$QZ\$51,1),0)QX62 = 2 (contains the formula =IF(LARGE(\$QZ\$4:\$QZ\$51,2)>0,LARGE(\$QZ\$4:\$QZ\$51,2),0)QX63 = 2 (contains the formula =IF(LARGE(\$QZ\$4:\$QZ\$51,3)>0,LARGE(\$QZ\$4:\$QZ\$51,3),0)QZ61 contains =IF(QX61=0,"",VLOOKUP((LARGE(\$QZ\$4:\$QZ\$51,1)),\$QZ\$4:\$SO\$51,42,FALSE))QZ62 contains =IF(QX62=0,"",VLOOKUP((LARGE(\$QZ\$4:\$QZ\$51,2)),\$QZ\$4:\$SO\$51,42,FALSE))QZ63 contains =IF(QX63=0,"",VLOOKUP((LARGE(\$QZ\$4:\$QZ\$51,3)),\$QZ\$4:\$SO\$51,42,FALSE))These formulas select the name with the value so my results are as follows3 Tony Gibb2 Mark Steen2 Mark SteenThe results are as expected as per the formulaHowever, what I require is for the next name in the list with the value 2 to appear, so that the results are as follows3 Tony Gibb2 Mark Steen2 Billy WhizzOr3 Tony Gibb3 Tony Gibb3 Tony Gibbwould need to appear as3 Tony Gibb3 Mark Steen3 Billy WhizzHas any one any ideas how to get around this?

See More: Lookup problem with same name appearing

#1
February 13, 2014 at 11:15:33
 As I'm sure you are aware, LARGE will always pick up the first occurrence of the kth largest value when there is more than one occurrence of that value.Therefore, you need to "break the tie" so that each value is unique.The standard method for breaking a tie for LARGE or SMALL or RANK or any other function/formula that exhibits this problem is to add a small but differing number to each value so that there will never be a tie.For example, you could add a helper column in QY, put this in QY4 and drag it down:=QZ4+ROW()/500000For a 2 in QZ4 and QZ5, this will give you:``` QY QZ 4 2.0000010 2 5 2.0000012 2 ```Now do your VLOOKUP(LARGE(...) on QY4:QY51 and the names from both 2's will be returned since you really don't have two 2's anymore.