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

Video duration: 10:52
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.

## 6 thoughts on “Excel Magic Trick # 273: Extract Top 5 Records w Formula”

#### ExcelIsFun

(October 20, 2018 - 7:32 pm)

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

mrexcel[dot]com/forum

#### chaboya353

(October 20, 2018 - 7:32 pm)

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

(October 20, 2018 - 7:32 pm)

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

(October 20, 2018 - 7:32 pm)

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…

#### ExcelIsFun

(October 20, 2018 - 7:32 pm)

Dear planiolro ,

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

–excelisfun

#### planiolro

(October 20, 2018 - 7:32 pm)

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.