Including ISERROR - Multiple IF/VLOOKUP formula

[okjam]

Hi,
I will shorten this to not be to long of a read, have a formula which is like this;

=IF(A1="YES",VLOOKUP(TABLE1-ETC)),IF(A1="OK",VLOOKUP(TABLE2-ETC),IF(A1="NO",VLOOKUP(TABLE3-ETC))))
(this a non detailed/wrong version of the formula - the real one works fine etc.

The problem I is that when it VLOOKUP's on a table, if it doesn't find what im looking up it returns the #N/A error.

Normally, when i would have a single vlookup string, i would use;
=IF(ISERROR(VLOOKUP(TABLE1-ETC),"OK",VLOOKUP(TABLE1-ETC))) --- So if it couldn't find what I want, it would return OK into the field.
This is also fine.


What I don't know is how to include the ISERROR into the top string;
=IF(A1="YES",VLOOKUP(TABLE1-ETC)),IF(A1="OK",VLOOKUP(TABLE2-ETC),IF(A1="NO",VLOOKUP(TABLE3-ETC))))

Or, is there a more suitable way for me to do this so it still will lookup against the correct table depending on whats in A1, but if it cant find whats being looked up it returns a "OK" (or a 0)

Any help appreciated
Thanks

[DBY]

Hello
Unless I'm missing something, I think you just need to wrap the ISERROR function around your formula. e.g.

=ISERROR(IF(A1="YES",VLOOKUP(TABLE1-ETC)),IF(A1="OK",VLOOKUP(TABLE2-ETC),IF(A1="NO",VLOOKUP(TABLE3-ETC)))),"OK")

If no value is found it returns "OK".

Hope this helps

[okjam]

Thanks for your reply,
Just tried this
=ISERROR(IF(A1=1,VLOOKUP(A5,'s3'!A1:B9,2,FALSE),IF(A1=2,VLOOKUP(A5,'s2'!H2:I10,2,FALSE),IF(A1=3,VLOOKUP(A5,'s2'!H2:H10,2,FALSE),"OK"))))

Basically now, if it gives an error the cell is just TRUE or FALSE rather than the OK if it doesnt find the result in any of the 3 vlookups :S

Need some more help!

[DBY]

Hello
Not sure why you've got the 3 extra brackets at the end of the formula. It should just be your original formula inside the ISERROR function as in my example. Is your formula without the ISERROR working correctly?

[okjam]

Hi,
Excel says theres a problem with the formula if I try putting the OK elsewhere, i moved it around until it actually allowed the formula.

Yes it works fine if i take out the ,"OK"
=(IF(A1=1,VLOOKUP(A5,'s3'!A1:B9,2,FALSE),IF(A1=2,VLOOKUP(A5,'s2'!H2:I10,2,FALSE),IF(A1=3,VLOOKUP(A5,'s2'!H2:H10,2,FALSE)))))

[Dionysos]

=IF(ISERROR(formula)=TRUE,"OK",formula)

[DBY]

Hi
Just realized we're using the wrong function. Sorry, silly of me. You need to use the IFERROR function not ISERROR. Then my original answer should work.

Sorry again.

[okjam]

DBY - PERFECT!!!!
Thank you so much.

Just for people who may read this in the future - completed formula looks like this;

=IFERROR(IF(A1=1,VLOOKUP(A5,'Sheet1'!A1:B9,2,FALSE),IF(A1=2,VLOOKUP(A5,'Sheet2'!H2:I10,2,FALSE),IF(A1=3,VLOOKUP(A5,'Sheet3'!H2:H10,2,FALSE)))),"OK")