How to use IFS and AND for checking if number falls between range

[Cuzzaa]

Hi everyone, hope someone can help.

I am trying to figure out how to use an IFS & AND statement to find if a value falls between a specific range but I am a bit stuck, could anyone help amend my formula below please?

=IFS(G3<29,C3*1,G3>29,C3*2,G3>59,C3*10)

What I'm trying to achieve is:

I have a number in cell C3, a number in cell G3, and in cell J3 I am trying to check if the number in G3 falls between 1 - 10, if it does then * C3 by 5, or, if the number in G3 falls between 11-20 then * C3 by 5, or lastly if the number in G3 falls between 21 - 30 then * C3 by 100.

Is this possible?

Thank you!

 

[jasonb75]

No IF's or AND's here

=C3*LOOKUP(G3,{1,11,21},{5,5,100})

I'm guessing that one of the 5's should be something else, but I've given you what you asked for:-

check if the number in G3 falls between 1 - 10, if it does then * C3 by 5, or, if the number in G3 falls between 11-20 then * C3 by 5, or lastly if the number in G3 falls between 21 - 30 then * C3 by 100.

 

[Cuzzaa]

No IF's or AND's here

=C3*LOOKUP(G3,{1,11,21},{5,5,100})

I'm guessing that one of the 5's should be something else, but I've given you what you asked for:-

Thank you so much Jason. Yes that was just a typo.

Can I ask, instead of * by 5, 5 or 100, how would I set it to for example find the % of cell C3, i.e. check if the number in G3 falls between 1 - 10, if it does then calculate 50% of C3, or, if the number in G3 falls between 11-20 then calculate 75% of C3, or lastly if the number in G3 falls between 21 - 30 then calculate 100% of C3?

 

[Cuzzaa]

Ignore that I've got it working now.

Thanks again so much!