# Nesting IF Function as FALSE result of IF Function

#### Aeafa

##### New Member
Hello everyone!
I am developing a spreadsheet in which I need to match a value against 3 different tables and once it matches with a value in one of those tables result in a ceiling formula.
For example: A1 is 7 and 7 is in Table3. Once I have what table 7 is in I need to round up B1 to the nearest 2000.
There are 3 tables because each table needs B1 to be rounded up to different amounts, ie. Table1 is 100, Table2 is 1000 and Table3 is 2000.
I have tried the below formula, at it seems to work with the first IF, but once I try nesting an IF formula as the FALSE result, I end up with errors.

`=IF(VLOOKUP(A1,E1:F5,1,FALSE)=A1,(CEILING(-B1+1,100)),IF(VLOOKUP(A1,H1:I5,1,FALSE)=A1,(CEILING(-B1+1,1000)),IF(VLOOKUP(A1,K1:L5,1,FALSE)=A1,(CEILING(-B1+1,2000)),"SPECIAL")))`

I'm really not sure where I am going wrong.
Is anyone able to help out? I will be eternally grateful!

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Peter_SSs

##### MrExcel MVP, Moderator
Does this do what you want?
=IFERROR(CEILING(-B1+1,IF(COUNTIF(E1:E5,A1),100,IF(COUNTIF(H1:H5,A1),1000,IF(COUNTIF(K1:K5,A1),2000,"")))),"SPECIAL")

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

#### Aeafa

##### New Member
Does this do what you want?
=IFERROR(CEILING(-B1+1,IF(COUNTIF(E1:E5,A1),100,IF(COUNTIF(H1:H5,A1),1000,IF(COUNTIF(K1:K5,A1),2000,"")))),"SPECIAL")

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you for this, it is a great starting point for me. I really appreciate your response.
It doesn't quite achieve what I want as I now realise I have an error in my original post.
I should have said that B1 should be rounded up to the nearest 100 only if it is more than the Table limit.
So sorry, as the the post should have read:
A1 is 7 and 7 is in Table3. Once I have what table 7 is in I need to round up B1 to the nearest 100 only if B1 is more than 2000, and if it is not then return SPECIAL.
There are 3 tables because each table needs B1 to have reached certain number before being rounded up to different amounts, ie. Table1 needs to reach 100, Table2 needs to reach 1000 and Table3 needs to reach 2000.

Thank you, I have updated my profile too! ^_^

#### Aeafa

##### New Member
I have now solved my problem.

Thank you for your response. It was a great help!

#### Peter_SSs

##### MrExcel MVP, Moderator
Great news, glad you got it sorted! Thanks for letting us know.

.. and thanks for updating your profile.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,290
Messages
5,836,444
Members
430,429
Latest member
monkeyhalf

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