Defining a function to find last value with multiple criteria

[disprove]

A                 B                C                           D
Date            Item           InspectionType      RecheckDays
2024-3-21   A                Full                        
2024-3-21   B                Full                         3
2023-3-21   C                Partial                
2023-3-28   A                Full                         4
2023-3-28   B                Full
2023-3-28   C                Full
2023-4-06   A                Partial                      2
2023-4-06   B                Full                  
2023-4-06   C                Full

I am trying to create a summary sheet pulling data from a few other sheets. I find myself in need to find, for example, the last row where item=A and retrieve the value for column D for that item. In this case, the value would be 2. If fun looking for the last row where item=B, the value would be blank (or zero).

[dscheikey]

You can use the following formula for your search:

=INDEX(D:D,LOOKUP(2,1/(B:B="A"),ROW(B:B)))

[disprove]

That gives me a division by zero… but I’m playing with those functions now and maybe I can sort it out.

[dscheikey]

Try this:

disprove.ods (13,0 KB)

[disprove]

Hmm. Clearly there is a user error/setting problem on my end. I can download that, open it… it works perfectly. If I touch the formula at all, I get Div 0 error. And I mean I can do a nothing change like remove “A” and type it back in and DIV0 error. I can even “Undo” the edit… still DIV0. I can copy/paste that line into another cell (verifying it is exactly the same afterwards) and get Div0.

You are clearly solving the issue… but I’m doing something very wrong on my end.

[dscheikey]

I think it would be time for you to tell us which operating system you are using and which version of LibreOffice you are using. Maybe then someone can understand the problem.

[disprove]

Linux Mint 19.1; Libreoffice Version: 6.0.7.3

[disprove]

I updated to 7.6.6.3 and it works flawlessly. Thank you for your patience. I was convinced it was something I was doing wrong and not a bug in libreoffice.