Work out 1st, 2nd, 3rd etc from a row of values

[wayneknox01]

I have a row of values in D3:K3 want to work out which is ranked 1st, 2nd, 3rd etc?

I can not work out how to use RANK as I am putting 1st place in L3, 2nd place in M3, 3rd place in N3. I have looked into Array formula's but just can't quite make it work.

[martindwilson]

why cant you use rank? you are not very clear or simply use small() or large()?

[wayneknox01]

Using RANK will simply rank the particular cell used in the formula from the row of values. However I do not want to select a particular cell to RANK I just want to find out the highest value from the row, then the next highest. Maybe there is another way to use RANK that I do not know of?

[martindwilson]

use large
=large(D3:K3,1),=large(D3:K3,2),large(D3:K3,3)

[wayneknox01]

Thanks perfect.

Next stage of the problem is once I have the ranked values I want to find out which 'brand' they correspond to - these are in cells D2:K2. Instead of just showing the value worked out from the formula above I need to lookup which 'brand' that is.

Picture1.jpg

[martindwilson]

ok before we go on you need to say what happens if rank/large are =

[wayneknox01]

Hi Martin,
Thanks for helping again - not sure what you mean in your last post? What further info do you need from me?

[martindwilson]

try like this but i needed a helper sheet

[wayneknox01]

Ok appreciate your work - this look brilliant.

Can I ask: the A1 in =IF(COLUMN(A1) - does that need to be fixed or open like you have done? If I drag across and down A1 will soon become A3 and will start looking at other values?

Thanks.

[martindwilson]

column(a1) just returns a number ie 1, column(b1)=2 but so does column(b12) or column(b2000).... so dragged across it just gets bigger by 1 dragging down has no effect