Help needed for Lookup Macro

[Yabby]

On one sheet I have a complete list of company registration numbers in Column A. in the following columns I want to put return values from these companies on a monthly basis. i.e Column B contains values for jan04, column c for feb04 etc.

Each month I get another worksheet which lists those companies that have sent new returns in column A and the value of those returns in column B. This is an incomplete list as not all companies make returns every month.

I want to get the value for each company from column B of the second sheet into the relevant column of the first sheet as according to the company reference number.

I tried doing this using the lookup formula, however, when excel can't find a company from the full list in the incomplete list it simply returns#N/A for any companies with a number lower than those in the incomplate list or puts in the return value of a company in the incomplete list closest to it in numerical terms (I'd like a zero or blank if the exact company isn't in the list).

Is there any way to write a macro to do what the lookup function does, but that will return a blank or zero if it can't find the look-up value in the look-up array/vector?

 

[Cbrine]

You could use this structure.

If(isna(Vlookup formula),0,Vlookup formula)

This will eliminate you NA errors. It's not an efficent formula, since it does 2 vlookups for each value, but unless your tables are huge, it shouldn't make a difference.

 

[Yabby]

Unfortunately, there are only one or two #N/As and I had seen that the isna function could be applied like that. I need something to dodge/over ride the problem of all the duplicates too.

 

[Cbrine]

I'm not qutie sure what you mean, but I think it has do with the exact match portion of your orginal post. If you use this vlookup, it will only select exact mataches.

Vlookup(Source Value, Lookup Range(Name),2, false)

The false portion of formula will tell the vlookup to get exact matches, as opposed to closest values. If this is not what you are referring to, please expand on your explanation of duplicates.

 

[Yabby]

Ah, just tried it. For some reason I didn't think Vlookpup works the way it does. With the isna thing in too it works a treat. Ta very much