to many ifs

[mamfa]

i need some help as i have too many if functions with the formula i need to write. this is what the complete formula should be:
=IF(AB2="A*",0,IF(AB2="A",1,IF(AB2="B",2,IF(AB2="C",3,IF(AB2="D",4,IF(AB2="E",5,IF(AB2="F",6,IF(AB2="G",7,IF(AB2="U",8,"NA")))))))

it will cope until the last if but then it says theres to many i know abit about naming formulas but cant get that to work, and dumbed down help would be much appreciated

thanks
Samantha

 

[mamfa]

that has worked brilliantly thankyou!! is there any chance you could give mem an explanation of what the formula is saying so that i can use it in other areas

many thanks

 

[Aladin Akyurek]

that has worked brilliantly thankyou!! is there any chance you could give mem an explanation of what the formula is saying so that i can use it in other areas

many thanks

In

=MATCH(AB2,{"A*","A","B","C","D","E","F","G","U"},0)-1

the vector (or array constant as it is often referred to)

{"A*","A","B","C","D","E","F","G","U"}

has 9 items, which are implicitly indexed as 1, 2, 3,...,9. At index/position 1 we have A*, at 2 A, etc.

MATCH() always returns a position after a hit, otherwise #N/A.

If AB2 houses C, AB2 will match 4th item in the vector, so the MATCH bit will return 4. Since there is a correspondance between the values you assign to A*, A, B,... and the index values of 1, 2, 3, 4, etc.:

0,1,2,3,4,5,6,7,8
1,2,3,4,5,6,7,8,9

we have:

MATCH(...)-1

==> 4-1

==> 3

which is the value you assign to C.

 

[SidBord]

Another technique you can use to perform conversions is to use SELECT CASE logic:

Select Case AB2
   Case "A*":   Value = 0
   Case "A":     Value = 1
   Case "B":     Value = 2
 '. . .
   Case "U":     Value = 8
End Select