Vlookup returning #N/A when it should not
 

Hi all,

Per the subject, vlookup is not working -- I thought it might be because of
hidden values as the data was downloaded from a website (in Excel format
though), however, I used David McRitchie's TRIMALL macro, but the problem
remains.

I also used Chip Pearson's CellView Add-In which showed that there were no
special characters. I also used the =ISTEXT(A2) worksheet function to
validate that no cells are text (both source cells for the vlookup as well as
the cells being looked up). Finally, I tried various different formats
(number, date, text), however, nothing can get it to work. Just to be sure
that it should work, I copied one of the lookup values, went to the column of
the sheet where vlookup was searching, and used CTRL+F to validate that the
value was witing the search range.

Any idea on what the issue can be??

Thanks!
--
Robert

Vlookup returning #N/A when it should not
 

Can you show a copy of the formula you are using? And it would also help to
describe your data layout - your post implies an earlier thread, but I don't
remember seeing it.

Pete

"robs3131" wrote in message
...
Hi all,

Per the subject, vlookup is not working -- I thought it might be because
of
hidden values as the data was downloaded from a website (in Excel format
though), however, I used David McRitchie's TRIMALL macro, but the problem
remains.

I also used Chip Pearson's CellView Add-In which showed that there were no
special characters. I also used the =ISTEXT(A2) worksheet function to
validate that no cells are text (both source cells for the vlookup as well
as
the cells being looked up). Finally, I tried various different formats
(number, date, text), however, nothing can get it to work. Just to be
sure
that it should work, I copied one of the lookup values, went to the column
of
the sheet where vlookup was searching, and used CTRL+F to validate that
the
value was witing the search range.

Any idea on what the issue can be??

Thanks!
--
Robert

Vlookup returning #N/A when it should not
 

Hi Robert,

One possibility:

If the lookup number is, for example: 2 and the values in the table are 2.1,
2.2 etc. you will get the #NA error. The exanmple above would behave exactly
as you described in your post. Is it possible the number is something like:
2.0000000001?

To test this, copy your lookup number into the table, I suspect the #NA will
go away.

Good luck

Mike


"robs3131" wrote:

Hi all,

Per the subject, vlookup is not working -- I thought it might be because of
hidden values as the data was downloaded from a website (in Excel format
though), however, I used David McRitchie's TRIMALL macro, but the problem
remains.

I also used Chip Pearson's CellView Add-In which showed that there were no
special characters. I also used the =ISTEXT(A2) worksheet function to
validate that no cells are text (both source cells for the vlookup as well as
the cells being looked up). Finally, I tried various different formats
(number, date, text), however, nothing can get it to work. Just to be sure
that it should work, I copied one of the lookup values, went to the column of
the sheet where vlookup was searching, and used CTRL+F to validate that the
value was witing the search range.

Any idea on what the issue can be??

Thanks!
--
Robert

Vlookup returning #N/A when it should not
 

It's almost always something like a space at the end of the cell contents...
if you have a value like "abcd " (note space after d), and you control-F
"abcd", it will find that cell. But a VLOOKUP on "abcd" won't...

After you do the control-F to find the cell, then F2 edit to see if it
contains any spaces at the end...


"robs3131" wrote in message
...
Hi all,

Per the subject, vlookup is not working -- I thought it might be because
of
hidden values as the data was downloaded from a website (in Excel format
though), however, I used David McRitchie's TRIMALL macro, but the problem
remains.

I also used Chip Pearson's CellView Add-In which showed that there were no
special characters. I also used the =ISTEXT(A2) worksheet function to
validate that no cells are text (both source cells for the vlookup as well
as
the cells being looked up). Finally, I tried various different formats
(number, date, text), however, nothing can get it to work. Just to be
sure
that it should work, I copied one of the lookup values, went to the column
of
the sheet where vlookup was searching, and used CTRL+F to validate that
the
value was witing the search range.

Any idea on what the issue can be??

Thanks!
--
Robert

Vlookup returning #N/A when it should not
 

After looking at it, it appears that the values being looked up are numbers
(when I highlight them all, the "sum" function on the bottom Excel bar shows
a sum) while the source vlookup values are not numbers (nothing shows in the
bottom Excel bar when I highlight these values).

