VLOOKUP and conditional formating

Hello, I am trying to set up conditional formating based on a VLOOKUP. But I also want it to format if an error is returned. So I have the formula below that works. It lists a 1 or a 2 depending on the values and the conditional formating is based on that. But I want it to list a 0 if an error is returned. I know I will use the ISERROR function, but can't get it to work. Any help? Thanks

=IF((VLOOKUP(V13,'[Master Price Guide-Edited.xls]Sysco'!$B8:$J$196,9,FALSE))<(VLOOKUP(W13,'[Master Price Guide-Edited.xls]Northwestern Fruits'!$B$7:$I$147,9,FALSE)),1,2)

Your second VLOOKUP won't work as written because you're returning a value from column 9 in an 8 column range...fix that and use a formula of the form

=LOOKUP(3,CHOOSE({1,2},0,IF(VLOOKUP(B1,C2:D10,2,FALSE)<VLOOKUP(C1,C2:D10,2,FALSE),2,1)))

This function does work.

=IF((VLOOKUP(V13,'[Master Price Guide-Edited.xls]Sysco'!$B8:$J$196,9,FALSE))<(VLOOKUP(W13,'[Master Price Guide-Edited.xls]Northwestern Fruits'!$B$7:$J$147,9,FALSE)),1,2)

But I need it to work around errors. I don't quite understand how your formula works.

Ok you now have a 9 column range for the second lookup.

My suggestion does what you want, I think, it returns a zero if either VLOOKUP returns an error, without using a long ISERROR type formula

=LOOKUP(3,CHOOSE({1,2},0,IF((VLOOKUP(V13,'[Master Price Guide-Edited.xls]Sysco'!$B8:$J$196,9,FALSE))<(VLOOKUP(W13,'[Master Price Guide-Edited.xls]Northwestern Fruits'!$B$7:$J$147,9,FALSE)),1,2)))

The choose part returns an array of 2 values, the first of which is 0 and the second of which is the result of your original formula, 1, 2 or an error (probably #N/A)

so that would look like {0,1} or {0,2} or {0,#N/A)

The LOOKUP part will take the last numeric value from that array, so when your original formula returns a 1 or a 2 that number is returned but when the formula gives an error zero is returned.

Thank you, that works great.

But now I realize I could have just done an =ISERROR() for the conditional formating.