Using multiple IF Statements

[cimajc17]

Hey,
I'm trying to use the following if statement to create a category column.
=IF(VLOOKUP(E4,Med,1,FALSE)<>"#N/A","Medical",IF(VLOOKUP(E4,Surgery,1,FALSE)<>"#N/A","Surgery"," "))

But the second statement isn't working. Any suggestion.

 

[Joe4]

If the #N/A is the standard Excel error (and not literal text), you cannot check/compare it like that.
Try:

[COLOR=#333333]=IF(ISNA(VLOOKUP(E4,Med,1,FALSE)),"Medical",IF(ISNA(VLOOKUP(E4,Surgery,1,FALSE)),"Surgery"," "))[/COLOR]

 

[puru.sve]

=IF(ISERROR(VLOOKUP(E4,Med,1,FALSE))= FALSE,"Medical",IF(ISERROR(VLOOKUP(E4,Surgery,1,FALSE)) =FALSE,"Surgery"," "))

try it

 

[jaapfrank]

Hi i try'd it over here. And it should be:

=IF(VLOOKUP("Med", E4,1,FALSE)<>"#N/A","Medical",IF(VLOOKUP("Surgery",E4,1,FALSE)<>"#N/A","Surgery"," "))

Cheers.

 

[Joe4]

=IF(VLOOKUP("Med", E4,1,FALSE)<>"#N/A","Medical",IF(VLOOKUP("Surgery",E4,1,FALSE)<>"#N/A","Surgery"," "))

I believe that will only work is "#N/A" is a literal text value that has been entered, and not the #N/A error that formulas sometimes return.

 

[cimajc17]

Thank you it works

 

[cimajc17]

I was trying to add to more categorize but i only get Medical, Surgery categorize.
=IF(ISNA(VLOOKUP(E4,Med,1,FALSE)),"Medical",IF(ISNA(VLOOKUP(E4,Surgery,1,FALSE)),"Surgery",IF(ISNA(VLOOKUP(E4,ICU,1,FALSE)),"ICU",IF(ISNA(VLOOKUP(E4,Transplant,1,FALSE)),"Transplant"," "))))

 

[Joe4]

Can you describe your named ranges (Med, Surgery,ICU, and Transplant), their structure and contents, and what is in E4?

 

[cimajc17]

This is what Med looks like and formatted as text :
[TABLE="width: 213"]
<colgroup><col></colgroup><tbody>[TR]
[TD]MED, CARD HEART RHYTHM[/TD]
[/TR]
[TR]
[TD]MED, CARD ISCHEMIC HEART[/TD]
[/TR]
[TR]
[TD]MED, CARD VALVE STRUCTURE[/TD]
[/TR]
[TR]
[TD]MED, CARDIOLOGY 1[/TD]
[/TR]
[TR]
[TD]MED, CARDIOLOGY 2[/TD]
[/TR]
[TR]
[TD]MED, CARDIOLOGY 3

This is ICU
[TABLE="width: 174"]
<colgroup><col></colgroup><tbody>[TR]
[TD]MED, CRIT MICU[/TD]
[/TR]
[TR]
[TD]MED, CRIT MICU 1[/TD]
[/TR]
[TR]
[TD]MED, CRIT MICU 2

Transplant
[TABLE="width: 200"]
<colgroup><col></colgroup><tbody>[TR]
[TD]TRANSPLANT, KIDNEY/PANCR[/TD]
[/TR]
[TR]
[TD]TRANSPLANT, LIVER

Surgery
[TABLE="width: 187"]
<colgroup><col></colgroup><tbody>[TR]
[TD]SURG, ORTHO TRAUMA 1[/TD]
[/TR]
[TR]
[TD]SURG, ORTHO TRAUMA 2[/TD]
[/TR]
[TR]
[TD]SURG, ORTHO TRAUMA 3[/TD]
[/TR]
[TR]
[TD]SURG, ORTHO TRAUMA 4

In Column E is a combination all the things in the named ranges[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

OK. I think I may have designed your formula backwards, as you were originally wanting to check for NOT equal to #N/A, and my formula is checking for #N/A.

Are all these named ranges in the same area (above/below each other), or are the in separate locations? If they are in the same area, you may only need one VLOOKUP, instead of nesting them. Otherwise, I think you will need to modify the formula like this:

[COLOR=#333333]=IF(NOT(ISNA(VLOOKUP(E4,Med,1,FALSE))),"Medical",IF(NOT(ISNA(VLOOKUP(E4,Surgery,1,FALSE))),"Surgery",IF(NOT(ISNA(VLOOKUP(E4,ICU,1,FALSE))),"ICU",IF(NOT(ISNA(VLOOKUP(E4,Transplant,1,FALSE))),"Transplant"," "))))[/COLOR]