Lookup Formula not working completely

  • Thread starter Thread starter Newsgal
  • Start date Start date
N

Newsgal

=LOOKUP(M11,{0,0.98,1},{0,"$750.00","$1000.00"})
This formula works fine if M11=100+%. However, it's not working when M11 =
98%.
Help!
 
What do you mean when you say it's not working?

It works for me.

M11 = 0.98

The formula correctly returns $750.00 (as a TEXT value)
 
Yes, M11 is formatted as a percentage. Does that require different set up in
the forumula?
 
No, the formatting has no effect on the lookup. He just wanted to ensure you
had 0.98 in the cell, rather than 98.

When you say "it's not working", what result do you get -- 0 or $1000.00?

Regards,
Fred.
 
My bet is that M11 is not quite 98.0%. It's, say, 97.9992%. This will format
to one decimal place as 98.0%, but won't meet the lookup criteria.

What happens when you format M11 as General? What do you see then?

Regards,
Fred.
 
Newsgal said:
I get a $0. (If it helps, M11 is formulated as a percent and displays 98.0%.)
Click to expand...

You probably have a formula-calculated value in M11 which is returning say:
97.99%. The formatting applied makes it appear as 98.0%, but the underlying
value remains at 97.99%.

Try it like this:
=LOOKUP(ROUND(M11,2),{0,0.98,1},{0,"$750.00","$1000.00"})

---
 
That was it! Thanks, Max.
--
News Gal


Max said:
You probably have a formula-calculated value in M11 which is returning say:
97.99%. The formatting applied makes it appear as 98.0%, but the underlying
value remains at 97.99%.

Try it like this:
=LOOKUP(ROUND(M11,2),{0,0.98,1},{0,"$750.00","$1000.00"})

---
Click to expand...
 
Back
Top