Referencing a second Criteria within INDEX and MATCH

Hi Tim,

You first need to get rid of your Merge cells in the table and repeat all cell data. Merge cells keep you from dealing with your data as a real table.

I did that and created a Helper column (so I didn't need array formulas) to put two columns together. Then a Pivot and finally a column next to the pivot to return what I think you wanted.
Why a pivot you ask? I know that a pivot table can return the Max value of version for each column 2. See the attached to see if it helps.

Is the latest revision status found in column F that corresponds to the max date in column E for that particular document?

Try this...

Enter this formula in cell F3 of the Summary sheet and copy down:

=LOOKUP(2,1/((ISNUMBER(FIND(LEFT(INDEX(Input!A$2:A$28,MATCH(B3,Input!B$2:B$28,0)),FIND("-",INDEX(Input!A$2:A$28,MATCH(B3,Input!B$2:B$28,0)))),Input!A$2:A$28)))/(Input!E$2:E$28<>"")),Input!F$2:F$28)

You can shorten that a bit by using named ranges.

Name: Item
Refers to: =Input!$A$2:$A$28

Name: Doc
Refers to: =Input!$B$2:$B$28

Name: Dates
Refers to: =Input!$E$2:$E$28

Name: Status
Refers to: =Input!$F$2:$F$28

Then the formula becomes:

=LOOKUP(2,1/((ISNUMBER(FIND(LEFT(INDEX(Item,MATCH(B3,Doc,0)),FIND("-",INDEX(Item,MATCH(B3,Doc,0)))),Item)))/(Dates<>"")),Status)

One approach which encourages complete data association to simplify downstream calculations

In Input,
1. Complete the row-wise data association for the document number column and document title column, which have gaps. This simple formulae setup will "fill-from-above" and do the task in 2 seconds. Complete data association will simplify downstream calculations

In I2: =B2
Copy across to J2

In I3: =IF(B3="",I2,B3)
Copy across to J3, fill down to the last row of source data

Then in Summary,
As the Latest date is simply the "Max" date ...
To extract latest Rev
Put this in D3, and array enter the formula, ie press CTRL+SHIFT+ENTER to confirm the formula:
=INDEX(Input!$D$2:$D$28,MATCH(MAX(IF((Input!$I$2:$I$28=$B3)*(Input!$J$2:$J$28=$C3),Input!$E$2:$E$28)),IF((Input!$I$2:$I$28=$B3)*(Input!$J$2:$J$28=$C3),Input!$E$2:$E$28),0))
Copy down

Likewise, to extract latest Status,
Put in F3, and array enter the formula, ie press CTRL+SHIFT+ENTER to confirm the formula:
=INDEX(Input!$F$2:$F$28,MATCH(MAX(IF((Input!$I$2:$I$28=$B3)*(Input!$J$2:$J$28=$C3),Input!$E$2:$E$28)),IF((Input!$I$2:$I$28=$B3)*(Input!$J$2:$J$28=$C3),Input!$E$2:$E$28),0))
Copy down
----------------------------------
Success? Celebrate it, click on the little star at the bottom left of my responses

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.