# Index value with column location determined by aggregate(15,6

1. ## Index value with column location determined by aggregate(15,6

Hello,

I have a table with that populates per score per company

A1:E1 46, 46, 59, 63, #DIV!/0
A2:E2 Company 1,Company 2, Company 3, Company 4, BLANK

I wanted to use INDEX(\$A\$2:\$E\$2,1,COLUMN(AGGREGATE(15,6,\$A\$1:\$E\$1,1))) to ignore errors and find the smallest value, return the respective company name.
But it does not seem to catch the column number using this formula.

I want to display this on a separate table where I would call out top 2 and bottom two scorers so I need to make sure it will still work if two companies share the same score.

What am I doing wrong?

2. ## Re: Index value with column location determined by aggregate(15,6

for sake of illustration -- using the above references / values as source

Formula:
`Please Login or Register  to view this content.`

3. ## Re: Index value with column location determined by aggregate(15,6

I had to transpose the table for other reasons so would this work?

=IFERROR(INDEX(\$A\$1:\$A,MOD(AGGREGATE(15,6,(ROUND(\$B\$1:\$B\$,4)*10^5+ROW(\$B\$1:\$B\$5)),COLUMN(H\$2:H2)),100)),"")

also there are other values in this sheet so I don't know if I can select the whole column A:A.

4. ## Re: Index value with column location determined by aggregate(15,6

The INDEX can reference the entire column, the MOD(AGGREGATE is determining the ROW number, and is limited to the relevant rows.

So, it should be:

=IFERROR(INDEX(\$A:\$A,MOD(AGGREGATE(15,6,(ROUND(\$B\$1:\$B\$5,4)*10^5+ROW(\$B\$1:\$B\$5)),COLUMNS(\$H2:H2)),100)),"")

the piece in red I am unclear on -- if you are now looking to return smallest in H2, and 2nd smallest in I2 then use

COLUMNS(\$H2:H2)

if, conversely, you're still looking to return smallest in H2 and 2nd smallest in H3 then persist with

ROWS(H\$2:H2)

this is just being used to determine "k" (from 1 to n) for use with the AGGREGATE SMALL/LARGE - and should increment by 1 as you drag the formula (to return next value).

note: in both cases the use of COLUMNS/ROWS rather than COLUMN/ROW.

5. ## Re: Index value with column location determined by aggregate(15,6

Seems that is referencing to the wrong cell when I indexed the whole column. For your reference, I have attached the file I'm working on in the post. The cells in question are marked in red from row 107 onwards.

6. ## Re: Index value with column location determined by aggregate(15,6

having reviewed your file -- given you're returning the associated value in the adjacent column (something we were not aware of) you can simplify:

Formula:
`Please Login or Register  to view this content.`

7. ## Re: Index value with column location determined by aggregate(15,6

Thanks! This works perfectly!

There are currently 1 users browsing this thread. (0 members and 1 guests)