Identifying specific data in Excel

Question

Hello,

I'm currently putting together an excel doc that's linked to a google form that my coworkers use to input current vehicle mileage after use. My question is this, all the data links to this excel document but what I'd like to do is have a separate cell that shows the most recent mileage input based on date entered. Is there a formula for this?

For example how can I get the most recent entry for Vehicle 4 (highlighted below) and it's corresponding mileage at turn-in to automatically appear in the highlighted cell in Column I. is it possible?

Additional: if there is a solution to this, please dumb this down for me as much as possible as i'm extremely new to Excel.

Answer 1

Hi,

1. Yes . . You may select an entire column.

I will recommend . . instead of an entire column, You take a larger range e.g. $B$2:$B$5000 OR $B$2:$B$10000.

2. You are writing the formula in cell I14.

Vehicle 1 is written in cell I13.

Hence, in the formula in the picture . . please change I14 to I13.

Please respond if You require further assistance. I will try My best to be of help.

If I was able to help You, please mark My response as answer and helpful.

Thank You!

Answer 2

First, thank you so much, this has been helpful, second what am I missing lol? I've adjusted the field for each vehicle to display the mileage in but it's just returning zeros, I have to be missing something obvious. Also is there a way to have it pull data from the entire column rather than specifying specific cell ranges?

Answer 3

Hi,

Please check whether the following solution is helpful:

Formula in cell I12 is:

=XLOOKUP(I11, TEXTBEFORE($B$2:$B$5, " (", 1), $D$2:$D$5, "Not Available", , -1)

NOTE

1. In the above suggested formula, please change ranges to suit Your requirement.

2. Please copy cell I12 and paste on cell J12, K12, I15, J15, K15, I18.

Please respond if You require further assistance. I will try My best to be of help.

If I was able to help You, please mark My response as answer and helpful.

Thank You!

Answer 4

So this is getting me in the right direction but the formula is displaying the wrong data. I need the most recent mileage input for each vehicle to appear in it's corresponding block based on most recent date entered. So for example, if the most recent mileage by date input was for vehicle 4, I need that mileage to reflect in the corresponding cell.

Answer 5

=FILTER(B2:B6,(C2:C6=MAX(FILTER(C2:C6,ISNUMBER(SEARCH(I14,B2:B6)))))*ISNUMBER(SEARCH(I14,B2:B6)))

Formula in I15