# IF Problem

#### aivoryuk

##### Board Regular
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?

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hi. Try (alphabetical order)

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

Hi that worked thanks.

Is there a particular reason it works in alphabetical order

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

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))

Replies
2
Views
155
Replies
2
Views
105
Replies
3
Views
615
Replies
4
Views
557
Replies
9
Views
170

1,221,497
Messages
6,160,154
Members
451,625
Latest member
sukhman

### 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?

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