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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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.

Replies
10
Views
146
Replies
2
Views
181
Replies
5
Views
90
Replies
13
Views
82
Replies
26
Views
713

1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

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.

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