Find Last Row in Array with Multiple Criteria

Re: Find Last Row in Array with Multiple Criteria

One way:
[f]
{=MAX(IF($A$2:$A$12="A",IF($B$2:$B$12="NY",$C$2:$C$12)))}
[/f]

Another option is to use DMAX function.
You data source must use headers.
Assuming row1 - header row and data is in the range of A2:C12

1. Set up your criteria cells (required for DMAX). Let's use G1:H2

G1 and H1 are exactly the same as A1 and B1 header cells
G2 contains: ="="&G4
H2 contains: ="="&H4

Where G4 and H4 are input cells to make the criteria flexible

The cell to show the results would contain: =DMAX($A$1:$C$12,3,$G$1:$H$2)

See the Excel Help on DMAX and do a little searching, you may like it better than the array formula above (which could also use input cells to make it more flexible).

Re: Find Last Row in Array with Multiple Criteria

Apologies, misunderstood the requirement.

Add a helper column (say "D" for this example) and use this formula: =IF(AND(A2=$G$4,B2=$H$4),ROW(),"")
As before G4 and H4 are the input cells for the criteria.

The helper column will count the duplicate entries and return the row number of the last entry. Since it is incrementing this will always be the max value so now we just perfom a simple look up.

=INDEX($C$2:$C$12,MATCH(MAX($D$2:$D$12),$D$2:$D$12,0))

Does that work for you?

forum.ozgrid.com/index.php?attachment/54673/

Re: Find Last Row in Array with Multiple Criteria

Quote from Dave_Mc;671863

I have a table that looks like this:
C1 C2 C3
A NY 100
A NY 250
A NY 300
A NY 400
A NY 500
B LA 60
B LA 90
B LA 120
B LA 150
B LA 180
B LA 200

I want to pull the last value in column C when the criteria match. Ie: A and NY result is 500; B and LA result is 200.

Any ideas?

Thanks

Display More

You Can try this formula , where A2:A12 represent C1, B2:B12 represent C2, C2:C12 represent C3.
=SUMPRODUCT(MAX((A2:A12="A")*(B2:B12="B")*C2:C12))

Re: Find Last Row in Array with Multiple Criteria

See example file attached to previous post