Choose from multiple formulas

[2077delta]

Is there a simpler way than using a nested if statement to apply a formula based on the result of a cell? For example, in Cell A3 I have a formula that evaluates the value in Cell A1 and applies a formula that extracts text in Cell A2 depending on the value in Cell A1. Something like =function name(value of A1, formula1, formula2, formula3, formula4, formula5). Similar to the choose function only using formulas based on the index value instead of values. Is building a VBA function my only option?

 

[Cubist]

Try =SWITCH()

 

[Eric W]

Depending on your situation, either IFS or SWITCH could work.

Examples:

=IFS(A1=1,MID(A2,2,3),A1=2,LEFT(A2,4),A1=3,RIGHT(A2,4),TRUE,"invalid value")

=SWITCH(A1,1,LEFT(A2,1),2,MID(A2,2,2),3,RIGHT(A2),"oops")

 

[2077delta]

Depending on your situation, either IFS or SWITCH could work.

Examples:

=IFS(A1=1,MID(A2,2,3),A1=2,LEFT(A2,4),A1=3,RIGHT(A2,4),TRUE,"invalid value")

=SWITCH(A1,1,LEFT(A2,1),2,MID(A2,2,2),3,RIGHT(A2),"oops")

Thanks Eric, IFS was the answer I was looking for. Here's my formula
=LET(y,XLOOKUP(B4,src_names,src_index),z,V4,IFS(y=1,TEXTBEFORE(TEXTAFTER(z,"`",1),"",1),y=2,TEXTBEFORE(TEXTAFTER(z,"`",1),"",1),y=3,TEXTBEFORE(TEXTAFTER(z,"",1),":",1),y=4,TEXTBEFORE(TEXTAFTER(z,"`",1),"",1),y=5,TEXTBEFORE(TEXTAFTER(z,"`",1),"",1),y=6,TEXTBEFORE(TEXTAFTER(z,"`",1),"",1)))
When our accounting system exports it concatenates a bunch of fields together and inserts these awkward symbols so I have to split up a long text string so we can work with the data.

 

[Eric W]

Glad we could help.