vlookup not returning a match even when there is one
 

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

vlookup not returning a match even when there is one
 

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

On Jul 14, 11:36*am, karen wrote:
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

vlookup not returning a match even when there is one
 

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.......

"Pete_UK" wrote:

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

On Jul 14, 11:36 am, karen wrote:
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

vlookup not returning a match even when there is one
 

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

karen wrote:

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

--

Dave Peterson

vlookup not returning a match even when there is one
 

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

"karen" wrote:

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.......

"Pete_UK" wrote:

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

On Jul 14, 11:36 am, karen wrote:
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

vlookup not returning a match even when there is one
 

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.

"Mike H" wrote:

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

"karen" wrote:

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.......

"Pete_UK" wrote:

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

On Jul 14, 11:36 am, karen wrote:
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

vlookup not returning a match even when there is one
 

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

"Mike H" wrote:

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.

"Mike H" wrote:

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

"karen" wrote:

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.......

"Pete_UK" wrote:

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

On Jul 14, 11:36 am, karen wrote:
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