Hi all,

I have some data in one worksheet ( a list of text cells with fixed names, and then a list of values next to the text cells ). I need to grab the cells with the highest values and show them on another worksheet, like this :

Worksheet with data :

What I want to show on another worksheet in the same Excel document :

I initially thoughth of using a Pivot Table, however it's difficult to filter the list to only show the rows with the highest numbers - and also get rid of all of the bits that a Pivot Table adds like the headers. I just want to show the data as above and nothing else.

Here's what you need:

```
=IFERROR(INDEX(DATA,SMALL(IF(OCCURENCES=MAX(OCCURENCES),ROW(OCCURENCES)),ROW(1:1)),1),"")
```

I can confirm this works across different sheets using the named fields. This formula works without the names, you just have to use absolute sheet & cell references: something like Sheet1!$A$1:$B$10. If that gets confusing you can double click the section of the formula you are working on and then navigate to the sheet and drag your selection out. Preferably, just use names.

Your column of numbers I've called **Occurences**, and the full set of data is **Data**.

MAX(OCCURENCES) gives us the max value in the column, and what we're doing with the IF is creating an array of each cell matching that max value. That array is passed into SMALL, with the **k **value as ROW(1:1) - the "nth" row in our array. After that, we finish the INDEX with a 1 - this tells the index which column in the dataset to return from. Finally, the function is wrapped in an IFERROR handler to leave unwanted cells blank.

To make this work, enter the formula in a cell and hit **Ctrl+Shift+Enter** - that turns it into an Array Formula. You'll see the formula has changed to look like this:

```
{=IFERROR(INDEX(DATA,SMALL(IF(OCCURENCES=MAX(OCCURENCES),ROW(OCCURENCES)),ROW(1:1)),1),"")}
```

Then just drag the formula down as needed. Examining the formula in the next cell down, you'll see that ROW(1:1) is incrementing to ROW(2:2), etc. This lets the formula return each additional matching value - so you probably want to drag the formula down for as many rows as you have in your original dataset - in case every incedent has the same number of occurences.

To populate your number column on the report sheet, you could use something like this:

```
=IF(RESULTS="","",MAX(OCCURENCES))
```

That is, where your column of **RESULTS **is blank, stay blank, otherwise fill in the max value.

FYI - Here's how to name stuff if you don't already know - if you have labels along the top of the rows select all the data, go to **Create from Selection** in the **Formulas** tab, and leave **Create Names** **in**: **Top Row** checked. Or just select the column and type the name in the Name Box.

Hope this helps!

## 3 Replies

Here's what you need:

```
=IFERROR(INDEX(DATA,SMALL(IF(OCCURENCES=MAX(OCCURENCES),ROW(OCCURENCES)),ROW(1:1)),1),"")
```

I can confirm this works across different sheets using the named fields. This formula works without the names, you just have to use absolute sheet & cell references: something like Sheet1!$A$1:$B$10. If that gets confusing you can double click the section of the formula you are working on and then navigate to the sheet and drag your selection out. Preferably, just use names.

Your column of numbers I've called **Occurences**, and the full set of data is **Data**.

MAX(OCCURENCES) gives us the max value in the column, and what we're doing with the IF is creating an array of each cell matching that max value. That array is passed into SMALL, with the **k **value as ROW(1:1) - the "nth" row in our array. After that, we finish the INDEX with a 1 - this tells the index which column in the dataset to return from. Finally, the function is wrapped in an IFERROR handler to leave unwanted cells blank.

To make this work, enter the formula in a cell and hit **Ctrl+Shift+Enter** - that turns it into an Array Formula. You'll see the formula has changed to look like this:

```
{=IFERROR(INDEX(DATA,SMALL(IF(OCCURENCES=MAX(OCCURENCES),ROW(OCCURENCES)),ROW(1:1)),1),"")}
```

Then just drag the formula down as needed. Examining the formula in the next cell down, you'll see that ROW(1:1) is incrementing to ROW(2:2), etc. This lets the formula return each additional matching value - so you probably want to drag the formula down for as many rows as you have in your original dataset - in case every incedent has the same number of occurences.

To populate your number column on the report sheet, you could use something like this:

```
=IF(RESULTS="","",MAX(OCCURENCES))
```

That is, where your column of **RESULTS **is blank, stay blank, otherwise fill in the max value.

FYI - Here's how to name stuff if you don't already know - if you have labels along the top of the rows select all the data, go to **Create from Selection** in the **Formulas** tab, and leave **Create Names** **in**: **Top Row** checked. Or just select the column and type the name in the Name Box.

Hope this helps!

That's fantastic - thank you.

The formula didn't quite work, as the named range didn't start at row 1 so the data being returned was offset by the number of rows from the top that the named range started at. This was 4 rows in my case, so I just modified the formula as follows to get the right result : =IFERROR(INDEX(Data,SMALL(IF(Occurences=MAX(Occurences),ROW(Occurences)-4),ROW(1:1)),1),"")

Thanks again for the help !