It appears that highlighting the source vlookup numbers and using "Format"
from the Excel format to format these values as numbers does not work as they
still do not show a sum. Also, I used an If/Then to validate that that
source vlookup values and the values being looked up are not equal. Directly
below shows the spreadsheet with it's formulas while below that are the
results. Any help is greatly appreciated - I've spent way too long trying to
figure this out :)

Col A: Values being looked up Col B (Blank) Col C: Source Vlookup values Col
D: Vlookup formula Col E: If/Then test
720063454 720063454 =VLOOKUP(C2,A:B,1,FALSE) =IF(A2=C2,"","issue")
720062194 720062194 =VLOOKUP(C3,A:B,1,FALSE) =IF(A3=C3,"","issue")
720062860 720062860 =VLOOKUP(C4,A:B,1,FALSE) =IF(A4=C4,"","issue")
720063184 720063184 =VLOOKUP(C5,A:B,1,FALSE) =IF(A5=C5,"","issue")


Col A: Values being looked up Col B (Blank) Col C: Source Vlookup values Col
D: Vlookup formula Col E: If/Then test
720063454 720063454 #N/A issue
720062194 720062194 #N/A issue
720062860 720062860 #N/A issue
720063184 720063184 #N/A issue



--
Robert


"mike in texas" wrote:

Hi Robert,

One possibility:

If the lookup number is, for example: 2 and the values in the table are 2.1,
2.2 etc. you will get the #NA error. The exanmple above would behave exactly
as you described in your post. Is it possible the number is something like:
2.0000000001?

To test this, copy your lookup number into the table, I suspect the #NA will
go away.

Good luck

Mike


"robs3131" wrote:

Hi all,

Per the subject, vlookup is not working -- I thought it might be because of
hidden values as the data was downloaded from a website (in Excel format
though), however, I used David McRitchie's TRIMALL macro, but the problem
remains.

I also used Chip Pearson's CellView Add-In which showed that there were no
special characters. I also used the =ISTEXT(A2) worksheet function to
validate that no cells are text (both source cells for the vlookup as well as
the cells being looked up). Finally, I tried various different formats
(number, date, text), however, nothing can get it to work. Just to be sure
that it should work, I copied one of the lookup values, went to the column of
the sheet where vlookup was searching, and used CTRL+F to validate that the
value was witing the search range.

Any idea on what the issue can be??

Thanks!
--
Robert

Vlookup returning #N/A when it should not
 

The cell values have to match exactly, including format, for your VLOOKUPs
to work. However, you can achieve this quite easily within the formula
without having to change a lot of values. If column A contains proper
numbers and column C has "text" numbers, then change your formula to this:

=VLOOKUP(C2*1,A:B,1,FALSE)

The *1 will force the value in C2 to be treated as a number.

If you have text values in column A and proper numbers in column C, then
make this change:

=VLOOKUP(C2&"",A:B,1,FALSE)

Hope this helps.

Pete


"robs3131" wrote in message
...
After looking at it, it appears that the values being looked up are
numbers
(when I highlight them all, the "sum" function on the bottom Excel bar
shows
a sum) while the source vlookup values are not numbers (nothing shows in
the
bottom Excel bar when I highlight these values).

It appears that highlighting the source vlookup numbers and using "Format"
from the Excel format to format these values as numbers does not work as
they
still do not show a sum. Also, I used an If/Then to validate that that
source vlookup values and the values being looked up are not equal.
Directly
below shows the spreadsheet with it's formulas while below that are the
results. Any help is greatly appreciated - I've spent way too long trying
to
figure this out :)

Col A: Values being looked up Col B (Blank) Col C: Source Vlookup values
Col
D: Vlookup formula Col E: If/Then test
720063454 720063454 =VLOOKUP(C2,A:B,1,FALSE) =IF(A2=C2,"","issue")
720062194 720062194 =VLOOKUP(C3,A:B,1,FALSE) =IF(A3=C3,"","issue")
720062860 720062860 =VLOOKUP(C4,A:B,1,FALSE) =IF(A4=C4,"","issue")
720063184 720063184 =VLOOKUP(C5,A:B,1,FALSE) =IF(A5=C5,"","issue")


