Max reverse lookup

**TMPAPA** · 01-30-2010, 05:58 AM

Hi,
I am stuck at trying to get the rows of Max value (in a given column) from a table and return the values in a corresponding column. The trick is I need multiple Max values (and their product descriptions) based on the different vendor row in the table... maybe easier if you look at attached sample.

Thank you in advance fo your help!

Marc

**JBeaucaire** · 01-30-2010, 06:04 AM

Put this array formula in H5 and confirm it with CTRL-SHIFT-ENTER to activate the array:

=MAX(IF($B$4:$B$15=$F5, $D$4:$D$15, ""))

Copy that down.

Then put this regular formula in G5 and copy down:

=INDEX($C$4:$C$15, MATCH(F5&"-"&H5, INDEX($B$4:$B$15&"-"&$D$4:$D$15, 0), 0))

**TMPAPA** · 01-30-2010, 06:11 AM

Originally Posted by JBeaucaire

Put this array formula in H5 and confirm it with CTRL-SHIFT-ENTER to activate the array:

=MAX(IF($B$4:$B$15=$F5, $D$4:$D$15, ""))

Copy that down.

Then put this regular formula in G5 and copy down:

=INDEX($C$4:$C$15, MATCH(F5&"-"&H5, INDEX($B$4:$B$15&"-"&$D$4:$D$15, 0), 0))

JBeaucaire,
Thanks Beaucaire!!! I was hoping to avid the array function... as I will be dropping these furmulas into cells using a macro (Sheets(shtMain).Range("Prod_Mkt").FormulaR1C1 =) as the raw data varies based on monthly publish (number of rows will vary).

How can I enter this array formula via a macro? Maybe that is the answer...

Thank you for the help!!

**JBeaucaire** · 01-30-2010, 06:20 AM

This is the wrong forum if you know in advance you want a programming answer, you should pose those questions in the Programming forum.

Anyway:

    Range("G5:G8").FormulaR1C1 = _
        "=INDEX(R4C3:R15C3, MATCH(RC[-1]&""-""&RC[1], INDEX(R4C2:R15C2&""-""&R4C4:R15C4, 0), 0))"

    With Range("H5")
        .FormulaArray = "=MAX(IF(R4C2:R15C2=RC6,R4C[-4]:R15C[-4],""""))"
        .AutoFill Destination:=Range("H5:H8")
    End With

You can get code like this by just turning on the macro recorder to record you entering the formulas in one cell, then edit out the "selecting".

**TMPAPA** · 01-30-2010, 06:26 AM

JBeaucaire,
With your suggestion I can avoid the macro placing the formula and I can actually lock it down in a hidden table... only when I placed sample up I made a slight error. There are multiple entries for the same product / vendor code based on sales Qtr. How can I sum these up and find the MAX based on total sales during the period?

I updated the sample.

Many thanks!!!!!!!!!!