Formula help for IF, OR, LEN(), LEFT()

**winstontj** · 09-05-2009, 06:10 PM

Trying to use one formula for a variety logical tests. Is it possible to write:

=IF(Logical Test, Value if True, Value if False)

=If(A1=("ABC" OR "BCD" OR "CDE" OR "DEF"),("ABC"="123","BCD"="234","CDE"="345","DEF"="456"),"IT'S BROKEN")

If you view the attached spreadsheet and see my progression and the end result it will probably make more sense. I also may need to use IF LEFT() for the final data I'm working with.

I'm totally stumped on this one.

Thx for any help. This forum is awesome!!!

Running XP Pro/Excel 2003 (have access to 2007)

**martindwilson** · 09-05-2009, 07:19 PM

are you really working with these values or is that just an example?
i suspect your real data is nothing like that. but for step 1
=IF(ISNA(MATCH(A3,{"abc","bcd","cde","def"},0)),"it's broken",INDEX({123,234,345,456},MATCH(A3,{"abc","bcd","cde","def"},0)))
as you can see you have to pue all values in the function
so a beter approach would be to use a "data" sheet
in a1 down put abc,cde and so on with the corresponding values in b1 down
then use a look up of some sort
say vlookup(a3,data!a1:a100,2,false)
your working example doesnt include any examples that are "broken"

**winstontj** · 09-05-2009, 08:19 PM

The data is formatted exactly as I displayed in the "working data" section but the values are different. I download files from an FTP site every day that are formatted this way - I am trying to keep my manual process down to simply downloading new data sheets every day rather than to open, format and save as, etc.

I can't provide my real working data as some of it is sensative but the examples I gave were very similar.

Also,I didn't have any "broken" formulas because I didn't even know where to start writing the formula.

Thx for the reply, I will test this right away.

**winstontj** · 09-05-2009, 08:38 PM

Here is another way to ask this:

Can I string several of these along? Such that I could have a set of 22 different IF statements in a single cell/formula? I also have an external web query that pulls some of this information into excel which has XYZ and what it should equal

=IF(A7="XYZ",IF(LEFT(C7,6)="RGLGLL","RGLGLL",C7),C7)

The above works for me on a one-by one basis but I'd like to incorporate all 22 different variations of this into a single formula.

**daddylonglegs** · 09-05-2009, 08:54 PM

Hello winstontj,

Excel 2003 is limited to 7 nested functions, although there are ways round that it's probably better to use some sort of table as Martin suggests.

You say you have 22 variations....of what? is the "XYZ" part static with 22 variations on "RGLGLL"? or is it the other way round (or do they mix and match?)

You can probably still use VLOOKUP even if you are examining the first 6 characters, i.e. you can use LEFT(C7,6) as the lookup value.

If you want a more specific answer, rather than general advice, then you'll need to provide specific examples of what you want to do. Of course you can change the data but you need to be precise about the nature of the problem