problem with match index functions

[tomas]

I am learning excel and trying to figure out problems. In file lookup to left I am trying answer the question in H13 a my N/a formula is in I13. I need to find find rank for person with second highest sales. With match function I found a position of this person so I have so I have first argument for rank function, the second is easy . so please help.

in file sales 2013 match index I am trying using these formulas find the highest figure in array A1:J6. I learned how to make such formula if index used only for one column but if its used for array its not working for me. My formula is in L6

any help appreciated

 

[Somendra Misra]

Hi tomas,

See you lookup-to-left file for the formula in H13.
For your second file below are the observations:
1. MATCH function works only in 1-Dimensional array (horizontal & Vertical), you are using a 2-D array.
2. When defining a name range, avoid putting column & row header in them, they can be excluded.
3. for finding the large in a 2D array simply use =LARGE(banana,1). See the file, I had changed the banana ref. in name manager.

Write back for more doubts.

Regards,

 

[tomas]

Hi Somendra many thanks

for this 2D file I actually did not realise it can be solved so easy just wanted some practise for this formulas.

lookup unfortunately I cannot open this excel . ( I can the other one) while opening it writes something and ask if i wish recover I click yes but just empty window displays. Can you please just post the formula here?

 

[Somendra Misra]

For rank: try below formula:

=RANK(SUMPRODUCT((LARGE(D5:D17,1)=D5:D17)*(F5:F17)),F5:F17)

Regards,