0

I have an excel document with the LOOKUP formula which grabs the last entered date from another sheet. From below, you can see the date for 'Person One" is also the last date entry on the second image.

enter image description here

Sheet two

The Excel formula I used was

=LOOKUP(2,1/('Person One'!C:C>0),'Person One'!C:C)

However, when I use the same formula on Google Sheets, it returns an error stating:

Did not find value '2' in LOOKUP evaluation.

or it gives me the topmost value which is 'Last Visit' and I am not sure what I am doing wrong.

Improve this question
7
  • Why not simple =MAX(INDIRECT("'"&A2&"'!C1:C"))?
    – JohnSUN
    Aug 12, 2022 at 17:43
  • @JohnSUN because im not very excel literate and your formula gives me an error too
    – Diu.Lei
    Aug 13, 2022 at 1:43
  • I see only three possible reasons for an error to occur with this formula. 1: you are trying to calculate it in Excel, and this program does not know C1:C notation, this formula is for Google spreadsheets. 2: Person One is spelled differently in cell A2 and in the sheet name (maybe there is a space in the cell after One that you can't see). 3: the reason you mentioned because im not very excel literate
    – JohnSUN
    Aug 16, 2022 at 7:56
  • The lookup() function uses a binary search that only works correctly when the data range to be sorted. With unsorted data, you get unexpected results.
    – doubleunary
    Aug 19, 2022 at 4:05
  • The formula works fine and dandy on excel, but when I upload it to google sheets, the formula doesnt work. Not sure what the "unsorted data" stuff is about, I thought excel and google sheets would be compatible enough for it to work
    – Diu.Lei
    Aug 19, 2022 at 4:30

1 Answer 1

Reset to default
1
+50

To get the chronologically latest date in column 'Person One'!C2:C, you can simply use max(), like this:

=max('Person One'!C2:C)

To get the last non-blank value toward the bottom of the column 'Person One'!C2:C, use sort(), like this:

=+sort('Person One'!C2:C, 'Person One'!C2:C <> "", false, row('Person One'!C2:C), false)

If you want to get the most recently manually entered date, regardless of whether that date is before or after other dates in the column, you will need a script. An onEdit(e) simple trigger would be sufficient to do that.

The lookup() formula you quote will work in Google Sheets, provided that you put it in an arrayformula() wrapper. I think that the formula will need to be an array formula in Microsoft Excel as well. You can use Control+Shift+Enter instead of plain Enter when entering it to make it an array formula on both platforms.

Note that both Google Sheets and Microsoft Excel expect that the source data in C2:C is sorted. If the data is unsorted, you may get unexpected results. See:

Improve this answer
2
  • Thank you for both providing the new formula and the Ctrl+Shift+Enter trick, the latter is most useful. Im not very google sheets/excel literate, would you mind explaining how putting the formula in arrayformula() wrapper makes it work on both platforms?
    – Diu.Lei
    Aug 20, 2022 at 14:55
  • The arrayformula() wrapper is only available in Google Sheets. It gets inserted in the formula when you press CSE. Microsoft Excel does not have this function, but indicates array formulas using curly braces, and requires you need to press CSE when editing such formulas.
    – doubleunary
    Aug 20, 2022 at 18:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.