VLOOKUP bringing up #N/A in some cells despite reference value matching in source table?

Hi,

Excel novice here so please bear with me, but I can't seem to get to the bottom of this.

I've pulled the data from one column in 5 sheets to another, consolidated, and then hand coded these so that I could pull this coding back into the main sheets via a VLOOKUP. However - in some cases it refuses to pull this data over and instead gives me the value #N/A. Having hand coded over 3000 lines, I'd rather not have to go through another 5 tabs to find the cells that the formula failed in! It shouldn't be a problem with the formatting (blank spaces, etc.) as like I say the table array was copied and pasted from where I'm trying to bring the information back into. (Yes, I know that sentence makes no sense).

My formula reads: =VLOOKUP(D4,'Author Coding'!A2:B3171,2,FALSE)

One of the cells that hasn't worked reads: =VLOOKUP(D35,'Author Coding'!A33:B3202,2,FALSE) and contains a 'value not available error' but when I look at the table array I can see no difference between this and the other rows.

I'm losing my mind slightly so if anyone has any ideas what's going on, I'd be very grateful!

Thanks! It looks like the problem should be this:

[range_lookup]: Do you want an exact match? Is an approximate match okay?
If you use TRUE as the last argument, or omit the last argument, an approximate match can be returned. This example has FALSE as the last argument, so if the product code is not found, the result will be #N/A.

Except I can see the value it's looking for in the table array, which is why I'm so baffled.

You should also use absolute references for the row ranges of your table if you are copying down, i.e.:

'Author Coding'!A$2:B$3171

Notice how your table range has changed in the two example formulae that you quote - perhaps the matching value occurs in a cell which is no longer in the range, i.e. between A2 and A32.

Hope this helps.

Pete