Find the row of the maximum value based on one condition

[cmb80]

Hi everyone, I posted a similar conundrum a few days back which was kindly solved, I can't work out how to replicate the formula for what I need here so am looking for some help.


I have an attached an example file. What I am trying to do is find the row number with the highest value in column C where the value in column A is 1

The formula I have used is

Please Login or Register  to view this content.

but it is giving row number 95 - this can be seen in cell I3 where the formula is stored. It should be returning row 143, which I have manually typed into cell I4.

Can someone help with this formula?

[AliGW]

This did NOT need a new thread, however a simple tweak of the formula I gave you, which you told me worked but you now appear to have abandoned, will do the job:

=MATCH(1,(C:C=MAXIFS(C:C,A:A,1))*(A:A=1),0)

For the reason I explained in your other thread, this will find the first instance of the MAXIFS value, NOT the one you wnat:

=MATCH((MAXIFS(C:C,A:A,1)),C:C,0)

You have to define the criterion in the MAXIFS and then again in the MATCH section.

[cmb80]

I do not know why you need to be so rude in your reply? I marked the other thread as solved, hence why I did not want to re-open it.

Also, I have not abandoned your previous formula, this is a separate file to that one, and as I said - I was not able to modify your previous formula (for which I am grateful and still using) into the one I needed here.

Anyway, thank you for this proposal, I hadn't been able to figure it out for myself hence why I asked for some help

[AliGW]

Sorry - I had no intention of being rude. Indeed, there was nothing rude about my observations.

As this was a simple follow-on question, it could have been asked in the original thread (solved tags can be removed and then reapplied as necessary).

The formula you said you had tried here did not deal with the issue of defining the criterion twice - my point was that you had not used it, therefore the formula you were trying was never going to work. If you still don't understand why the criterion has to be defined twice, then please ask me to explain. If you don't, you'll keep making the same mistake.

[Czeslaw]

Formula

Please Login or Register  to view this content.

[wk9128]

Cell I3 array formula , Drag down

Please Login or Register  to view this content.

[AliGW]

Why drag down? This is a one-cell requirment.

[wk9128]

2016 OFFICE need cooperates with VBA custom user define function with one-cell requirment

Go home first, come back to the forum later

Cell I3 formula

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[AliGW]

No idea what you mean, sorry - please explain more clearly what you are trying to say.