using Vlookup in Excel; help?

[KatyMac]

I have it working OK; except if there isn't a lookup value when it displays as #N/A which messes up my Maths

Is it possible to change that #N/A to a zero?

[Imminentchaos1]

Don't have time to check in detail but try an 'if' statement, or 'iferror' also might work!

[GinnelsandWhippets]

Yes something like IF(#NA,"0") might work, am not very good at thinking up formulae when not in front of an excel sheet though and when I don't have the Mr Excel forum to consult

[BillnTedsMostFeministAdventure]

=if(iserror(vlookup), 0, vlookup) might work.

Personally I avoid vlookup...

[KatyMac]

Why do you Avoid it, BillnTed

I have amounts due on a variety of days & need to transfer them to a calendar - if there a better way to do it

Off to try various 'IFs'

[BillnTedsMostFeministAdventure]

It's very slow to calculate a large data set. You can get round it with the INDEX and MATCH functions. But most people use it anyway!

[PetraArkanian]

It is slow...but only when you start reaching thousands of lines. It does speed up if you can ensure your data is sorted though...

It's also worth having 2 columns:

the first one will be = vlookup()
the second will be = if(iserror(first),0,first)

otherwise it will evaluate the vlookup twice (I think).

[KatyMac]

Managed to get it to work - phew!!