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 :

MED, CARD HEART RHYTHM

MED, CARD ISCHEMIC HEART

MED, CARD VALVE STRUCTURE

MED, CARDIOLOGY 1

MED, CARDIOLOGY 2

MED, CARDIOLOGY 3 This is ICU MED, CRIT MICU MED, CRIT MICU 1 MED, CRIT MICU 2 Transplant TRANSPLANT, KIDNEY/PANCR TRANSPLANT, LIVER Surgery SURG, ORTHO TRAUMA 1 SURG, ORTHO TRAUMA 2 SURG, ORTHO TRAUMA 3 SURG, ORTHO TRAUMA 4 In Column E is a combination all the things in the named ranges <colgroup><col></colgroup><tbody> </tbody> <colgroup><col></colgroup><tbody> </tbody> <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

 

[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]