#NA with an =OFFSET formula

[bgamach1]

=OFFSET(I$3,(MATCH(LARGE($B:$B,(ROW()-2)),$B:$B,0)-3),0)+ROW()/10000

I am using this formula, and it works as long as cell I3 does not equal #NA . Is there a way to fix this formula so when #NA is in cell I3, it returns blank instead of #NA ?

 

[philwojo]

Try this:

=if(iserror(i$3),"",[COLOR=#333333]OFFSET(I$3,(MATCH(LARGE($B:$B,(ROW()-2)),$B:$B,0)-3),0)+ROW()/10000))[/COLOR]

I think your formula might be missing some stuff at the end, so just update as appropriate, but doing the check on i$3 at the start should do what you want.

 

[bgamach1]

Thanks. I am also having trouble with this formula.

=VLOOKUP(K15,B2:I29,8,0)

if the value in the column 8 is #na , what can i add to have it return blank?

 

[philwojo]

I think you can do this one like this:

=iferror([COLOR=#333333]VLOOKUP(K15,B2:I29,8,0),"")[/COLOR]