# Google Sheets LOOKUP last available value from another sheet

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.  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.

• Why not simple `=MAX(INDIRECT("'"&A2&"'!C1:C"))`? Aug 12, 2022 at 17:43
• @JohnSUN because im not very excel literate and your formula gives me an error too 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 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. 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 Aug 19, 2022 at 4:30

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:

• 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? 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. Aug 20, 2022 at 18:09