VLookup stops half way

[nyc123]

Hi all,

Having some trouble with a vlookup. Essentially I need to match data from B2 to the corresponding column in B1, based on the titles A1 and A2. Some B1 cells are expected to match with a 0, as there is no data in B2. But most are populating with #n/a, as there isn't a corresponding A2 value. The formula appears to be correct, as some cells fill in, but the rest just fill out with the dreaded #n/a. Not really sure what to do, as this is above my excel knowledge. I know I wordered this horribly, too.

This is the formula i'm using: =VLOOKUP(A3:A4358,'2016'!A2:B3349,2,FALSE)

Any help is appreciated!

[Pete_UK]

You should change the formula in B3 of the 2017 sheet to this:

=VLOOKUP(A3,'2016'!$A$2:$B$3349,2,FALSE)

When you copy this down the A3 will become A4, A5, A6 etc. on successive rows, but the table size (i.e. $A$2:$B$3349) will not change because of the dollar symbols in the cell references.

Mind you, that does not improve things, and the reason you get a lot of those #N/A errors is because there are not many exact matches, and that is what the formula is looking for. If you take cell A4 of the 2017 sheet for example (i.e. "3LeggedThingLtd."), this will not match with cell A6 of the 2016 sheet (i.e. "3 Legged Thing Ltd") as there are spaces and no full-stop.

Hope this helps.

Pete

[nyc123]

Thank you!!! Still makes my life easier. One was done manually and one was spit out by a computer, hence the spacing issues.

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.