Excel Magic Trick # 273: Extract Top 5 Records w Formula

Extract top (max high) 5 records w formula where duplicates are allowed. See how to extract the top (max high) five values and associated names from a database with an array formula that can handle duplicate top values. See the functions: TRANSPOSE, COUNTIF, MAX, INDIRECT, IF, COLUMNS, INDEX, SMALL and ROW in an array formula.

Reverse lookup. INDEX function with criteria conditions.

Category:

Science & Technology

Tags:

• Excel
• extract
• top
• five
• records
• from
• database
• formula
• duplicates
• Reverse
• lookup.
• INDEX
• function
• with
• criteria
• conditions
• excelisfun
• Mike
• Gel
• Highline
• Community
• College

[chaboya353]

hi

how can i make repeated rows without changing e.g A1,B2 as these values consider as input user, as we know that making multiple rows the value change e.g the A1,B2 will be as foloows

A2,B3

A3,B4 and so on

thanks

[ExcelIsFun]

I do not know. Try THE best Excel question site:

mrexcel[dot]com/forum

[chinchonchinchon]

Just like planioro I had to watch it several times to understand this type of "mixing" rows and columns, although I didn't watch this one: I watched another very similar to this one and I tried and did finish this one by myself!

The tricky part is to understand where to use rows and columns. I was dragging to the right along the columns with the rows function, naturally it didn't work, but finally It downed on me that it had to be COLUMNS!

This is amazing!

[ExcelIsFun]

It is amazing, chinchonchinchon, how far you have come with your Excel skills in such a short time!

Yes, you are right about the confusion of when to use the COLUMNS or ROWS function. I still mix it up sometimes...

[chinchonchinchon]

@ExcelIsFun

I had to struggle for a couple of hours before finishing the formula though.

I can understand the formula, but if you ask me to come up with such a clever formula I'd be still scratching my head trying to figure it out. Aladinn is indeed a clever fellow.

[ExcelIsFun]

Yes, but if you can understand it and you know where to go and ask, that is 99% of the battle.

Plus, if you keep at it, it will be amazing how some of the new knowledge can be applied when a new problem comes up!

[ExcelIsFun]

Dear planiolro ,

Yes, I just posted it recently. Go to the Playlist and near the bottom is the new INDIRECT function playlist.

--excelisfun

[planiolro]

This is a awesome video with an amazing usage of different excel functions. I have to watch it several times in order to fully understand it. Some time ago u mention something about a play list for indirect function.