IFS

[Jewells0905]

I am trying to translate the numbers from column D to text strings as follows: 0 = "A", 1 = "A#/Bb", 2 = "B", 3 = "C", 4 = "C#/Db", 5 = "D", 6 = "D#/Eb", 7 = "E", 8 = "F", 9 = "F#/Gb", 10 = "G", and 11 = "G#/Ab", into column E. This is the formula I'm using, which is not giving an error, but not quite correct. I'm getting A#Bb for all of them.

=IFS( D2= 0, "A", 1, "A#/Bb", 2, "B", 3, "C", 4,"C#/Db", 5, "D", 6, "D#/Eb", 7, "E", 8, "F", 9, "F#/Gb", 10, "G",11, "G#/Ab")

Year	Title	Artist	Key Orig	Key	Top 10
2010	This Is the House That Doubt Built	A Day to Remember	11	A#/Bb	0
2010	Sticks & Bricks	A Day to Remember	10	A#/Bb	0
2010	All I Want	A Day to Remember	2	A#/Bb	0
2010	It's Complicated	A Day to Remember	1	A#/Bb	0

 

[awoohaw]

try using SWITCH instead:

edited:

Book2
	A	B	C	D	E	F	G	H	I	J
1	Year	Title	Artist	Key Orig	Key	Top 10
2	2010	This Is the House That Doubt Built	A Day to Remember	0	A#/Bb	0				A
3	2010	Sticks & Bricks	A Day to Remember	1	A#/Bb	0				A#/Bb
4	2010	All I Want	A Day to Remember	2	A#/Bb	0				B
5	2010	It's Complicated	A Day to Remember	3	A#/Bb	0				C
6				4						C#/Db
7				5						D
8				6						D#/Eb
9				7						E
10				8						F
11				9						F#/Gb
12				10						G
13				11						G#/Ab
14				12						Error
Sheet2

Cell Formulas
Range	Formula
J2:J14	J2	=SWITCH(D2,0,"A",1,"A#/Bb",2,"B",3,"C",4,"C#/Db",5,"D",6,"D#/Eb",7,"E",8,"F",9,"F#/Gb",10,"G",11,"G#/Ab","Error")

 

[Jewells0905]

try using SWITCH instead:

edited:

Book2
	A	B	C	D	E	F	G	H	I	J
1	Year	Title	Artist	Key Orig	Key	Top 10
2	2010	This Is the House That Doubt Built	A Day to Remember	0	A#/Bb	0				A
3	2010	Sticks & Bricks	A Day to Remember	1	A#/Bb	0				A#/Bb
4	2010	All I Want	A Day to Remember	2	A#/Bb	0				B
5	2010	It's Complicated	A Day to Remember	3	A#/Bb	0				C
6				4						C#/Db
7				5						D
8				6						D#/Eb
9				7						E
10				8						F
11				9						F#/Gb
12				10						G
13				11						G#/Ab
14				12						Error
Sheet2

Cell Formulas
Range	Formula
J2:J14	J2	=SWITCH(D2,0,"A",1,"A#/Bb",2,"B",3,"C",4,"C#/Db",5,"D",6,"D#/Eb",7,"E",8,"F",9,"F#/Gb",10,"G",11,"G#/Ab","Error")

worked perfectly! Thank you!

 

[awoohaw]

My pleasure, happy to help. Best wishes!

 

[Peter_SSs]

worked perfectly! Thank you!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

I am not a fan of the IFS function at all but for the record the reason your formula was not working was because you omitted the D2 reference from all the tests except the first one.
IFS could have worked, as shown in col K below.
A more compact way to do this though is shown in Col J

24 04 02.xlsm
	D					I	J	K	L
1	Key Orig
2	0						A	A	A
3	1						A#/Bb	A#/Bb	A#/Bb
4	2						B	B	B
5	3						C	C	C
6	4						C#/Db	C#/Db	C#/Db
7	5						D	D	D
8	6						D#/Eb	D#/Eb	D#/Eb
9	7						E	E	E
10	8						F	F	F
11	9						F#/Gb	F#/Gb	F#/Gb
12	10						G	G	G
13	11						G#/Ab	G#/Ab	G#/Ab
Key

Cell Formulas
Range	Formula
J2:J13	J2	=INDEX({"A","A#/Bb","B","C","C#/Db","D","D#/Eb","E","F","F#/Gb","G","G#/Ab"},D2+1)
K2:K13	K2	=IFS( D2= 0, "A", D2=1, "A#/Bb", D2=2, "B", D2=3, "C", D2=4,"C#/Db", D2=5, "D", D2=6, "D#/Eb", D2=7, "E", D2=8, "F", D2=9, "F#/Gb", D2=10, "G",D2=11, "G#/Ab")
L2:L13	L2	=SWITCH(D2,0,"A",1,"A#/Bb",2,"B",3,"C",4,"C#/Db",5,"D",6,"D#/Eb",7,"E",8,"F",9,"F#/Gb",10,"G",11,"G#/Ab","Error")