Solved Lookup problem with same name appearing

February 13, 2014 at 10:05:24
Specs: Windows 7

QZ4:QZ51 is a list of values
SO4:SO51 is a list of names

QX61 = 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 follows

3 Tony Gibb
2 Mark Steen
2 Mark Steen

The results are as expected as per the formula
However, what I require is for the next name in the list with the value 2 to appear, so that the results are as follows

3 Tony Gibb
2 Mark Steen
2 Billy Whizz

Or

3 Tony Gibb
3 Tony Gibb
3 Tony Gibb

would need to appear as

3 Tony Gibb
3 Mark Steen
3 Billy Whizz


Has any one any ideas how to get around this?


See More: Lookup problem with same name appearing

Report •

#1
February 13, 2014 at 11:15:33
✔ Best Answer
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()/500000

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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 13, 2014 at 11:42:50
Spot on, thank you very much, my sheet know looks far more professional.
Glad I have you as a source of information

Report •
Related Solutions


Ask Question