How to use index match small large combo function in Excel

​In this tutorial you’ll learn how to use index match small large combo function in Excel. We will use these in combination to find the top 20 and bottom 20 values. They will return the values and related data.

Chapters:

0:00 Intro
0:11 Prep
1:37 Lesson
12:22 Wrap up
13:50 Outro

FUNCTIONS:
***These are for structure reference only. Be sure to change to reflect where data is in your worksheet.***

SMALL:
=SMALL(Table1[[#All],[Sales]],H2)

LARGE:
=LARGE(Table1[[#All],[Sales]],D2)

INDEX SMALL:
=INDEX(Table1[[#All],[Fruit]],MATCH(SMALL(Table1[[#All],[Sales]],H2),Table1[[#All],[Sales]],0))

INDEX LARGE:
=INDEX(Table1[[#All],[Fruit]],MATCH(LARGE(Table1[[#All],[Sales]],D2),Table1[[#All],[Sales]],0))

SOFTWARE:

GEAR:

LET’S CONNECT:
NOTE: This description contains affiliate links, which means at no additional cost to you, I will receive a small commission if you make a purchase using the links. This helps support the channel and allows me to continue making videos like this. Thank you for your support!

#MelissaCompton #Excel #Functions

[flootsy]

Thank you, Melissa, this is exactly what I was looking for. I used it to show the top and bottom sales for the month, my manager loved it. You're the best.

[yukiekato-green]

Easy to understand! Thank you Melissa!

[chrysnotreally]

Thank you. I had an idea to use index and match and another function to count and measure how many instances of a zip code are in a set of criteria... Now I can do something with this idea (I think index match countif large). Thank you again!

[vijaysahal]

Hi teacher its amazing also ur teaching way is nice tq 👍

[jameseberts]

Hello Melissa!
This is close to what I am working on but the concept is to pull from a picking list to create a list of items selected on a different worksheet that will show up in a consecutive list on another worksheet. For example, I am selecting items for a utility trailer from a list of options. To make the selection, the customer places a number of the items they want in a cell indicating how many of that item they want. On a separate worksheet, I need to create a consecutive list showing the quantity of items selected, the total cost of the items and the description of the items in an itemized list. This is to produce a sales quote that is itemized. It would look like this...

Qty. Cost Description
1 $45.00 Spare Tire Rack
1 $350.00 1, 000 lb. Axle
...
...
and so on.

Everything works fine until the description cell. It is simply grabbing one description after another in sequence as it is listed on the options selection worksheet. This has got me confused on why it is not selecting the correct description to match the item and cost respectively.
Here is the formula I am using for each column...

Qty. - {=IFERROR(INDEX('DTF240'!$BJ$20:$BO$64, SMALL(IF('DTF240'!$BJ$20:$BJ$64<>"", ROW('DTF240'!$BJ$20:$BJ$64)-19), ROW($A1)), 1), "")}

Cost -
{=IFERROR(INDEX('DTF240'!$BK$20:$BO$64, SMALL(IF('DTF240'!$BK$20:$BK$64<>"", ROW('DTF240'!$BK$20:$BK$64)-19), ROW($A1)), 1), "")}

Description -
{=IFERROR(INDEX('DTF240'!$BO$20:$BO$64, SMALL(IF('DTF240'!$BO$20:$BO$64<>"", ROW('DTF240'!$BO$20:$BO$64)-19), ROW($A1)), 1), "")}

Please help with what I am missing. Thank you!

[jaisan]

Thank you. How to do it the same if the sales available in month wise.

[colinhuntley]

thanks for the great tutorial, melissa! You must be inundated with questions which you do not have time answer, but i will ask anyway if you could put me on the right track: on the worksheet which i am working on i have 3 exact same highest results, and match only shows me one. for any tips i would be really grateful. regards from germany, colin

[ladyboss]

Hello how will I index a data (date but different year) from the top 5 of all data range (per date per year)

[ubaidillahmuhammad]

amazing. please give the excel file in the description video ...

[rupamtagore]

If more than 1 fruit has the same sales value, how you going to deal with that?