vlookup codes - multiple sheets

I use excel 2003 and sometimes 2010 so my solutions have to run on both.

Im getting n/a errors when i use vlookup to search another sheet for values.

I put my data in a sheet called 'data' and then search postcodes which are stored in 3 other sheets named pcode1, pcode2, pcode3.

I use the vlookup to attach the following to each postcode: Constituency Name, Ward Name, Westminster Name, Council Area Name, SP Region.

An example would be:
=VLOOKUP(B2,pcode1!$A$2:$I$65001,5,FALSE)

where B2 is the postcode
pcode1 is the first sheet with 65000 postcodes
column 5 has the constituency name, col 6 the ward name, col 7 the ward name, col 8 westminster name and so on.

Ive attached an example sheet.

How do i write a vlookup macro to search across all three sheets (pcode1, pcode2, pcode3), infilling the 5 fields next to each postcode (and most importantly while avoiding the dreaded n/a erorrs)?

You shouldn't need a macro... In C4 try this formula:

Please Login or Register  to view this content.

copied down and across.

The easiest way would be to make a new column with formula like =TRIM(A2) copied down.. then you can copy and paste special the column over the original and delete the formula column.

thanks!

last question..

.. how does the formula work? i can adapt it and use it no problrm, i also get the match() part, but the if(isnumber and index bits have me a little puzzled.

Match() returns a numeric position if a match is found and a #N/A! error if not found.... ISNUMBER() simply checks if a number was returned by the MATCH() function, so that if it was, we know there was a match. INDEX() simply indexes the range where the desired return data is and once the MATCH() position is found, then item in the same relative position from the INDEXed array is returned. Here is more on INDEX/MATCH