How To Reference Last Value in Column (To Show In Another Cell?)

[Dhira]

For info purposes, I have a column O with formulas, but it only shows a value if there are other valuse in the row. Otherwise, it shows a blank cell (but there's a formula in the cell).

The attempt I made with a VLOOKUP was:

=LOOKUP(2,1/(O:O<>""),O:O)
and
=LOOKUP(2,1/(NOT(ISBLANK(O:O))),O:O)

Neither one works, as I get a "#N/A" error ("Did not find value '2' in LOOKUP evaluation.")
What formula can I use to reference & show the value in the last cell?

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For the values that are in column O, are they
- numerical?
- text?
- could be some of each?

 

[Dhira]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For the values that are in column O, are they
- numerical?
- text?
- could be some of each?

I have Microsoft Office 2021, but I'm also using the formula in google sheets.

The values are numerical (in dollars), but like I said the column has formulas that only show a value if there's a calculation in the row.

 

[Dhira]

Plot twist - the formula does work in Excel, but not in Google Sheets

Turns out in Google sheets, the formula is different:

=INDEX(O:O,MATCH(100^100,O:O))

Weird, but this works.

Thanks for listening.

 

[Peter_SSs]

Since this really seems to be a Google Sheets question, I have moved it to the appropriate forum.