if statements with multiple returns

[Guest]

Hi someone,

I'm trying to find and return specific text (the 14th and 15th character)
from a cell, but only if its one of 6 different values (ex. "ip" or "iv")

can someone provide a formula that could return only these 2 values, and i
should be ok to replicate it from there??

Thanks,

 

[Guest]

=IF(LEFT(A1,14)="i",CONCATENATE(LEFT(A1,14),LEFT(A1,15),"")

Dave

 

[Guest]

Hi Dave,

Didn't work for me... how about this way..this returns ip..

=IF(ISNUMBER(SEARCH("IP",F3)),"IP","")

I just can't figure out how to enter multiple values to get mulitple returns.

Ryan

 

[Guest]

i think i am confused but i will guess this way..
lets say your lookup table is in i1:i6 (e.g. sorted like ia,ib,ie,ii,jo,yz)
and the text is on cell L9 : BATMAN_AND_ROIBN
14th and 15th is "IB"
the formula guess is
=IF(ISERROR(MATCH(MID(L9,14,2),I1:I6,0)),"no match",MID(L9,14,2))

regards to robin

 

[Guest]

Try something like this:

With
A1: (the text string to test)

This formula returns the 14th and 15th characters from the string, but only
if they match either "IV" or "IP"
B1: =IF(SUM(COUNTIF(A1,REPT("?",13)&{"IV","IP"}&"*")),MID(A1,14,2),"missing")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Pete_UK]

Your data seems to be in F3, so try this:

=IF(OR(MID(F3,14,2)="IP",MID(F3,14,2)="IV"),MID(F3,14,2),"not present")

You can change the "not present" message to something more to your
liking, then copy the formula down if you have similar strings to test
below F3.

Hope this helps.

Pete

 

[Biff]

Another way to write that:

=IF(OR(MID(F3,14,2)={"IP","IV"}),MID(F3,14,2),"not present")

Biff

 

[Aladin Akyurek]

Assuming that the string of interest is in A2 and the list of specific
text bits are (adjust to suit): "ip","iv","dp","dv","fi",and "xi"...

Try in B2:

=LOOKUP(9.99999999999999E+307,SEARCH({"Not
Found","ip","iv","dp","dv","fi","xi"},"Not Found"&MID(A2,14,2)),
{"Not Found","ip","iv","dp","dv","fi","xi"})

Note "Not Found" which is added to the preset list of bits of text.