CODEWITHSUNDEEP
excelexcel-formula
Midlife Crisis
Midlife Crisis

Reputation: 11

the OR function always trips me up

I've always had trouble with this one over the years! I don't seem able to get the OR function in Excel to work. In my model, the cell E46 should contain either a valid Postcode or the value "none". Unfortunately I have also encountered a "#value" in E46 so have written the following formula to trap this error and return "None" if encountered:

=IF(OR(E46="None",ISERROR(E46)),"None",VLOOKUP(E46, List,2,FALSE))

Trouble is, I still get #value returned by this formula when E46 contains #value even though I think I am trapping it!

All the help texts suggest I have written the formula correctly – I cannot see what I am doing wrong. Any helpful suggestions gratefully received.

Upvotes: 0

Views: 60

Answers (1)

barry houdini
barry houdini

Reputation: 46361

You can't use OR when there might be an error in there (because the OR function will give an error in that case - from E46="None" part), try trapping the error first with nested IFs, e.g.

=IF(ISERROR(E46),"None",IF(E46="None",E46,VLOOKUP(E46, List,2,FALSE)))

You'd still get an error if E46 isn't in List......

If you have Excel 2007 or greater you can use IFERROR like

=IFERROR(IF(E46="None",E46,VLOOKUP(E46, List,2,FALSE)),"None")

Upvotes: 3

Related Questions