You can have a multi range table array in the one worksheet (not necessarily
the same worksheet as the formula) but the ranges must be in the same column.
Simply separate the ranges with a colon. Does not work with multiple
worksheets.

=VLOOKUP(A1,Sheet2!$A$1:$A$6:Sheet2!$A$13:$A$24:Sh eet2!$A$29:$A$43,1,FALSE)

If table array ranges are on multiple worksheets then it is possible to use
IF(ISNA and use multiple nested if statements. However, only works with exact
match parameter otherwise will never get #N/A due to finding nearest match.
Also can get very complex with multiple If's.

=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$A$6,1,FALSE)),VLO OKUP(A1,Sheet3!$C$10:$C$36,1,FALSE),VLOOKUP(A1,She et2!$A$1:$A$6,1,FALSE))

Having said that, I think that you would be wise to try to find a solution
whereby you can get a copy of the table array all together in a contiguous
range. With complex formula solutions you can get it all working properly and
a small worksheet change can through everything in to disarray.

--
Regards,

OssieMac