Identify values and then display the content of the cell.

[NeoGen]

I am trying to set up a questionnaire that self populates the results. I have the answers given to 9 questions of 7 sections in a table (table is 9x7) Seen below:

Each column is then summed, and values can range from 0-70. I then have another 3x3 table that identifies what rank the summed column is, from highest to lowest, and enters the value of that column. For example, if column 1 =70 then this would be the first highest number and entered into the first box of the 9x9 and then this goes down to the lowest. Shown below:

What I don't know what to do, is get excel to enter the column title in to another grid based on the values in the above table, I want to enter them in to the following table:

What I want to do, is once I identify which column has the highest number, is to display the title of that column (which is text) so the column headers are (CO, SH, PL, ME, IMP, TW, RI, CF AND SP). The final part of this is, that each heading is then displayed to the person completing the questionnaire.

I hope this makes sense?

Many thanks in advance

Nick

[#2 · Accepted solution]

@NeoGen 

If your data including the headings are in cells B1:J8. The totals in row 9 then this function should work. See also the attached example file.

=INDEX(SORTBY(B1:J1,B9:J9,-1),SEQUENCE(3,3))

A little more modern with the new WRAPROWS() function:

=WRAPROWS(SORTBY(B1:J1,B9:J9,-1),3)

[#3]

@dscheikey Many thanks for this, this worked almost perfectly. One question, is it possible to say, that if all these cells are zero, then nothing is shown in the 

my three highest scoring team roles:
my three middle scoring team roles:
my three lowest scoring team roles:

cells, I know this may cause some issues as some cells may be zero which will need to be shown, but only in the beginning will all cells be zero and so the form should not show any results.

Not sure if I explained that very well, please do ask questions if not. Again, thank you so much for this it does work amazingly and worse case scenario I can live with data being in the 9 boxes initially.

Best wishes

Nick

[#4]

@NeoGen 

Please have a look at the attached file. I have included the solutions there.

[#5]

@dscheikey 

I had a look at this, and I don't think I explained it well, what I am looking for is if this grid shows all 0 (zero)

Then this table would be blank

The complicated bit is, that it should only be blank if all the rows and columns are blank and every other time show the results.

Hope that helps explain it a bit better.

Many thanks in advance for your help

Best wishes

Nick

[#6]

@NeoGen 

If you set an IFERROR() around it, the #CALC error is also gone!

[#7]

@dscheikey 

Many thanks for your quick response, as a novice to this, where would I put the IFERROR()?

Best wishes

Nick

[#8]

@NeoGen

See attached document.

[#9]

Thank you @dscheikey,

Is it possible to do the same with either cells F16:H18 or B16:D18 which are the cells I would like blank if all the cells in B2:J8 are zero?

Many thanks in advance

Nick

[#10]

@NeoGen 

J16 is the alternative to B16 and N16 is the alternative to F16.

I just left the old calculations and created the alternatives with IFERROR() next to them. I thought you would recognise that.

[#11]

@dscheikey, I completely missed that, sorry 😬

I have copied that across, and it is perfect, thank for all your patience and help.

Best wishes

Nick