Mr Excel & excelisfun Trick 36: VLOOKUP w Approximate Match & Unsorted Table

[23skittlesnic]

I have a question. How does this work with random numbers? Lets say these random numbers generate, I do a sum of these. I then rank them. I cannot re-arrange the columns. I want to display the 5th highest number from the sum column (bearing in mind that its sum of random generations from =randtriangular(G$2,G$3,G$4) for example)

Thank you for amazing tutorials!

[ExcelIsFun]

I am not sure what you are trying to do. I also am not familiar with the randtriangular function. However, using the SMALL function sort trick should work. However, if you were using functions like RAND or RANDBETWEEN, they are volatile functions that change when things are re-calculates (like hitting Enter), so they may be problems in regard to that.

[23skittlesnic]

@ExcelIsFun I have a question.

LOOKUP and VLOOKUP. Lets say I generate random numbers using =randtriangular, then I sum these numbers. I then rank them. I cannot sort the columns, but I know the SMALL function can be used. I want to display the 5th highest number (rank) from the sum column.

I have tried and tried, with the result of #N /A, even when using the SMALL function, results are inconsistent and inaccurate.

Hope this is clearer! Thanks a lot!

[ExcelIsFun]

I am not sure. But no problem. THE best site to ask Excel questions is:

mrexcel[dot]com/forum

[planiolro]

The combination of small and lookup of small function takes into consideration lookup table with only 2 columns. What can we do in case there are 3 or 5 columns for instance?

[ExcelIsFun]

Change the result_vector if it is just a single occurrence. If it involves copying, I would have to know how data is setup and which direction you are copying. But I think it would be a simple matter of putting the correct cell references in the result_vector SMALL function. Something like this if you had a 3rd column:

=LOOKUP(E3,SMALL($A$1:$A$3,{1,­2,3}),SMALL(B$1:B$3,{1,2,3}))

where second SMALL has just rows locked.

[ExcelIsFun]

Great question, By The Way!!

[planiolro]

U do not realize the value of the videos until u are faced with an issue whose solution is demonstrated here so clearly. Awesome solution with lookup

[ExcelIsFun]

This is true. Even for me, when I forget how to do something I just lookup one of these videos!

Did this answer your original question?

[ExcelIsFun]

I am just a guy having fun with Excel!

[mercy2mercy]

For example, in Col A, the value 1000 is the 2nd smallest value in that col, the result value need to be the 2nd smallest in Col B, which is the value (1), in this example that is the case because no one will get higher bonus unless he sold more.

But if you changed the (1) in Col B to 30, now it became the 7th smallest in Col B not the 2nd, and will not be the amount for the level of 1000.

I hope it is clear, excuse my English!

But in other case,

[ExcelIsFun]

I think that would mean that you are just using Col B as the lookup, not column A. If I understand you correctly.

[helicart]

I agree with M2M. Small() is used to re-sort column A and B, so both columns need values in the same ascending or descending order.

Bill's solution avoids this restriction. Nevertheless, both clever solutions.

[ExcelIsFun]

Thanks!

[mercy2mercy]

Both solutions are awesome, sorting the table in memory is brilliant.

But I think for the SMALL trick to work the (n) smallest value in the first col must corresponds to the same (n) smallest value in the second col. i.e. 1000 is the 2nd smallest in Col A, its value in the result array must be the 2nd smallest as well.

Isn't that correct?

[ExcelIsFun]

I do not undersatnd what you are asking. Can you re-stae your question?