Using match and rank together

[Alpacino]

Hi there,

I have got a spreadsheet as below

Names apple banana pears
Alan. 3. 5. 4
Sam. 6. 4. 2
Jeff. 2. 4. 3

This is very small example I have columns going across to bb
And rows down to 123

On another worksheet I have
name. Sam
Apples. Formula in here result(1st)
Bananas. (2nd)
Pears. (3rd)
I like to to rank the numbers in each column
But I like to use match function to match the column heading
E.g Sam rank for apples will result in 1st because he has the highest number (6)

Any ideas perhaps using rank(

 

[Andrew Poulsom]

If your first table in in A1:D4 on Sheet1 and you second table is in A1::B4 on another sheet, on that sheet in b2 enter:

=RANK(INDEX(Sheet1!$A$1:$D$4,MATCH(B$1,Sheet1!$A$1:$A$4,FALSE),MATCH(A2,Sheet1!$A$1:$D$1,FALSE)),INDEX(Sheet1!$A$2:$D$4,0,MATCH(A2,Sheet1!$A$1:$D$1,FALSE)))

and copy down.