Hmm . . .

If you really want to do this, try this . . .

=IF(OR(VLOOKUP(A1,B1:C10,2,FALSE)=0,ISNA(VLOOKUP(A1,B1:C10,2,FALSE))),VLOOKUP(A1,D1:E10,2,FALSE),VLOOKUP(A1,B1:C10,2,FALSE))

or a slightly different and shorter version . . .

=IF(ISERROR(0/VLOOKUP(A1,B1:C10,2,FALSE)),VLOOKUP(A1,D1:E10,2,FALSE),VLOOKUP(A1,B1:C10,2,FALSE))

Note - this shorter version will ONLY work if the lookup value is numeric.

If your lookup value is text, it won't work properly.

As you can see, both are a bit messier than the original.

You can extend both of these out to testing 4 ranges if you like, but the final version will be a good bit more complicated.

As I said before, consider merging your lookup tables into a single table.