| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Formula not working completely
=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! -- News Gal |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup Formula not working completely
It's working for me....is M11 formatted as percentage?
"Newsgal" wrote in message ... =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! -- News Gal |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup Formula not working completely
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) -- Biff Microsoft Excel MVP "Newsgal" wrote in message ... =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! -- News Gal |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup Formula not working completely
Yes, M11 is formatted as a percentage. Does that require different set up in
the forumula? -- News Gal "Gaurav" wrote: It's working for me....is M11 formatted as percentage? "Newsgal" wrote in message ... =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! -- News Gal |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup Formula not working completely
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. "Newsgal" wrote in message ... Yes, M11 is formatted as a percentage. Does that require different set up in the forumula? -- News Gal "Gaurav" wrote: It's working for me....is M11 formatted as percentage? "Newsgal" wrote in message ... =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! -- News Gal |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup Formula not working completely
I get a $0. (If it helps, M11 is formulated as a percent and displays 98.0%.)
-- -- News Gal "Fred Smith" wrote: 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. "Newsgal" wrote in message ... Yes, M11 is formatted as a percentage. Does that require different set up in the forumula? -- News Gal "Gaurav" wrote: It's working for me....is M11 formatted as percentage? "Newsgal" wrote in message ... =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! -- News Gal |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup Formula not working completely
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" wrote in message ... I get a $0. (If it helps, M11 is formulated as a percent and displays 98.0%.) -- -- News Gal "Fred Smith" wrote: 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. "Newsgal" wrote in message ... Yes, M11 is formatted as a percentage. Does that require different set up in the forumula? -- News Gal "Gaurav" wrote: It's working for me....is M11 formatted as percentage? "Newsgal" wrote in message ... =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! -- News Gal |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup Formula not working completely
"Newsgal" wrote:
I get a $0. (If it helps, M11 is formulated as a percent and displays 98.0%.) 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","$100 0.00"}) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup Formula not working completely
That was it! Thanks, Max.
-- News Gal "Max" wrote: "Newsgal" wrote: I get a $0. (If it helps, M11 is formulated as a percent and displays 98.0%.) 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","$100 0.00"}) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup Formula not working completely
"Newsgal" wrote:
That was it! Thanks, Max. Welcome. Please press the "Yes" button from where you read this. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| V Lookup Not working since change | Excel Discussion (Misc queries) | |||
| lookup not working | Excel Worksheet Functions | |||
| LOOKUP formula not working right | Excel Worksheet Functions | |||
| Lookup Not working as expected | New Users to Excel | |||
| Completely stumped for formula | Excel Discussion (Misc queries) | |||
Linear Mode
