vlookup not returning a match even when there is one

[karen]

Does anyone know why this should happen?
I am replicating a vlookup that worked previously, with a new set of data,
but it is returning #n/a for everything, even though I know there is data
there.

I am trying to match number records.
I have changed all columns to text format.
I have multiplied all cells by 1 to ensure accurate
I can't think of what else would mean that they don't match.
Please help.
cheers

 

[Pete_UK]

You new data might be text that looks like numbers, and it might
contain space characters or non-breaking space characters that you
obviously cannot see - use Find & Replace on the column to get rid of
these.

You could amend your formula along these lines:

=VLOOKUP(""&A1, ....

or:

=VLOOKUP(A1*1, ....

in the first case to try to match with text values in your lookup
table where the lookup value is a proper number, and in the second
case to match with real numbers in your lookup table where the lookup
value is a text value.

Hope this helps.

Pete

 

[karen]

Thanks for this, but I still can't get to the bottom of the error. I have
tried the below and still not winning. I can't be discounted that there
might be user issues here too!
=VLOOKUP($N2,$E:$I,1,FALSE)
is the basic formula. I am overlaying the data that was on N2 which worked
previously with other information, from the same original source. E-I have
not changed.
So confused with this one.......

 

[Dave Peterson]

Debra Dalgleish has lots of notes on troubleshooting this kind of error:
http://contextures.com/xlFunctions02.html#Trouble

 

[Mike H]

Karen,

Your formula is looking in column E to Match N2 and is then returning column
E so in other words if it finds a match it will only ever return what it
finds in N2. You have 5 columns in your lookup arrar (E - I) so change the 1
in your formula to the column you want to return.

Mike

 

[Mike H]

I should have added that if it then returns #NA it isn't finding a match for
N2 in column E which could mean your data aren't what you think they are.

Check for extra spaces numbers that look like numbers but are really text.

 

[karen]

Pete/Mike/Dave
Thanks a million for your help, I've got it sorted now, this is my first
time using one of these forums and it has been more than helpful.
thanks again
Karen