help with an if statement

[clegge]

1) basically would like to get the following statement to work:
=if(foo!H2=ssbs,"ssbs2","foo'!H2")
basically if fooH2 contains the value SSBS i need to return SSBS2 if it does not i need to return the value that is presently in fooH2

2) if possiple i would like to make it a compound if ..
if foo!H2=ssbs, then return ssbs2, if foo!H2 = x then return x2, if foo!H2=x then return x3

thank you in advance for all the help....

[BenjieLop]

<< 1) basically would like to get the following statement to work:
=if(foo!H2=ssbs,"ssbs2","foo'!H2")
basically if fooH2 contains the value SSBS i need to return SSBS2 if it does not i need to return the value that is presently in fooH2 >>

=if(foo!H2="ssbs","ssbs2",foo!H2)

<< 2) if possiple i would like to make it a compound if ..
if foo!H2=ssbs, then return ssbs2, if foo!H2 = x then return x2, if foo!H2=x then return x3 >>

There is something wrong with the logic here. If foo!H2=x, it can only return either x2 or x3 but not both. You have to pick one and only one option if foo!H2=x.

Anyway, this formula

=if(foo!h2="ssbs","ssbs2",if(foo!H2="x","x2",any_value)

will return

"ssbs2" if foo!H2="ssbs"
"x2" if foo!H2="x"

and will return any value if foo!H2 is neither "ssbs" nor "x"

Regards.

[clegge]

your right - i did not describe the scenario correctly... basically want a formula that will return
ssbs2 if there is an ssbs value in h2..
an empty cell if there is a value of x in h2
an empty cell if there is a value of y in h2
an empty cell if there is a value of z in h2
if there is any other vaule in h2 i want the formula to show that value....

hard to exlain... sory about that


thanks again....

[BenjieLop]

your right - i did not describe the scenario correctly... basically want a formula that will return
ssbs2 if there is an ssbs value in h2..
an empty cell if there is a value of x in h2
an empty cell if there is a value of y in h2
an empty cell if there is a value of z in h2
if there is any other vaule in h2 i want the formula to show that value....

hard to exlain... sory about that


thanks again....

=if(h2="ssbs","ssbs2",if(or(h2="x",h2="y",h2="z"),"",h2))

and if "h2" is the same as "foo!h2" in your original message, then simply replace all "h2" with "foo!h2" in the above formula.

Regards.

[clegge]

i think i wast trying to make things to complicated... i ended up using a vlookup and it worked pretty well... i should have thought of that from the start...

i am really bad at describing what i need to get done but here goes...

i am doing a vlookup... basically the criteria in a1 is used against column b, and if there is a hit, the corresponding value in column c is displayed... is ther anyway to have this lookup just return the originaly criteria a1 if nothing matches in column b