Thread: If statements and v look up

Hi

I have the following formula -

=IF(A12=1,(VLOOKUP(B12,'O2 Offlline'!$A:$N,7,0)),(VLOOKUP(B12,'All Offline'!$A:$N,7,0)))

which is a formula to perform a v look on cell B based on whether Cell A is equal to 1 or 2. However the problem with this is that it will return #N/A if it can't find any data for cell B which is a problem when it's totaling at the bottom.

However using this formula

=IF(ISNA(VLOOKUP($B12,'O2 Offlline'!$A:$N,7,FALSE)),0,VLOOKUP($B12,'O2 Offlline'!$A:$N,7,FALSE))

on just one sheet will return a - if it can't find the data and therefore doesn't effect the total as it's counted as a zero. Now that's not helpful as I need it to perform the vlook-up based on the 2 sheets but for it also to return a - if it can't find any data... any ides on how to combine the 2 formulas to create a vlook-up on 2 sheets but it return any data that it can't find as a -

Thanks