Get the maximum value and then how to get other values on the same ROW

[gborja888]

I'm trying to fix a macro code which can get the maximum values (array formula - =MAX-IF) and the other values on the same using (multiple array formula =INDEX). However I find this method to be really slow. I have tried different ways ie. using dynamic named range but still find this slow.

Please Login or Register  to view this content.

I need to find the value for Column C, D and E for Sheet2. I'm using the following array formula:


[C2] {=MAX(IF((Sheet1!C$1:C$2000=A2)*(Sheet1!D$1:D$2000=B2),Sheet1!E$1:E$2000))}
[D2] {=INDEX(Sheet1!F$1:F$2000,MATCH(1,(C2=Sheet1!$E$1:$E$2000)*(A2=Sheet1!$C$1:$C$2000)*(B3=Sheet1!$D$1:$D$2000),0))}
[E2] {=INDEX(Sheet1!B$1:B$2000,MATCH(1,(C2=Sheet1!$E$1:$E$2000)*(A2=Sheet1!$C$1:$C$2000)*(B3=Sheet1!$D$1:$D$2000),0))}

Due to duplicate max values, I can't just use a single array formula or just referring to the max values for MAX_PART_USG, I have to include the column A and B as well to find the exact match for Column D and E.

I was thinking is there a way to find the match for MAX values and at the same time pick up the values of the other fields on the same row? instead of doing the matching on column D and E. Any suggestions on how to do this? TIA.

[Leith Ross]

Hello gborja888,

Is there some reason you don't want to use VBA?

[rylo]

Hi

If you want to stick with formulas then try this

G2: =MATCH(MAX(IF((Sheet1!C$1:C$16=A2)*(Sheet1!D$1:D$16=B2),Sheet1!E$1:E$16)),IF((Sheet1!C$1:C$16=A2)*(Sheet1!D$1:D$16=B2),Sheet1!E$1:E$16),0) (array entered)
C2: =INDEX(Sheet1!E:E,Sheet3!$G2)
D2: =INDEX(Sheet1!F:F,Sheet3!$G2)
E2: =INDEX(Sheet1!B:B,Sheet3!$G2)

Doesn't bring all 3 back in one hit, but you don't have to refer to C2 for D2 and E2...

rylo

[gborja888]

Hi Leith,

No, if VB code can make this run faster, I would be happy to try this. Any suggestions on how to do this using VB code?

Gerry

[gborja888]

Hi Rylo,

Thanks for your reply. Do I need to create a new sheet - sheet3 for G2 to enter the MATCH array formula?

Gerry

[gborja888]

Hi Rylo,

I'm getting #N/A error on G2 after creating sheet3, paste sheet2 and then enter your array formula on G2.

Gerry

[jindon]

Try this and let's see if this makes any better.

Please Login or Register  to view this content.

[gborja888]

Hi Jindon,

Thanks, I will try this out.

Gerry

[gborja888]

Hi rylo,

Nevermind about the N/A error, I figured it out. Thanks again.

Gerry

[gborja888]

Hi Jindon,

It works perfectly! It runs really fast now. Thanks again..

rylo, Your suggestion also works too! Thanks again..

I have got two solutions now and will decide which one to use. I really appreciate all your help!

Gerry

[jindon]

Hi Jindon,

It works perfectly! It runs really fast now. Thanks again..

rylo, Your suggestion also works too! Thanks again..

I have got two solutions now and will decide which one to use. I really appreciate all your help!

Gerry

Nice.......

[gborja888]

rylo, I have just discovered that the match command you've given me have some limitations:

=MATCH(MAX(IF((Sheet1!C$1:C$16=A2)*(Sheet1!D$1:D$16=B2),Sheet1!E$1:E$16)),IF((Sheet1!C$1:C$16=A2)*(S heet1!D$1:D$16=B2),Sheet1!E$1:E$16),0) (array entered)

If I use the actual range say Sheet1!C$1:C$25655, it won't get executed in the macro. Entering this formula however in EXCEL spreadsheet seems to be fine. The maximum value seems to be 9999. I'm just curious why this happened.

Gerry