Col A: Values being looked up Col B (Blank) Col C: Source Vlookup values
Col
D: Vlookup formula Col E: If/Then test
720063454 720063454 #N/A issue
720062194 720062194 #N/A issue
720062860 720062860 #N/A issue
720063184 720063184 #N/A issue



--
Robert


"mike in texas" wrote:

Hi Robert,

One possibility:

If the lookup number is, for example: 2 and the values in the table are
2.1,
2.2 etc. you will get the #NA error. The exanmple above would behave
exactly
as you described in your post. Is it possible the number is something
like:
2.0000000001?

To test this, copy your lookup number into the table, I suspect the #NA
will
go away.

Good luck

Mike


"robs3131" wrote:

Hi all,

Per the subject, vlookup is not working -- I thought it might be
because of
hidden values as the data was downloaded from a website (in Excel
format
though), however, I used David McRitchie's TRIMALL macro, but the
problem
remains.

I also used Chip Pearson's CellView Add-In which showed that there were
no
special characters. I also used the =ISTEXT(A2) worksheet function to
validate that no cells are text (both source cells for the vlookup as
well as
the cells being looked up). Finally, I tried various different formats
(number, date, text), however, nothing can get it to work. Just to be
sure
that it should work, I copied one of the lookup values, went to the
column of
the sheet where vlookup was searching, and used CTRL+F to validate that
the
value was witing the search range.

Any idea on what the issue can be??

Thanks!
--
Robert

Vlookup returning #N/A when it should not
 

You can find out quickly if your values match. Suppose your lookup value is
in A1and your lookup table is in B1:C10 and you think the value in A1 should
match the value in B6. Try the formula =A1=B6. If that returns TRUE your
values match, if it returns FALSE, they don't

Tyro

"robs3131" wrote in message
...
Hi all,

Per the subject, vlookup is not working -- I thought it might be because
of
hidden values as the data was downloaded from a website (in Excel format
though), however, I used David McRitchie's TRIMALL macro, but the problem
remains.

I also used Chip Pearson's CellView Add-In which showed that there were no
special characters. I also used the =ISTEXT(A2) worksheet function to
validate that no cells are text (both source cells for the vlookup as well
as
the cells being looked up). Finally, I tried various different formats
(number, date, text), however, nothing can get it to work. Just to be
sure
that it should work, I copied one of the lookup values, went to the column
of
the sheet where vlookup was searching, and used CTRL+F to validate that
the
value was witing the search range.

Any idea on what the issue can be??

Thanks!
--
Robert

Vlookup returning #N/A when it should not
 

Thanks Pete! That did it -- I input the *1 into the vlookup and it worked.
So frustrating that it just comes down to something so simple after so many
hours spent looking into this... :)
--
Robert


"Pete_UK" wrote:

The cell values have to match exactly, including format, for your VLOOKUPs
to work. However, you can achieve this quite easily within the formula
without having to change a lot of values. If column A contains proper
numbers and column C has "text" numbers, then change your formula to this:

=VLOOKUP(C2*1,A:B,1,FALSE)

The *1 will force the value in C2 to be treated as a number.

If you have text values in column A and proper numbers in column C, then
make this change:

=VLOOKUP(C2&"",A:B,1,FALSE)

Hope this helps.

Pete


"robs3131" wrote in message
...
After looking at it, it appears that the values being looked up are
numbers
(when I highlight them all, the "sum" function on the bottom Excel bar
shows
a sum) while the source vlookup values are not numbers (nothing shows in
the
bottom Excel bar when I highlight these values).

It appears that highlighting the source vlookup numbers and using "Format"
from the Excel format to format these values as numbers does not work as
they
still do not show a sum. Also, I used an If/Then to validate that that
source vlookup values and the values being looked up are not equal.
Directly
below shows the spreadsheet with it's formulas while below that are the
results. Any help is greatly appreciated - I've spent way too long trying
to
figure this out :)

Col A: Values being looked up Col B (Blank) Col C: Source Vlookup values
Col
D: Vlookup formula Col E: If/Then test
720063454 720063454 =VLOOKUP(C2,A:B,1,FALSE) =IF(A2=C2,"","issue")
720062194 720062194 =VLOOKUP(C3,A:B,1,FALSE) =IF(A3=C3,"","issue")
720062860 720062860 =VLOOKUP(C4,A:B,1,FALSE) =IF(A4=C4,"","issue")
720063184 720063184 =VLOOKUP(C5,A:B,1,FALSE) =IF(A5=C5,"","issue")


