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

Snake Eyes

New Member
Joined
Dec 14, 2010
Messages
45
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Try
Excel Formula:
=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

New Member
Joined
Dec 14, 2010
Messages
45
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
Excel Formula:
=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

Well-known Member
Joined
Mar 5, 2018
Messages
1,286
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What is the formula in N159:N177?
 

Snake Eyes

New Member
Joined
Dec 14, 2010
Messages
45
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
Excel Formula:
=LOOKUP(2,1/(N159:N177<>""),N159:N177)
 

Snake Eyes

New Member
Joined
Dec 14, 2010
Messages
45
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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...
Excel Formula:
=(H175*I175)-K175
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
In that case do it like Toadstool showed
Excel Formula:
=LOOKUP(2,1/(N159:N177<>0),N159:N177)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,589
Messages
5,597,053
Members
414,118
Latest member
moversnpackers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top