# 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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.

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

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>

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

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

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
5
Views
221
Replies
5
Views
184
Replies
1
Views
97
Replies
10
Views
198
Replies
10
Views
335

1,219,828
Messages
6,150,473
Members
450,966
Latest member
Yali

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