# Thread: excel formula (office xp)

1. ## excel formula (office xp)

Hi. [img]/forums/images/smilies/smile.gif[/img]. i was trying to do this formula (difficult for me) but couldn't. just find the max value of an an array and return the 'stuff' next to the cell of max value. as example, please see small sample in attached file. Thanks.

2. ## Re: excel formula (office xp)

Hi

In your example you could use:
<code>=VLOOKUP(F2,D1:E15,2,FALSE)</code>

The first part (F2) is the value to lookup
D1:E15 is the table that it is looking up, the first column is the column it looks in to match the value
2 - this is the relative column in the range D1:E15 that it is looking in to get the result
FALSE - this means it must find an exact match

3. ## Re: excel formula (office xp)

Hi Tony, thanks a lot. it works and you made my task lighter.

Jill.

4. ## Re: excel formula (office xp)

What you want is a Top 1 (Max) list. What follows is a formula system I devised to create the so-called Top N lists.

Let D1:E16 house the sample your attachment shows, which is extended with an additional record:

{"NUMBERS","LETTERS";5,"b";9,"c";10,"d";2,"e";4,"f ";6,"g";7,"h";11,"i";20,"j";20,"k";25,"l";12,"m";3 ,"n";1,"o";25,"p"}

In F1 enter: RANK

In F2 enter & copy down:

=RANK(D2,\$D\$2:\$D\$16)+COUNTIF(\$D\$22,D2)-1

In H1 enter: MAX VALUE

In H2 enter:

=MAX(D216)

In H3 enter: 1 (which means: I want a Top 1 list.)

In H4 enter:

=MAX(IF(INDEX(D216,MATCH(H3,F2:F16,0))=D216,F2:F16))-H3

which must be confirmed with control+shift+enter instead of just with enter.

This formula calculates the number of the ties of the max value.

In H5 enter & cpoy down:

=IF(ROW()-ROW(H\$5)+1<=\$H\$3+\$H\$4,INDEX(\$E\$2:\$E\$16,MATCH(ROW()-ROW(H\$5)+1,\$F\$2:\$F\$16,0)),"")

The result list is:

{"l";"p"}

Running a pivot table on the sample would give you exactly the same list.

5. ## Re: excel formula (office xp)

=VLOOKUP(MAX(D215),D2:E15,2, FALSE)
does it in one step

6. ## Re: excel formula (office xp)

Why don't you try it on the sample I used?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•