Something where Excel 2007 would help.

What you want to do is search your Sheet1 as you are
doing and if there is an error use the same formula with
Sheet2 that contains the additional columns.

See your HELP (IS Functions)
ISERR Value refers to any error value except #N/A.

ISERROR Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!,
#NUM!, #NAME?, or #NULL!).


=IF(ISERR(old formula), sheet2 formula, oldformula)

For the ISERR(old formula) portion you only need that
part of the formula that cause a failure, but you can include
the entire old formula.

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


wrote in message
oups.com...
All -

I am using the following formula:

=OFFSET('Sheet 1'!A2,MATCH(C8,'Sheet 1'!A3:A229,0),MATCH(E4,'Sheet 1'!
B1:IV1,0))

to lookup data in a table. The problem is that my table is on to
worksheets due to the number of vertical columns. I can't do a range
across 2 sheets due to the nature of the Match formula. If it doesn't
find the item on the sheet, it returns #n/a. I was trying to use
=IF(ISERROR formula to tell it that if an #n/a comes back to go to the
next sheet (by repeating the above match code and just changing the
sheet reference), however, I haven't been succesful. Anyone have any
ideas on how I could do this?