"Martin Brown" wrote:
"Pat" wrote:
=IF(D2="","",LOOKUP(D2,{0,4.99,9.99,24.99,99.99,14 99.99},
{"1","1.5","2.5",D2*0.1,(0.05*(D2-100))+10,(0.03*(D2-1500))+80}))
This formula works in google sheets but not in Excel on my PC.
How to make it works on Excel? "Using Excel 2007"

Try 2003 or earlier it will probably work OK there.

No it doesn't. At least, not in Excel 2003.


"Martin Brown" wrote:
On 04/03/2014 20:32, joeu2004 wrote:
=IF(D2="","",CHOOSE(MATCH(D2,{0,4.99,9.99,24.99,99 .99,1499.99}),
"1","1.5","2.5",D2*0.1,(0.05*(D2-100))+10,(0.03*(D2-1500))+80))
[....]
When I try it in XL2010 it faults any attempt to put a formula
in the results array list. It only accepts simple manifest constants.

..... Which is why I effectively replaced LOOKUP with CHOOSE.

From the Excel LOOKUP help page:

[BEGIN QUOTE]
LOOKUP(lookup_value,lookup_vector,result_vector)

Lookup_value is a value that LOOKUP searches for in the first vector.
Lookup_value can be a number, text, a logical value, or a name or reference
that refers to a value.

Lookup_vector is a range that contains only one row or one column. The
values in lookup_vector can be text, numbers, or logical values.
[END QUOTE]

"Text, number or logical values". Nothing there about expressions. A
little experimentation would have confirmed:

=LOOKUP(5,{0,4.99,10},{1,2,3})

works, but:

=LOOKUP(5,{0,4.99,10},{1,2+0,3})

raises a syntax error.