QZ4:QZ51 is a list of values

SO4: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 follows

3 Tony Gibb

2 Mark Steen

2 Mark SteenThe 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 follows3 Tony Gibb

2 Mark Steen

2 Billy WhizzOr

3 Tony Gibb

3 Tony Gibb

3 Tony Gibbwould need to appear as

3 Tony Gibb

3 Mark Steen

3 Billy Whizz

Has any one any ideas how to get around this?

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.

Spot on, thank you very much, my sheet know looks far more professional.

Glad I have you as a source of information

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History