errors

  • Thread starter Thread starter not Albert Einstein
  • Start date Start date
N

not Albert Einstein

Hi all -

How would I include error trapping in this formula -


=IF(A1="NEW",VLOOKUP(B1,sheet2!C19:K23,MATCH(C1,sheet2!
C18:K18,0),0),IF(A1="PREVIOUS",VLOOKUP(B1,sheet2!
C57:G61,4,0),IF(A1="NEXT",VLOOKUP(B1,sheet2!C50:H54,4,0),IF
(A1="FUTURE",VLOOKUP(B1,sheet3!B10:H13,MATCH(C1,sheet3!
B9:H9,0),0),""))))

If I put an IF ISNA or IF ISERROR with each of the
conditions it looks like I'll hit the wall on nested
functions and the formula will be *extremely* long and
hard to manage.

I've considered cf to hide the error but I'd like to know
if there's any way to do it in the formula itself.

Thank You
 
OK, I figured it out.

I put the whole thing in an IF ISERROR. The new formula
with error trapping now looks like this:

=IF(ISERROR(IF(A1="NEW",VLOOKUP(B1,Sheet2!C19:K23,MATCH
(C1,Sheet2!C18:K18,0),0),IF(A1="PREVIOUS",VLOOKUP
(B1,Sheet2!C57:G61,4,0),IF(A1="NEXT",VLOOKUP(B1,Sheet2!
C50:H54,4,0),IF(A1="FUTURE",VLOOKUP(B1,Sheet3!B10:H13,MATCH
(C1,Sheet3!B9:H9,0),0),""))))),"",IF(A1="NEW",VLOOKUP
(B1,Sheet2!C19:K23,MATCH(C1,Sheet2!C18:K18,0),0),IF
(A1="PREVIOUS",VLOOKUP(B1,Sheet2!C57:G61,4,0),IF
(A1="NEXT",VLOOKUP(B1,Sheet2!C50:H54,4,0),IF
(A1="FUTURE",VLOOKUP(B1,Sheet3!B10:H13,MATCH(C1,Sheet3!
B9:H9,0),0),"")))))

That's a thing of beauty, ain't it !
 
Back
Top