Index an array, return most recent repair, first three letters lookup

Rows A, B, C is a growing list of Machine repairs.

I need to capture the most recent repair by searching the first three letters of a word in Column C and enter the part and date into the table.

(I am trying to populate Most Recent Repair Data table shown in the attachment. The data entered manually in the table is the result I am looking for)

Can you help. Thanks in advance

G4:

=IFERROR(LOOKUP(2,1/(($B$2:$B$16=$F4)*(LEFT($C$2:$C$16,3)=$H$3)),A$2:A$16),"")

H4:

=IFERROR(LOOKUP(2,1/(($B$2:$B$16=$F4)*(LEFT($C$2:$C$16,3)=$H$3)),C$2:C$16),"")

I4:

=IFERROR(LOOKUP(2,1/(($B$2:$B$16=$F4)*(LEFT($C$2:$C$16,3)=$J$3)),A$2:A$16),"")

J4:

=IFERROR(LOOKUP(2,1/(($B$2:$B$16=$F4)*(LEFT($C$2:$C$16,3)=$J$3)),C$2:C$16),"")

and then copy all 4 cells down


VLOOKUP always stops at the first match, whereas LOOKUP always stops at the last possible match. It's even possible to nest arrays into its parameters, as I have.

Hi!

If your repair table is constantly updated, you should consider making it a table (select the data then ctrl+t). This makes refering to certain columns much easier and eliminates the need of updating your ranges.

If you do that, in G4, this formula works although I find it quite unattractive ^^

=IF(MAX(IF((Table1[Machine]=$F4)*(NOT(ISERROR(SEARCH(H$3&"*",Table1[Repair information]))))*(Table1[Column1]=""),Table1[Repair Date],""))=0,"",MAX(IF((Table1[Machine]=$F4)*(NOT(ISERROR(SEARCH(H$3&"*",Table1[Repair information]))))*(Table1[Column1]=""),Table1[Repair Date],"")))

(arrayed formula so ctrl+shift+enter)

You can do it without the general IF function so just
=MAX(IF((Table1[Machine]=$F4)*(NOT(ISERROR(SEARCH(H$3&"*",Table1[Repair information]))))*(Table1[Column1]=""),Table1[Repair Date];""))

But it'll return 1/1/1900 when it should be empty, which is not very nice.


Formula copy/pasted to I4 should work aswell.

Regards

Many thanks to both for your swift solutions. I will practise on the table solution.

Best Regards

Chambo

Or thirdly, try this one.