Looking up & Matching Repeated Values...

Look at the following table, how will you find the Lowest two values and the corresponding Person & Region? The Question had been less trickier if the 5% value had not been repeated twice. In this post i will show you how to retrieve the values that have duplicate appearance in the tables.

Person Region Score
Engineer 2         South 5%
Engineer 3         North 5%
Engineer 8         South 15%
Engineer 1         North 20%
Engineer 4         South 25%
Engineer 5         North 30%
Engineer 10 South 35%
Engineer 7         North 40%
Engineer 6         South 50%
Engineer 9         North 65%

Using the following formula does the task very well. Lets take it up and split to understand how does it works!

=INDEX($A$3:$A$12,MATCH(SMALL(IF($B$3:$B$12=$F$2,($D$3:$D$12)+ROW($D$3:$D$12)*0.0000001),ROW(A1)),($D$3:$D$12)+ROW($D$3:$D$12)*0.0000001,0),0)

=VLOOKUP(F4,$A$3:$D$12,3,FALSE)

Today we will discuss a technique that is useful in getting smallest of values while testing for certain conditions. The usually used formula for getting nth smalles value is SMALL() that works fine for simple tables where we are just interested in the number itself. But when it comes to find the nth smallest number and then finding something against that number then it becomes a challange because there could be multiple enteries of a same value. 

Lets me explain it with this table: 

If i try to found the first three smallest values for the above table we can safely conclude them to be 5%, 5% & 15%. But when we try to fetch the corresponding "Person" against these values using MATCH() function, the result is "Engineer2", "Engineer2" and "Engineer8". So why this. This is because MATCH() always looks for the first match that in encounter swhile looking for a values. so it does not distinguishes between 5% of Engineer2 & 5% of Engineer3!!

The solution to this problem is to make each of these 5% different from each other so that when MATCH() goes for a lookup, it differentiate between the First & the Second 5% respectively. In order to make them different we use following formula. 

The IF() in the above formula IF($B$3:$B$12=$F$2, ($D$3:$D$12)+ROW($D$3:$D$12)*0.0000001) checks for every value in B3:B12 for whether it is "South" or not, if it is "South", the second argument of the function will pass a value that will have added value of ROW()*0.0000001. This results in addition of 0.0000002, 0.0000003, 0.0000004,...., 0.0000010, 0.0000011, 0.0000012 to the each of the %age values thus two 5s are now differentiate as they are 0.0500002 & 0.0500003 respectively, the logic is applied to the entire range. Note that not all the values in the resulting array will be numbers,  only those that are upto the creiteria will be values, rest will be FALSE.

The SMALL() function examines the resulting values [SMALL(0.0500002,0.0500003,0.1500004,0.2000005,...,0.6500011,0.0000012)]
and picks up the first smallest value. 

Now this value needs to be matched with an array of similarly generated values so that we can find its location. the same formula is feed to MATCH()'s second argument so that it can lookup for the values. Once these values are found, INDEX() finds out the corresponding value from Column A to reach the solution. 

Now this values can be VLOOKUP()-ed in the table to get the corresponding values-a thing that has other wise been impossible. 

I hope that you will enjoy the post...am waiting for feedback