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

See Mr Excel and excelisfun do a VLOOKUP for an approximate match when the table is not sorted. See Mr Excel create the exact match for his lookup with the MIN, IF, MATCH and INDEX functions. See excelisfun use the SMALL, ROWS and LOOKUP functions to sort the table inside the formula and to an approximate match.
PodCast 1144 Learn Mr Excel
Amazing Excel Trick.

Category:

Science & Technology

Tags:

• Excel
• VLOOKUP
• Approximate
• Match
• Unsorted
• Table
• excelisfun
• Bill
• Mr
• Jelen
• Mike
• Gel
• Girvin
• Highline
• Community
• College
• SMALL
• ROWS
• LOOKUP
• MIN
• IF
• INDEX
• MATCH
• function

[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]

Great question, By The Way!!

[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]

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?

[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]

Thanks!