Col A: Values being looked up Col B (Blank) Col C: Source Vlookup values
Col
D: Vlookup formula Col E: If/Then test
720063454 720063454 #N/A issue
720062194 720062194 #N/A issue
720062860 720062860 #N/A issue
720063184 720063184 #N/A issue



--
Robert


"mike in texas" wrote:

Hi Robert,

One possibility:

If the lookup number is, for example: 2 and the values in the table are
2.1,
2.2 etc. you will get the #NA error. The exanmple above would behave
exactly
as you described in your post. Is it possible the number is something
like:
2.0000000001?

To test this, copy your lookup number into the table, I suspect the #NA
will
go away.

Good luck

Mike


"robs3131" wrote:

Hi all,

Per the subject, vlookup is not working -- I thought it might be
because of
hidden values as the data was downloaded from a website (in Excel
format
though), however, I used David McRitchie's TRIMALL macro, but the
problem
remains.

I also used Chip Pearson's CellView Add-In which showed that there were
no
special characters. I also used the =ISTEXT(A2) worksheet function to
validate that no cells are text (both source cells for the vlookup as
well as
the cells being looked up). Finally, I tried various different formats
(number, date, text), however, nothing can get it to work. Just to be
sure
that it should work, I copied one of the lookup values, went to the
column of
the sheet where vlookup was searching, and used CTRL+F to validate that
the
value was witing the search range.

Any idea on what the issue can be??

Thanks!
--
Robert

Vlookup returning #N/A when it should not
 

Glad to hear it worked for you.

Sometimes the most frustrating problems have an easy solution.

Pete

On Mar 1, 1:52*am, robs3131
wrote:
Thanks Pete! *That did it -- I input the *1 into the vlookup and it worked. *
So frustrating that it just comes down to something so simple after so many
hours spent looking into this... *:)
--
Robert



"Pete_UK" wrote:
The cell values have to match exactly, including format, for your VLOOKUPs
to work. However, you can achieve this quite easily within the formula
without having to change a lot of values. If column A contains proper
numbers and column C has "text" numbers, then change your formula to this:

=VLOOKUP(C2*1,A:B,1,FALSE)

The *1 will force the value in C2 to be treated as a number.

If you have text values in column A and proper numbers in column C, then
make this change:

=VLOOKUP(C2&"",A:B,1,FALSE)

Hope this helps.

Pete

"robs3131" wrote in message
...
After looking at it, it appears that the values being looked up are
numbers
(when I highlight them all, the "sum" function on the bottom Excel bar
shows
a sum) while the source vlookup values are not numbers (nothing shows in
the
bottom Excel bar when I highlight these values).

It appears that highlighting the source vlookup numbers and using "Format"
from the Excel format to format these values as numbers does not work as
they
still do not show a sum. *Also, I used an If/Then to validate that that
source vlookup values and the values being looked up are not equal.
Directly
below shows the spreadsheet with it's formulas while below that are the
results. *Any help is greatly appreciated - I've spent way too long trying
to
figure this out *:)

Col A: Values being looked up Col B (Blank) Col C: Source Vlookup values
Col
D: Vlookup formula Col E: If/Then test
720063454 720063454 =VLOOKUP(C2,A:B,1,FALSE) =IF(A2=C2,"","issue")
720062194 720062194 =VLOOKUP(C3,A:B,1,FALSE) =IF(A3=C3,"","issue")
720062860 720062860 =VLOOKUP(C4,A:B,1,FALSE) =IF(A4=C4,"","issue")
720063184 720063184 =VLOOKUP(C5,A:B,1,FALSE) =IF(A5=C5,"","issue")

Col A: Values being looked up Col B (Blank) Col C: Source Vlookup values
Col
D: Vlookup formula Col E: If/Then test
720063454 720063454 #N/A issue
720062194 720062194 #N/A issue
720062860 720062860 #N/A issue
720063184 720063184 #N/A issue

--
Robert

"mike in texas" wrote:

Hi Robert,

One possibility:

If the lookup number is, for example: 2 and the values in the table are
2.1,
2.2 etc. you will get the #NA error. The exanmple above would behave
exactly
as you described in your post. Is it possible the number is something
like:
2.0000000001?

