isna vlookup #n/a but not false

C

Clairedebear

Hi,

I am creating 1 spreadsheet from 2 moving sheets using Vlookups. However not
all sheets have the same things to look up and returns #n/a - I can change to
use isna, but if I use for all cells I get False where their would be a value
without the isna.

Can anyone help to give 1 formula ?

=VLOOKUP($B:$B,'Current Forecast'!$C:$S,4,FALSE)+VLOOKUP($B:$B,'Current
Forecast'!$C:$S,4,FALSE)

=IF(ISNA(VLOOKUP($B:$B,'Current
Forecast'!$C:$S,4,FALSE)),0)+IF(ISNA(VLOOKUP($B:$B,'Current
Forecast'!$C:$S,4,FALSE)),0)

Many thanks for any advice!
 
J

Joel

You original formula did nothing when the cell didn't have a ISNA. You
equation reduced to

=FALSE+FALSE

Which gave #n/a because VBA didn't know how to add False + False.

I think this is whatt you really wanted.
=IF(ISNA(VLOOKUP($B:$B,'Current
Forecast'!$C:$S,4,FALSE)),0,VLOOKUP($B:$B,'Current
Forecast'!$C:$S,4,FALSE))+IF(ISNA(VLOOKUP($B:$B,'Current
Forecast'!$C:$S,4,FALSE)),0,VLOOKUP($B:$B,'Current
Forecast'!$C:$S,4,FALSE))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top