IF Problem

[aivoryuk]

Hi

I have the following problem
=IF(ISBLANK(F11),0,LOOKUP(F11,{"H","M","L"},{1000,500,0}))

The problem I have that when if the cell (f11) has a L is returns 1000 instead of 0. it works fine if the value is H or M.

Where is it going wrong?

 

[VoG]

Hi. Try (alphabetical order)

=IF(ISBLANK(F11),0,LOOKUP(F11,{"H","L","M"},{1000,0,500}))

 

[aivoryuk]

Hi that worked thanks.

Is there a particular reason it works in alphabetical order

 

[VoG]

Lookup expects the values in the first {} to be sorted ascending (=alphabetical for text).

 

[T. Valko]

Hi

I have the following problem
=IF(ISBLANK(F11),0,LOOKUP(F11,{"H","M","L"},{1000,500,0}))

The problem I have that when if the cell (f11) has a L is returns 1000 instead of 0. it works fine if the value is H or M.

Where is it going wrong?

The lookup_array *must* be sorted in ascending order.

Assuming that the only possible entries are an empty cell, H, L or M, here's another way to do it:

=IF(OR(F11={"","L"}),0,IF(F11="H",1000,500))