Nesting IF Function as FALSE result of IF Function

Aeafa

New Member
Joined
Aug 31, 2016
Messages
12
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,557
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 31, 2016
Messages
12
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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
Joined
Aug 31, 2016
Messages
12
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
I have now solved my problem.

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,557
Office Version
  1. 365
Platform
  1. Windows
Great news, glad you got it sorted! Thanks for letting us know.

.. and thanks for updating your profile. (y)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

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
Top