Index Match - last value

[emmagizer]

Hi
I'm trying to use the index match formula but it only picks up the first value, I need it to collect the last value - is this possible?

I have a Building Permit register and an inspection register but would like to see where the job is at in regards to inspection status.

So for example job number ABC-21010 has had 2 inspections, Waterproofing on the 10/1/21 & Final on the 15/1/21.
I need column K in the Job Register Sheet to reflect "15/1/21 - Final" however it shows "10/1/21 - Waterproofing"

I've attached an examples spreadsheet

Any assistance would be great

[protonLeah]

array formula:

Please Login or Register  to view this content.

[bebo021999]

Non array solution:

Please Login or Register  to view this content.

[FDibbins]

INDEX/MATCH will always only ever find the 1st match - it stops looking after that.

You need to add a work-around by adding something extra to the MATCH criteria that will ID which entry you need to return - I did that adding the Inspection Type to the criteria...
K2=IFERROR(INDEX(Inspections!$F:$F,IF(COUNTIF(Inspections!$A:$A,'Job Register'!$A2)>1,MATCH('Job Register'!$A2&"Final",INDEX(Inspections!A:A&Inspections!$E:$E,0),0),MATCH('Job Register'!$A2,Inspections!A:A,0))),"")

[emmagizer]

Thankyou very much, these formulas are perfect.

Question.. what does "array formula" & "non array solution" mean??

[Bo_Ry]

Please try XLookup Last to first

=XLOOKUP([@[ABC Job Number]],Table2[ABC Job Number],Table2[Date & Inspection Type (Merged)],"",,-1)