Inserting a value based on another value

[AAbrams2008]

I have a place where the value of A2 can be A,B,C,D,E,H,J,K,L,M,S, or null.
Based in the value entered in this cell, I want O2 to take the value of
Z2,Z3,Z4,Z5,Z6,Z7,Z8,Z9,Z10,Z11,Z12, or 0. I need to calculate this for all
row values of the column O. It's too complex for a nested if/then loop, so I
think I need to either insert a custom macro or embed a VB function, however
both of these are things I don't have a lot of experience with. What do I
need to do?

Thanks!

 

[Rick Rothstein]

Put this formula in O2...

=IF(A2="","",SEARCH(A2,"ABCDEHJKLMS"))

Note: You didn't say what you wanted to happen if A2 was not empty (or, as
you say, "null"), but also is not one of the letters you listed... the above
formula will generate a #VALUE! error for that condition.

 

[AAbrams2008]

The value error is fine - these are the only things that should be in the
field. I don't understand where this tells me to pull the value from the Z
column of values.

 

[AAbrams2008]

I don't think I was too clear. What I'm trying to return is:

If the value inserted in A2 is then the value in O2 should be

A the value from
cell Z2
B the value from
cell Z3
C the value from
cell Z4
null 0

Sorry about my lack of clarity

 

[Rick Rothstein]

No, you were clear... I just forgot to complete the formula before I posted
it. Here is what I had intended to post...

=IF(A2="","",INDEX(Z:Z,SEARCH(A2,"ABCDEHJKLMS")))

Sorry for any confusion.

 

[Rick Rothstein]

Sorry, I need to make a correction (you are starting from Z2, not Z1). Use
either this formula...

=IF(A2="","",INDEX(Z:Z,1+SEARCH(A2,"ABCDEHJKLMS")))

where the addition of the 1 is meant to adjust the offset for index to match
its starting point one cell down from Z1 which is where Z:Z starts from), or
you can use this formula...

=IF(A2="","",INDEX(Z2:Z100,SEARCH(A2,"ABCDEHJKLMS")))

where you would set the Z100 reference to the last possible cell in Column Z
that will hold data (probably Z12 for the data you posted).

 

[AAbrams2008]

Thank you - it's perfect.

Sorry, I need to make a correction (you are starting from Z2, not Z1). Use
either this formula...

=IF(A2="","",INDEX(Z:Z,1+SEARCH(A2,"ABCDEHJKLMS")))

where the addition of the 1 is meant to adjust the offset for index to match
its starting point one cell down from Z1 which is where Z:Z starts from), or
you can use this formula...

=IF(A2="","",INDEX(Z2:Z100,SEARCH(A2,"ABCDEHJKLMS")))

where you would set the Z100 reference to the last possible cell in Column Z
that will hold data (probably Z12 for the data you posted).