Function for changing different sets of values to specific values (categorization)

[BoloRen]

Hey everyone. I need some help, please.

We have two tables, but let's just look at one cell in each table: A1 and D1.

A1 can only be 0, 1, 2, 3, 4, 5, or 6, and I want D1 to display a specific value depending on the input value in A1.

Specifically, I want D1 to function like this:
If A1 is 0, 1, 2 or 3, D1 should be 0.
If A1 is 4, D1 should be 1.
If A1 is 5, D1 should be 2.
If A1 is 6, D1 should be 3.

I want all of these conditions to be embedded in one cell (D1).

Here is my attempt:

=IF(A1="0","0",IF(A1="1","0",IF(A1="2","0",IF(A1="3","0",IF(A1="4","1",IF(A1="5","2",IF(A1="6","3")))))))

There seems to be something wrong with this formula, however. No matter what value is entered into A1, D1 will always return "FALSE".

Any suggestions?

 

[Jon von der Heyden]

Try:
=XLOOKUP(A1,{0;4;5;6},{0;1;2;3},,-1,2)

 

[Jon von der Heyden]

Better yet: =MAX(A1-3,0)

 

[BoloRen]

Thanks for the quick help, Jon von der Heyden.