IF Statement Not Accepting Parameters - Spreadsheets-CodePudding

I have a large spreadsheet that I am automating. I want to get a IF statement to accept multiple parameters. Can't seem to get this to work. I have lines where AIxxxx are either 160 or 320, but the outcome below still provides the original value or 160, 320, ect.

=IF(AI1264={10,20,40,80,160,320,640,1280,2560,5120,10240,20480},5,AI1264)

AIxxx is the original value I am looking at and determining if it equals 10,20,40,80,etc,etc. If it does, I want the output to be 5. If it does not equal one of the above, it should show the original value of AIxxx

I could break this out into a large IFS statement, or make each variable challenge a separate column, but that would make the spreadsheet even bigger, so I would like to get this working.

Any help is appreciated!

CodePudding user response：

With a little logic and algebra...

=IF(MOD(LN(AI264/10)/LN(2),1),AI264,5)

Essentially, if your value is not exactly 10 times a power of 2, show the value, otherwise show 5.

CodePudding user response：

or try long way:

=IF(REGEXMATCH(AI1264&"", "10|20|40|80|160|320|640|1280|2560|5120|10240|20480"), 5, AI1264)

CodePudding user response：

another way would be:

=INDEX(IFNA(VLOOKUP(A1, 
 {10*2^SEQUENCE(12, 1, ), SEQUENCE(12, 1, 5, )}, 2, ), A1))

or reverse:

=INDEX(IFNA(VLOOKUP(AI1264, 10*2^SEQUENCE(12, 1, ), 1, ), 5))