To test this, copy your lookup number into the table, I suspect the #NA
will
go away.

Good luck

Mike

"robs3131" wrote:

Hi all,

Per the subject, vlookup is not working -- I thought it might be
because of
hidden values as the data was downloaded from a website (in Excel
format
though), however, I used David McRitchie's TRIMALL macro, but the
problem
remains.

I also used Chip Pearson's CellView Add-In which showed that there were
no
special characters. *I also used the =ISTEXT(A2) worksheet function to
validate that no cells are text (both source cells for the vlookup as
well as
the cells being looked up). *Finally, I tried various different formats
(number, date, text), however, nothing can get it to work. *Just to be
sure
that it should work, I copied one of the lookup values, went to the
column of
the sheet where vlookup was searching, and used CTRL+F to validate that
the
value was witing the search range.

Any idea on what the issue can be??

Thanks!
--
Robert- Hide quoted text -

- Show quoted text -

Vlookup returning #N/A when it should not
 

I am still finding my vlookup will not return a value for some odd lines of
my data, even though I know that the data is there in the array. I have been
having issues with various formats, and am using vlookup(A1+0, ... or
vlookup(A1&"", ... to force the vlookup to search for number or text values,
and generally this works.

However, I still find some odd lines return an N/A even if I copy the cell
from the search list to the table array - and use =A1=D5 which returns TRUE
showing they are definitely the same.

So is there anything else that could be causing this error?

Thanks for reading...


"Tyro" wrote:

You can find out quickly if your values match. Suppose your lookup value is
in A1and your lookup table is in B1:C10 and you think the value in A1 should
match the value in B6. Try the formula =A1=B6. If that returns TRUE your
values match, if it returns FALSE, they don't

Tyro

"robs3131" wrote in message
...
Hi all,

Per the subject, vlookup is not working -- I thought it might be because
of
hidden values as the data was downloaded from a website (in Excel format
though), however, I used David McRitchie's TRIMALL macro, but the problem
remains.

I also used Chip Pearson's CellView Add-In which showed that there were no
special characters. I also used the =ISTEXT(A2) worksheet function to
validate that no cells are text (both source cells for the vlookup as well
as
the cells being looked up). Finally, I tried various different formats
(number, date, text), however, nothing can get it to work. Just to be
sure
that it should work, I copied one of the lookup values, went to the column
of
the sheet where vlookup was searching, and used CTRL+F to validate that
the
value was witing the search range.

Any idea on what the issue can be??

Thanks!
--
Robert

Vlookup returning #N/A when it should not
 

Always post your formula
If the 4th argument of the VLOOKUP is omitted or TRUE, the list has to be
sorted ascending

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"holly" wrote in message
...
I am still finding my vlookup will not return a value for some odd lines of
my data, even though I know that the data is there in the array. I have
been
having issues with various formats, and am using vlookup(A1+0, ... or
vlookup(A1&"", ... to force the vlookup to search for number or text
values,
and generally this works.

However, I still find some odd lines return an N/A even if I copy the cell
from the search list to the table array - and use =A1=D5 which returns
TRUE
showing they are definitely the same.

So is there anything else that could be causing this error?

Thanks for reading...


"Tyro" wrote:

You can find out quickly if your values match. Suppose your lookup value
is
in A1and your lookup table is in B1:C10 and you think the value in A1
should
match the value in B6. Try the formula =A1=B6. If that returns TRUE your
values match, if it returns FALSE, they don't

Tyro

"robs3131" wrote in message
...
Hi all,

Per the subject, vlookup is not working -- I thought it might be
because
of
hidden values as the data was downloaded from a website (in Excel
format
though), however, I used David McRitchie's TRIMALL macro, but the
problem
remains.

I also used Chip Pearson's CellView Add-In which showed that there were
no
special characters. I also used the =ISTEXT(A2) worksheet function to
validate that no cells are text (both source cells for the vlookup as
well
as
the cells being looked up). Finally, I tried various different formats
(number, date, text), however, nothing can get it to work. Just to be
sure
that it should work, I copied one of the lookup values, went to the
column
of
the sheet where vlookup was searching, and used CTRL+F to validate that
the
value was witing the search range.

Any idea on what the issue can be??

Thanks!
--
Robert