• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup or Index

Jamie Wagler

Jamie Wagler

Member
Please refer to the attached file.

In Row 1 I am trying to retrieve the most commonly appearing number in that particular column in reference to the number in column F.

In Row 2 I am counting the number of times the Max number appears in that column.

In Column G I am Summing the total number across the row.

My issue is with column B where both rows 8 & 9 have a value of 26. In this case I would like the secondary value in Column G to be the 'tie breaker'.

I feel that there should be a simple way to do this with an INDEX but I am unsure on how to tie it together.
 

Attachments

Jamie -

Take a look at your sample file again - it appears that the file isn't reflecting what you're saying in your narrative, which makes understanding your issue a bit more convoluted ... :confused:o_O
 
My mistake David I edited the file I had open after uploading the file to the email.

Please see the file attached.

In Row 1 I am asking for the value in F column which corresponds to the largest value for that column. I.E. Column A (rows 3 to 10) the largest value is 49 so the value returned from F Column is 2.

My issue is cell B1. In Column B (rows 3 to 10) there are two values of 26 which is why Cell B1 has a 6. What I want is 7, this is because between 6 and 7 the sum per row in G Column 7 has a greater value than 6.

I have the same issue in Column C where there are two 9's and I would like 7 to appear in Cell C1 not 5
 

Attachments

I am having a bit of success with

=INDEX(B$3:$F$10,MATCH(MAX(B3:B10),B3:B10),5)

But in Cell A1 it is returning 8 when it should be returning 2 and I am not sure why.
 
I'm using a slightly different approach - =MATCH(MAX(A3:A10),A3:A10,0).

Then I was thinking looking for duplicates of the max number .... but I'm not there yet!
 
Right, which is why I have the values in G column. I wanted to use that as the second criteria with the indicator in row 2 of how many Max duplicates there were. In case of 3+
 
Hmmm - I've got a solution for you, but it's returning the same 8 instead of 2 in the first Column, but I'm doing it in a different manner from you .... hmmmm

I've looked at it for 15mins and it's not coming to me yet ..... We need to attract a Ninja!

Look at the Sheet DME Calcs for my version of your challenge ..
 

Attachments

This is why I believe the answer is with an INDEX array where it (when row 2 is greater than 1) will compare the values in G column and return the value from F column with the greater value.
 
Hey Jamie,

Try the below CSE function without any helper column

INDEX($F$3:$F$10,MATCH(1,(A$3:A$10=MAX(A$3:A$10))*(SumRng=MAX(SUBTOTAL(9,OFFSET($A$2,SMALL(IF(A$3:A$10=MAX(A$3:A$10),$F$3:$F$10),ROW(INDIRECT("1:"&COUNT(IF(A$3:A$10=MAX(A$3:A$10),$F$3:$F$10))))),,,5)))),0))


Define Name: SUBTOTAL(9,OFFSET(Sheet1!$A$2,ROW(Sheet1!$A$3:$A$10)-2,,,5))

See the attached for reference
 

Attachments

Thanks all for your help!

I think I've got it working

=MAX((MAX(C$40:C$88)=C$40:C$88)*$K$40:$K$88)

as an array seems to do the trick
 
I have been looking at all of the formulas provided and although they are good when there is a single Max number in a column.

What I require is...

When there are 2 or more identical Max values in the same column the higher value in G column should act as the tie breaker.
 
You must log in or register to reply here.
Back
Top