Reference for MAXIF array formula

The array formula MAXIF correctly gives me the maximum
value of A in a separate sheet in my table Sheet2!A1:H99, say A43. How can
I find
the corresponding value in column D in A1:H99, i.e. in this case Sheet2!D43?

You have a reply to one of your threads in .misc

JAK wrote:
>
> The array formula MAXIF correctly gives me the maximum
> value of A in a separate sheet in my table Sheet2!A1:H99, say A43. How can
> I find
> the corresponding value in column D in A1:H99, i.e. in this case Sheet2!D43?

--

Dave Peterson

You would need an array formula.

What are the conditions in your maxif formula?

--
Regards,
Tom Ogilvy



"JAK" <[email protected]> wrote in message
news:[email protected]...
> The array formula MAXIF correctly gives me the maximum
> value of A in a separate sheet in my table Sheet2!A1:H99, say A43. How
can
> I find
> the corresponding value in column D in A1:H99, i.e. in this case
Sheet2!D43?
>
>

Tom

I posted a simplified version of my query. Here is a more complete version:
The formula

{=MAX((TEXT(Data!A$1:A$1000,"dd mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000))}

correctly gives me the maximum value of Data! column B subject to a
specified date (format dd mmm) in columns A of the current and data! sheets.
That works well. My problem is that while I am interested in the maximum
value returned from Data!B1:B1000 I also want the correspoding value in
another column in the full table Data!A1:H1000, e.g. the value in Data column
H in the same row as the maximum value returned from Data column D.

I would welcome help from any reader.

"Tom Ogilvy" wrote:

> You would need an array formula.
>
> What are the conditions in your maxif formula?
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "JAK" <[email protected]> wrote in message
> news:[email protected]...
> > The array formula MAXIF correctly gives me the maximum
> > value of A in a separate sheet in my table Sheet2!A1:H99, say A43. How
> can
> > I find
> > the corresponding value in column D in A1:H99, i.e. in this case
> Sheet2!D43?
> >
> >
>
>
>

=INDEX(Data!$H$1:$H$1000,MATCH(MAX((TEXT(Data!A$1:A$1000,"dd
mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),((TEXT(Data!A$1:A$1000,"dd
mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),0),1)

Your formula, as posted/written returns the maximum value in Column B for
those rows matching the date. You would change the $B$1:$B$1000 to
$D$1:$D$1000 if you want to find the row based on the maximum value in
column D.

The value returned is from column H.

--
Regards,
Tom Ogilvy


"JAK" <[email protected]> wrote in message
news:[email protected]...
> Tom
>
> I posted a simplified version of my query. Here is a more complete
version:
> The formula
>
> {=MAX((TEXT(Data!A$1:A$1000,"dd mmm")=TEXT(A8,"dd
mmm"))*(Data!$B$1:$B1000))}
>
> correctly gives me the maximum value of Data! column B subject to a
> specified date (format dd mmm) in columns A of the current and data!
sheets.
> That works well. My problem is that while I am interested in the maximum
> value returned from Data!B1:B1000 I also want the correspoding value in
> another column in the full table Data!A1:H1000, e.g. the value in Data
column
> H in the same row as the maximum value returned from Data column D.
>
> I would welcome help from any reader.
>
> "Tom Ogilvy" wrote:
>
> > You would need an array formula.
> >
> > What are the conditions in your maxif formula?
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "JAK" <[email protected]> wrote in message
> > news:[email protected]...
> > > The array formula MAXIF correctly gives me the maximum
> > > value of A in a separate sheet in my table Sheet2!A1:H99, say A43.
How
> > can
> > > I find
> > > the corresponding value in column D in A1:H99, i.e. in this case
> > Sheet2!D43?
> > >
> > >
> >
> >
> >

Tom

Thanks for your help so far

I apologise for not being clear enough. I am looking for the value in
Column D that corresponds with the maximum value in Column B- not the maximum
value in Row D. I could do that easily if I could return the reference to
the maximum value in Column B as well as the maximim value, because that
would identify the row number with the maximim B-value but have failed to see
how to do that.

Regards

JAK

"Tom Ogilvy" wrote:

> =INDEX(Data!$H$1:$H$1000,MATCH(MAX((TEXT(Data!A$1:A$1000,"dd
> mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),((TEXT(Data!A$1:A$1000,"dd
> mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),0),1)
>
> Your formula, as posted/written returns the maximum value in Column B for
> those rows matching the date. You would change the $B$1:$B$1000 to
> $D$1:$D$1000 if you want to find the row based on the maximum value in
> column D.
>
> The value returned is from column H.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "JAK" <[email protected]> wrote in message
> news:[email protected]...
> > Tom
> >
> > I posted a simplified version of my query. Here is a more complete
> version:
> > The formula
> >
> > {=MAX((TEXT(Data!A$1:A$1000,"dd mmm")=TEXT(A8,"dd
> mmm"))*(Data!$B$1:$B1000))}
> >
> > correctly gives me the maximum value of Data! column B subject to a
> > specified date (format dd mmm) in columns A of the current and data!
> sheets.
> > That works well. My problem is that while I am interested in the maximum
> > value returned from Data!B1:B1000 I also want the correspoding value in
> > another column in the full table Data!A1:H1000, e.g. the value in Data
> column
> > H in the same row as the maximum value returned from Data column D.
> >
> > I would welcome help from any reader.
> >
> > "Tom Ogilvy" wrote:
> >
> > > You would need an array formula.
> > >
> > > What are the conditions in your maxif formula?
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "JAK" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > The array formula MAXIF correctly gives me the maximum
> > > > value of A in a separate sheet in my table Sheet2!A1:H99, say A43.
> How
> > > can
> > > > I find
> > > > the corresponding value in column D in A1:H99, i.e. in this case
> > > Sheet2!D43?
> > > >
> > > >
> > >
> > >
> > >
>
>
>

What's not clear about:

>e.g. the value in Data column
> **H** in the same row as the maximum value returned from Data column
**D**.


=INDEX(Data!$D$1:$D$1000,MATCH(MAX((TEXT(Data!A$1:A$1000,"dd
mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),((TEXT(Data!A$1:A$1000,"dd
mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),0),1)

The only adjustment was to change the H's to D's

--
Regards,
Tom Ogilvy


"James Kelly" <James [email protected]> wrote in message
news:[email protected]...
> Tom
>
> Thanks for your help so far
>
> I apologise for not being clear enough. I am looking for the value in
> Column D that corresponds with the maximum value in Column B- not the
maximum
> value in Row D. I could do that easily if I could return the reference to
> the maximum value in Column B as well as the maximim value, because that
> would identify the row number with the maximim B-value but have failed to
see
> how to do that.
>
> Regards
>
> JAK
>
> "Tom Ogilvy" wrote:
>
> > =INDEX(Data!$H$1:$H$1000,MATCH(MAX((TEXT(Data!A$1:A$1000,"dd
> > mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),((TEXT(Data!A$1:A$1000,"dd
> > mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),0),1)
> >
> > Your formula, as posted/written returns the maximum value in Column B
for
> > those rows matching the date. You would change the $B$1:$B$1000 to
> > $D$1:$D$1000 if you want to find the row based on the maximum value in
> > column D.
> >
> > The value returned is from column H.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "JAK" <[email protected]> wrote in message
> > news:[email protected]...
> > > Tom
> > >
> > > I posted a simplified version of my query. Here is a more complete
> > version:
> > > The formula
> > >
> > > {=MAX((TEXT(Data!A$1:A$1000,"dd mmm")=TEXT(A8,"dd
> > mmm"))*(Data!$B$1:$B1000))}
> > >
> > > correctly gives me the maximum value of Data! column B subject to a
> > > specified date (format dd mmm) in columns A of the current and data!
> > sheets.
> > > That works well. My problem is that while I am interested in the
maximum
> > > value returned from Data!B1:B1000 I also want the correspoding value
in
> > > another column in the full table Data!A1:H1000, e.g. the value in Data
> > column
> > > H in the same row as the maximum value returned from Data column D.
> > >
> > > I would welcome help from any reader.
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > You would need an array formula.
> > > >
> > > > What are the conditions in your maxif formula?
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > >
> > > > "JAK" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > The array formula MAXIF correctly gives me the maximum
> > > > > value of A in a separate sheet in my table Sheet2!A1:H99, say A43.
> > How
> > > > can
> > > > > I find
> > > > > the corresponding value in column D in A1:H99, i.e. in this case
> > > > Sheet2!D43?
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Tom

My apologies. Your formula looks good

Jim, from South EastQueensland

"Tom Ogilvy" wrote:

> What's not clear about:
>
> >e.g. the value in Data column
> > **H** in the same row as the maximum value returned from Data column
> **D**.
>
>
> =INDEX(Data!$D$1:$D$1000,MATCH(MAX((TEXT(Data!A$1:A$1000,"dd
> mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),((TEXT(Data!A$1:A$1000,"dd
> mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),0),1)
>
> The only adjustment was to change the H's to D's
>
> --
> Regards,
> Tom Ogilvy
>
>
> "James Kelly" <James [email protected]> wrote in message
> news:[email protected]...
> > Tom
> >
> > Thanks for your help so far
> >
> > I apologise for not being clear enough. I am looking for the value in
> > Column D that corresponds with the maximum value in Column B- not the
> maximum
> > value in Row D. I could do that easily if I could return the reference to
> > the maximum value in Column B as well as the maximim value, because that
> > would identify the row number with the maximim B-value but have failed to
> see
> > how to do that.
> >
> > Regards
> >
> > JAK
> >
> > "Tom Ogilvy" wrote:
> >
> > > =INDEX(Data!$H$1:$H$1000,MATCH(MAX((TEXT(Data!A$1:A$1000,"dd
> > > mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),((TEXT(Data!A$1:A$1000,"dd
> > > mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),0),1)
> > >
> > > Your formula, as posted/written returns the maximum value in Column B
> for
> > > those rows matching the date. You would change the $B$1:$B$1000 to
> > > $D$1:$D$1000 if you want to find the row based on the maximum value in
> > > column D.
> > >
> > > The value returned is from column H.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "JAK" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Tom
> > > >
> > > > I posted a simplified version of my query. Here is a more complete
> > > version:
> > > > The formula
> > > >
> > > > {=MAX((TEXT(Data!A$1:A$1000,"dd mmm")=TEXT(A8,"dd
> > > mmm"))*(Data!$B$1:$B1000))}
> > > >
> > > > correctly gives me the maximum value of Data! column B subject to a
> > > > specified date (format dd mmm) in columns A of the current and data!
> > > sheets.
> > > > That works well. My problem is that while I am interested in the
> maximum
> > > > value returned from Data!B1:B1000 I also want the correspoding value
> in
> > > > another column in the full table Data!A1:H1000, e.g. the value in Data
> > > column
> > > > H in the same row as the maximum value returned from Data column D.
> > > >
> > > > I would welcome help from any reader.
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > You would need an array formula.
> > > > >
> > > > > What are the conditions in your maxif formula?
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > >
> > > > > "JAK" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > The array formula MAXIF correctly gives me the maximum
> > > > > > value of A in a separate sheet in my table Sheet2!A1:H99, say A43.
> > > How
> > > > > can
> > > > > > I find
> > > > > > the corresponding value in column D in A1:H99, i.e. in this case
> > > > > Sheet2!D43?
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>