# IF function

#### glerwell

##### Well-known Member
Hi

Im looking for help using the IF function

I want A1 display the value from various cells depending on the value of B1.

For example, if B1 is 2.37 then I want to display the value of E1.

Now I know the formula for this is:
Code:
``=IF(B1=2.37,E1,0)``

and if B1 is greater than 2.37 then display E2 is:

Code:
``=IF(B1>2.37,E2,0)``

I also know to use the < sign within the formula.

What I want to know is how do I display a value of a cell if the cell in question needs to be between two different values. eg.

I want A1 to display the value of E3 if it is between 2.38 - 3. 56.

Thanks

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### barry houdini

##### MrExcel MVP
The simple answer is to use AND like

=IF(AND(B1>2.37,B1<=3.57),E3)

but there may be a simpler way if you have a whole series of ranges, e.g.

=LOOKUP(B1,{0,2,4,6},E1:E4)

this will give you E1 if B1 is >=0 and < 2, E2 if B1 >=2 and < 4 etc.

#### glerwell

##### Well-known Member
Im unsure of what to use

this is my data

what i want in A1 is

if A2 is <1.10 then display D1
if A2 is between 1.11 & 1.25 display D2
if A2 is between 1.26 & 1.38 display D3

it goes on a bit but that is what Im trying to achieve

thanks

#### Yogi Anand

##### MrExcel MVP
Hi glerwell:

In your situation, I would use a Lookup Table ... as illustrated in the following ...

Excel Workbook
ABCDEFGHIJ
1ASSUMPTIONLookup Table
21.05D1if A2 is9.90E-307D1
3if A2 is between 1.11 & 1.25 display D21.11D2
4if A2 is between 1.26 & 1.38 display D31.26D3
5it goes on a bit but that is what Im trying to achieve1.39tbd
6
Sheet16

</body></html>

#### glerwell

##### Well-known Member
I can't get any to work, I keep getting circular references.

never mind I'll just enter id manually its no big deal.

thanks anyway

#### barry houdini

##### MrExcel MVP
What formula did you try and where are you putting it?

I'm assuming that A2 will always be >=0 and that when you say, D1, D2 and D3 you mean the contents of cells D1:D3.

Try

=LOOKUP(A2,{0,1.1,1.26},D1:D3)

or, if you actually want to display "D1" etc. in the result cell

=LOOKUP(A2,{0,1.1,1.26},{"D1","D2","D3"})

#### glerwell

##### Well-known Member
the values D1,D2,D3 etc are dependent on a sum of another cell eg. F5, F5 is a sum of other cells. So that's why I'm having problems, I don't think its anything to do with the formulas

Replies
4
Views
249
Replies
2
Views
79
Replies
3
Views
38
Replies
5
Views
48
Replies
9
Views
133

1,172,162
Messages
5,879,380
Members
433,425
Latest member
calinorth

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

### Which adblocker are you using?

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

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