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

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
Joined
May 28, 2005
Messages
54,391
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
54,391
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)
 
Master Excel Bundle

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.

Forum statistics

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