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?

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

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

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

Personally I avoid vlookup...

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'

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!

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).

Managed to get it to work - phew!!