Prevent lookup formula from capturing a cell with only a hyphen "-" symbol in it

[Snake Eyes]

Hi All,
I have a Lookup formula in a cell that is capturing the cell value of the last cell in a range with actual data. In that range, each cell gets it's value from a simple cell reference or formula. The cells with a formula sometimes result in a dash (hyphen) "-" symbol.
My issue is that the lookup formula captures the hyphen in the last cell in the range if that cell has the hyphen and not the last cell with actual data.
How can I prevent this?
I have already changed the formatting of those cells to hide the hyphen but the lookup still sees the value because it is technically nor a blank cell.


Here is my LOOKUP formula that captures the hyphen...
=LOOKUP(2,1/(O159:O165<>""),O159:O165)

I tried this but I then capture the last blank cell...
=LOOKUP(2,1/(O159:O165<>"-"),O159:O165)

I can't figure out the correct syntax to ignore blanks AND hyphens.

Kindest Regards,
Snake Eyes

 

[Peter_SSs]

Try

=LOOKUP(2,1/(O159:O165<>"-")/(O159:O165<>""),O159:O165)

.. or change the formula that sometimes results in a "-" to return "" instead and then use your original LOOKUP formula.

However, I suggest that you update your Account details (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’)

 

[Snake Eyes]

Thanks for the suggestions Peter_SSs however, neither seems to work.
Note, I needed to expand the range but the issue remains the same.

Ultimately, I need this cell that is formatted as a number with two decimal places (1234.00) to capture the value of the last cell in the range and not capture the last blank cell which results in a blank cell.

What I have determined is that if the cells in the range get their values from any formula, which is what I need, then I have my issue.
If the cells in the range are absent any formulas requiring manual data entries then this code works as desired.

=LOOKUP(2,1/(N159:N177<>""),N159:N177)

I guess the question now is how to get the formula to work with a range of cells that contain formulas?

Thanks for your time.

 

[Toadstool]

Hi Snake Eyes,

Is the hyphen a result of the cell being formatted as currency and the calculation resulting in zero?

=LOOKUP(2,1/(O159:O165<>0),O159:O165)

 

[Fluff]

What is the formula in N159:N177?

 

[Snake Eyes]

Hi Snake Eyes,

Is the hyphen a result of the cell being formatted as currency and the calculation resulting in zero?

=LOOKUP(2,1/(O159:O165<>0),O159:O165)

Hi Toadstool,
The hyphen was never actually a problem and was resolved when I formatted the cell as general.

I need to get this formula to work with a range of cells that contain formulas and not capture the last blank cell but instead capture the value of the last cell in the range that has a real value.

=LOOKUP(2,1/(N159:N177<>""),N159:N177)

 

[Snake Eyes]

What is the formula in N159:N177?

Hi Fluff,

Each cell in the range refers to other cells in the same row with this formula...

=(H175*I175)-K175

 

[Fluff]

In that case do it like Toadstool showed

=LOOKUP(2,1/(N159:N177<>0),N159:N177)

 

[Snake Eyes]

Thanks All.
Toadstools suggestion worked.