XLOOKUP Not Return Desired Results
I am trying to make a mileage log, but not all miles are tracked. Often, there are gaps between the vehicle’s beginning mileage and its “prior” ending mileage.
I jot the mileage on small postage notes, then at some point I enter the mileage into Numbers. While I try to keep the notes in sequence, occasionally I will misplace or get one out of order.
If I enter a vehicle’s beginning mileage that’s less than its prior ending mileage, I want to highlight my beginning mileage is not correct. The mileage can be equal to the last ending milage, but it cannot be less than the last ending mileage.
I though the best way to do this was to create a helper cell (column G: Last Logged Ending Mileage). Then use conditional formatting to highlight if the vehicle’s beginning mileage is less than the vehicle’s last ending mileage. This may not be the best practice, but I though it’s something I could do.
I used the XLOOKUP function (see screenshot for formula), but I’m not getting the desired results. As an example: using row 7, I was expecting the function to match the vehicle in column B (i.e., GMC), then go to the GMC's last entry (row 5) and return the ending mileage of 121 from column D, but instead it return 105.
I would appreciate help my formula and welcome suggestions for best practices or alternative solutions.
Thank you
