Too much nesting in if statement

[Dean]

This is a plain EXCEL question, no VBA solutions please!

I want to say, if cell1 value= cell 2 value, then set a certain cell equal
to cell2Avlaue
If not, then if it's equal to cell 3 value, then set this certain cell equal
to cell3Avlaue,
etc.

Since the cell I'm testing is always the same cell, is there a way to do
this without a big multi-nested if statement?

I'm afraid that the # of characters will be too long, since there are many
possibilities. Also, it seems embarrassingly inelegant to my user, even if
not. In case it matters, each value is distinct, so it can only equal one
or the other.

I think I could do this with lookup functions somehow, but don't like that
complexity either, as there will be thousands of them since I will repeat
this equation many times.

Thanks!
Dean

 

[Guest]

I would try and use vlookup.

If you have different conditions for different cells you could use a
combinded key for the vlookup

as in lookupsheet:
[A1:B1]="A1:"&sheet1!c1,sheet1!d1
[A2:B2]="A1:"&sheet1!c2,sheet1!d2
[A3:B3]="A1:"&sheet1!c3,sheet1!d3
[A4:B4]="A1:"&sheet1!c4,sheet1!d4
[A5:B5]="A2:"&sheet1!e1,sheet1!f1
[A6:B6]="A2:"&sheet1!e2,sheet1!f2
[A7:B7]="A2:"&sheet1!e3,sheet1!f3
[A8:B8]="A2:"&e4,f4

then use vlookup("A1")&x1,'lookupsheet'!$a$1:$b$8, 2, false)
its also a lot easier to maintain

 

[Dean]

Interesting approach! Let me give it some thought.

Thanks!
Dean

I would try and use vlookup.

If you have different conditions for different cells you could use a
combinded key for the vlookup

as in lookupsheet:
[A1:B1]="A1:"&sheet1!c1,sheet1!d1
[A2:B2]="A1:"&sheet1!c2,sheet1!d2
[A3:B3]="A1:"&sheet1!c3,sheet1!d3
[A4:B4]="A1:"&sheet1!c4,sheet1!d4
[A5:B5]="A2:"&sheet1!e1,sheet1!f1
[A6:B6]="A2:"&sheet1!e2,sheet1!f2
[A7:B7]="A2:"&sheet1!e3,sheet1!f3
[A8:B8]="A2:"&e4,f4

then use vlookup("A1")&x1,'lookupsheet'!$a$1:$b$8, 2, false)
its also a lot easier to maintain
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

This is a plain EXCEL question, no VBA solutions please!

I want to say, if cell1 value= cell 2 value, then set a certain cell
equal
to cell2Avlaue
If not, then if it's equal to cell 3 value, then set this certain cell
equal
to cell3Avlaue,
etc.

Since the cell I'm testing is always the same cell, is there a way to do
this without a big multi-nested if statement?

I'm afraid that the # of characters will be too long, since there are
many
possibilities. Also, it seems embarrassingly inelegant to my user, even
if
not. In case it matters, each value is distinct, so it can only equal
one
or the other.

I think I could do this with lookup functions somehow, but don't like
that
complexity either, as there will be thousands of them since I will repeat
this equation many times.

Thanks!
Dean