Nesting IF Function as FALSE result of IF Function

[Aeafa]

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!

 

[Peter_SSs]

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]

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]

I have now solved my problem.

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

 

[Peter_SSs]

Great news, glad you got it sorted! Thanks for letting us know.

.. and thanks for updating your profile.