Multiple IF Statements Excel 2007

Question

I have a vector that has different values in it. In the vector next to it I would like to use multiple =IF(FIND(IF(FIND(),IF(FIND(),..... statements.

Here is an example of my data and how I would like the cells next to it to come out:

COMPLAINT          | DESCRIPTION <-- How I would like it to come out
CHF                | CHF
COPD (Nos)         | COPD
Chest, Pain, Acute | CHEST PAIN
CP                 | CHEST PAIN
...                | ...

What I have written for this is as follows, which does not work:

=IF(FIND("CHF",E3,1),"CHF",IF(FIND("COPD",E3,1),"CHF",0))

I need to expand the above to accept at least 5 different arguments. I tried using this post here but I am not following it right.

EDIT

I expect the function to work something like this:

IF FIND "CHF"
  THEN "CHF"
ELSE IF FIND "COPD"
  THEN "COPD"
ELSE IF FIND "CP"
  THEN "CHEST PAIN"
...

Thank you,

In what way(s) does it not work? How does the result differ from what you expected? — Scott Hunter
– Scott Hunter, Commented Dec 12, 2013 at 19:40
I need to add what I expect to my question I apologize, what I need is if "CHF" is found THEN "CHF" but if "COPD" is found THEN "COPD" but if "CP" is fond THEN "CHEST PAIN",...,etc. — MCP_infiltrator
– MCP_infiltrator, Commented Dec 12, 2013 at 19:48

Jerry · Accepted Answer · 2013-12-12 20:10:06Z

2

I think that's it's better if you have some sort of 'dictionary' to look up the values.

For example, have a table with the following somewhere:

CHF          CHF
COPD         COPD
CP           CHEST PAIN
Chest Pain   CHEST PAIN

Let's say those are in the range G2:H5 and that CHF begins in cell A2.

The first step is to try match CHF, COPD, CP and Chest Pain to the cell A2. You can use SEARCH for this (FIND is equivalent but case sensitive).

=SEARCH($G$2:$G$5,A2)

This gives you errors and a number in case there's a match. To deal with your 3rd sample case, you can first remove the commas with a SUBSTITUTE:

=SEARCH($G$2:$G$5,SUBSTITUTE(A2,",",""))

For CHF, you'll get

 1,#VALUE!,#VALUE!,#VALUE!

Using an IFERROR() can make the results a little prettier. You can get the index 1 from that, to get CHF from the second table of the lookup table using a MATCH:

=MATCH(1,SEARCH($G$2:$G$5,SUBSTITUTE(A2,",","")),0)

And use INDEX to bring back the value of the cell from the lookup table...

=INDEX($H$2:$H$5,MATCH(1,SEARCH($G$2:$G$5,SUBSTITUTE(A2,",","")),0))

You can also change the size of your lookup table to accommodate any number of values to lookup. The only downside is that it'll be at the expense of performance, but that's only natural if Excel has more values to check.

Now, because of the SEARCH (it takes in multiple values instead of a single one), this formula is an array formula. As such, instead of pressing Enter, you'll have to hold Ctrl+Shift and then press Enter for it to work properly.

edited Dec 12, 2013 at 20:10

answered Dec 12, 2013 at 19:45

Jerry

71.8k14 gold badges106 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MCP_infiltrator Over a year ago

Thank you much for this one. I'm out for now but will try this in the morning and if it works then I'll accept it as the answer.

Jerry Over a year ago

@MCP_infiltrator Okay, that's fine with me. I actually forgot to mention that it was an array formula, so I added that bit at the end. ^^;

Collectives™ on Stack Overflow

Multiple IF Statements Excel 2007

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related