SMALL VLOOKUP challenge!

[emerycloth]

Hi all


Basically my issue is thus:


A table exists in my spreadsheet which contains numerical values against names.

I want to be able to highlight those names with the lowest numerical values, ie the bottom 5, and display this in another table.

I've figured out how to look up the bottom 5 by using the SMALL formula, and I've created a VLOOKUP table to get the appropriate name against the appropriate numerical value.


The problem arises when there are two names that have the same numerical value eg 0. The SMALL formula correctly picks up that 0 is the lowest number and the VLOOKUP correctly picks up the right name, but when the SMALL formula looks for the second lowest number, it again finds 0 and matches it to the same name as before (rather than the 'second' 0 and it's corresponding name).


I need some sort of way for the formula to be clever enough to realise that the first 0 value is 'Name 1', say, and the second 0 value is 'Name 2' etc.


Is this possible, and if so what do I need to do?


I've put the tables and formulas into the link below so you can see what I mean.


https://www.dropbox.com/s/rowpc7h1xd4kd81/Excel%20Issue.xlsx


I'd be really grateful for your advice and help, if possible, as I am clearly pushing the boundaries of my expertise here!!


Thanks in advance!!

 

[Jordan]

There are several ways to fix this problem, but my favorite, by far, is to introduce a little bit of error into the mix. In this updated version, I've added + ROW()/1000000 to each % green value. The ROW() formula grabs the current row and dividing it by 1000000 ensures that small, negligible amount of error is added to the value. As you might imagine, this adds a very, very small amount to each value - but because it's based on the current row, the value it adds always makes the number unique - which is what we want for the SMALL() formula.


Additionally, I made the #DIV/0 error an arbitrarily large number so that SMALL wouldn't pick it up...


https://docs.google.com/file/d/0B1OBNnu3ZbL0aHU2eXRnTDE2WG8/edit