Formulas for Latest Service Details

Hello,

Can there be formulas to get the latest service details from the Full Service Log sheet based on the selected vehicle in B6 matching the service performed B13:B30?

The requested formulas will be in Vehicle Service Overview sheet C13:F30.

Thank you very much

There are no expected results manually mocked up ... Please add 2-3 rows to show what's required.

I changed the workbook to include 2 examples.

This would work if you didn't have structured tables:

=IFERROR(CHOOSECOLS(TAKE(FILTER(LogT[[Column1]:[Column6]],(LogT[Column2]=$B$6)*(LogT[Column4]=$B13)),-1),1,3,5,6),"")

In the workbook, I converted the table to a range to show it working.

You paste the formula into C13 and drag copy down. It's a dynamic array formula that spills the required results to the right. Where the cells remain blank, there is no data to spill.

Only column C contains the formula.

If you want to keep the tables you should create a formula for each cell: For example: =XLOOKUP([@Column1],LogT[Column4],LogT[Column1],"",,-1)

Which one? Mine is - it references B6.

delete comment please

worksheet or Tab name : Vehicle Service Overview

Cell C13 formula , Drag down and across

Formula:

Please Login or Register  to view this content.

disregard comment

Thanks yes formulas in post #16 also work great! @wk9128

I am using yours Ali just wanted to mention for others.

Use @UserName to make it clear whom you are addressing in your posts. Thanks.

Further checking it seems the results are for the oldest date per service not the newest.

@AliGW can you please check?

Thank you

Try this instead:

=IFERROR(CHOOSECOLS(TAKE(FILTER(LogT[[Date]:[Comments]],(LogT[Vehicle]=$D$2)*(LogT[Service Performed]=$B8)),1),1,3,5,6),"")

No worries. I hadn't realised that the source data was in reverse chronological order!

Yes, you can - hang on a minute.

This will sort the range within the formula, so it won't matter what order it is in on the source sheet:

=IFERROR(CHOOSECOLS(TAKE(SORT(FILTER(LogT[[Date]:[Comments]],(LogT[Vehicle]=$D$2)*(LogT[Service Performed]=$B8)),1,-1),1),1,3,5,6),"")

How do you want to develop it? Or dow you really mean, "How does it work?"

How you can use it in other situations is not something I can answer, as I am not you and I am not in any of your situations!

However, I can break it down for you:

FILTER(LogT[[Date]:[Comments]],(LogT[Vehicle]=$D$2)*(LogT[Service Performed]=$B8))

This filters the range LogT[[Date]:[Comments]] where LogT[Vehicle]=$D$2 and LogT[Service Performed]=$B8.

SORT(...,1,-1)

This sorts the array returned above using its first column (1) which is the date and then in reverse chronological order (-1), that is from newest to oldest.

TAKE(...,1)

This takes the first row from the resulting array (which will be the latest date that the check in question was carried out).

CHOOSECOLS(...,1,3,5,6)

This then extracts from that row the columns required (1, 3, 5 and 6).

IFERROR(...,"")

Finally we mask any rows that return an error because there's no match.

No worries.