Better way required to write 1 formula

[hsandeep]

Any better 'way' to write the below formula:

=IF(AGG2=100,"i",IF(AGG2=200,"j",IF(AGC2=100,"p",IF(AGC2=-100,"q",IFERROR(CHOOSE(B2,"J","K","L","M"),0)))))

Thanks in adv.

 

[Jeffrey Mahoney]

I'm just giving you choices. If you want to create a user defined function instead, it's easier to read and easier to make changes.

Function GetChoice(R1 As Range, R2 As Range, R3 As Range) As Variant  

  
  

  If R1.Value = 100 Then
    GetChoice = "i"
  ElseIf R1.Value = 200 Then
    GetChoice = "j"
  ElseIf R2.Value = 100 Then
    GetChoice = "p"
  ElseIf R2.Value = -100 Then
    GetChoice = "q"
  ElseIf R3.Value = 1 Then
    GetChoice = "j"
  ElseIf R3.Value = 2 Then
    GetChoice = "k"
  ElseIf R3.Value = 3 Then
    GetChoice = "l"
  ElseIf R3.Value = 4 Then
    GetChoice = "m"
  Else
    GetChoice = 0
  End If
  
End Function

The cell formula would look something like this: =GetChoice(AGG2,AGC2,B2)

 

[Jeffrey Mahoney]

Add this line to the code if you want it to always refresh with cell changes: Application.Volatile

 

[hsandeep]

Add this line to the code if you want it to always refresh with cell changes: Application.Volatile

Formula needed instead of UDF.

 

[hsandeep]

I want to use 'another' formula for the 'first 2' IFs (in the formula).