VLOOKUP #N/A error. Sort and format are correct.

[charlene leblanc]

I have inherited two spreadsheets that are linked and a VLOOKUP in one is
referring to a range in the other. The VLOOKUPs all work on the existing
data but as when I add new data, bearing in mind the formatting of the cells
and the order of the lookup range, those do not work. I get the #N/A error.

Any ideas?

[Niek Otten]

What formula?
What format and why does that matter?
What values in the table and in the search argument?


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"charlene leblanc" <charlene leblanc@discussions.microsoft.com> wrote in
message news:2D40ED2C-BA23-4DD4-A5C7-EF4A1CF1CD00@microsoft.com...
>I have inherited two spreadsheets that are linked and a VLOOKUP in one is
> referring to a range in the other. The VLOOKUPs all work on the existing
> data but as when I add new data, bearing in mind the formatting of the
> cells
> and the order of the lookup range, those do not work. I get the #N/A
> error.
>
> Any ideas?

[charlene leblanc]

Sorry for being so vague. I have workbook ONE with a VLOOKUP in column O.
The VLOOKUP uses column A and looks for that value in column T in workbook
TWO and retrieves the value from column Z in the corresponding row.

Workbook ONE
Column A Column O
12345 VLOOKUP()
23456 VLOOKUP()

Workbook TWO
Column T Column Z
12345 XXXXX
23456 YYYYY

When making a change to an existing row in workbook TWO, (i.e. changing the
XXXXX in column Z to XXXYZ, the change is reflected in column O of workbook
ONE. That works as it should.

When I add a new row to workbook TWO and a corresponding on in workbook ONE,
copying the VLOOKUP formula from the existing row that works as above, I get
the #N/A error. The data in column T is sorted as it needs to be for the
VLOOKUP to succeed. To follow from the illustration above adding 12789 in
both column A of ONE and column T of TWO should give the VLOOKUP for that row
a result of XXABC. Instead it gives the #N/A error. All columns are
formatted as General, but I've also tried formatting them as Text and it made
no difference.

Workbook ONE
Column A Column O
12345 VLOOKUP()
23456 VLOOKUP()
12789 VLOOKUP()

Workbook TWO
Column T Column Z
12345 XXXXX
12789 XXABC
23456 YYYYY

The VLookup formula is as follows:
=VLOOKUP(A15,'\\BBIC\CML\[TWO.xls]One Fund'!$T:$Z,7,FALSE)

Hopefully this illustration has answered your questions and you may have
some idea what is going on.

Charlene

"Niek Otten" wrote:

> What formula?
> What format and why does that matter?
> What values in the table and in the search argument?
>
>
> --
>
> Kind Regards,
>
> Niek Otten
>
> Microsoft MVP - Excel
>
> "charlene leblanc" <charlene leblanc@discussions.microsoft.com> wrote in
> message news:2D40ED2C-BA23-4DD4-A5C7-EF4A1CF1CD00@microsoft.com...
> >I have inherited two spreadsheets that are linked and a VLOOKUP in one is
> > referring to a range in the other. The VLOOKUPs all work on the existing
> > data but as when I add new data, bearing in mind the formatting of the
> > cells
> > and the order of the lookup range, those do not work. I get the #N/A
> > error.
> >
> > Any ideas?
>
>
>

[Tom Ogilvy]

Have you extended the lookup range to include the new data?

Often this is caused because you are looking for "123" and the source range
is 123. The string "123" does not match the number 123 (regardless of which
is the term being looked for and the which is in the source data).

If a cell is formatted as Text and you enter 123, you actually get "123"
If a cell contains the number 123 and you then format it as text, the numer
123 is still stored in the cell.

Perhaps something like this is the problem.

--
Regards,
Tom Ogilvy


"charlene leblanc" <charlene leblanc@discussions.microsoft.com> wrote in
message news:2D40ED2C-BA23-4DD4-A5C7-EF4A1CF1CD00@microsoft.com...
> I have inherited two spreadsheets that are linked and a VLOOKUP in one is
> referring to a range in the other. The VLOOKUPs all work on the existing
> data but as when I add new data, bearing in mind the formatting of the
cells
> and the order of the lookup range, those do not work. I get the #N/A
error.
>
> Any ideas?

[charlene leblanc]

Format of the cells is identical and the new row is within the range. See my
entry in response to Niek Otten for an illustration.

Thanks,
Charlene

"Tom Ogilvy" wrote:

> Have you extended the lookup range to include the new data?
>
> Often this is caused because you are looking for "123" and the source range
> is 123. The string "123" does not match the number 123 (regardless of which
> is the term being looked for and the which is in the source data).
>
> If a cell is formatted as Text and you enter 123, you actually get "123"
> If a cell contains the number 123 and you then format it as text, the numer
> 123 is still stored in the cell.
>
> Perhaps something like this is the problem.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "charlene leblanc" <charlene leblanc@discussions.microsoft.com> wrote in
> message news:2D40ED2C-BA23-4DD4-A5C7-EF4A1CF1CD00@microsoft.com...
> > I have inherited two spreadsheets that are linked and a VLOOKUP in one is
> > referring to a range in the other. The VLOOKUPs all work on the existing
> > data but as when I add new data, bearing in mind the formatting of the
> cells
> > and the order of the lookup range, those do not work. I get the #N/A
> error.
> >
> > Any ideas?
>
>
>

[Tom Ogilvy]

I just tried to tell you that the format of the cells is not the determining
factor. The determining factor is how the value is stored in the cell. You
can check how with =IsText() and =IsNumber

--
Regards,
Tom Ogilvy

[charlene leblanc]

Tom,

I think you may have been onto something with the istext and isnumber
functions. However, I have now discovered what I believe to be the cause.

The two spreadsheets were opened in different sessions of Excel and the
reference was to the other workbook including the drive assignment.

When I open the second spreadsheet as a second workbook in the same Excel
session, and do exactly the same changes, I have no problem and no error. I
discovered that when I tried to reference the cell of the second workbook for
the istext function.

Not sure whether the underlying cause was a memory issue or what, but in any
case, my problem is resoved as long as I open both workbooks in the same
Excel session.

Closing off this request for assistance.
Thank you very much.
Charlene LeBlanc

"Tom Ogilvy" wrote:

> I just tried to tell you that the format of the cells is not the determining
> factor. The determining factor is how the value is stored in the cell. You
> can check how with =IsText() and =IsNumber
>
> --
> Regards,
> Tom Ogilvy
>
>
>

[Tom Ogilvy]

You definitely don't want multiple sessions. Glad you solved your problem.

--
Regards,
Tom Ogilvy

"charlene leblanc" <charleneleblanc@discussions.microsoft.com> wrote in
message news:583BC09F-0714-415C-A11E-08D35519CE91@microsoft.com...
> Tom,
>
> I think you may have been onto something with the istext and isnumber
> functions. However, I have now discovered what I believe to be the cause.
>
> The two spreadsheets were opened in different sessions of Excel and the
> reference was to the other workbook including the drive assignment.
>
> When I open the second spreadsheet as a second workbook in the same Excel
> session, and do exactly the same changes, I have no problem and no error.
I
> discovered that when I tried to reference the cell of the second workbook
for
> the istext function.
>
> Not sure whether the underlying cause was a memory issue or what, but in
any
> case, my problem is resoved as long as I open both workbooks in the same
> Excel session.
>
> Closing off this request for assistance.
> Thank you very much.
> Charlene LeBlanc
>
> "Tom Ogilvy" wrote:
>
> > I just tried to tell you that the format of the cells is not the
determining
> > factor. The determining factor is how the value is stored in the cell.
You
> > can check how with =IsText() and =IsNumber
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >

[Ann Mc]

Tom
I believe this is the problem I have just been struggling with. Can you
tell me please how I can convert a large chunk of data already entered and
stored as numbers into text?
Thanks
Ann

"Tom Ogilvy" wrote:

> Have you extended the lookup range to include the new data?
>
> Often this is caused because you are looking for "123" and the source range
> is 123. The string "123" does not match the number 123 (regardless of which
> is the term being looked for and the which is in the source data).
>
> If a cell is formatted as Text and you enter 123, you actually get "123"
> If a cell contains the number 123 and you then format it as text, the numer
> 123 is still stored in the cell.
>
> Perhaps something like this is the problem.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "charlene leblanc" <charlene leblanc@discussions.microsoft.com> wrote in
> message news:2D40ED2C-BA23-4DD4-A5C7-EF4A1CF1CD00@microsoft.com...
> > I have inherited two spreadsheets that are linked and a VLOOKUP in one is
> > referring to a range in the other. The VLOOKUPs all work on the existing
> > data but as when I add new data, bearing in mind the formatting of the
> cells
> > and the order of the lookup range, those do not work. I get the #N/A
> error.
> >
> > Any ideas?
>
>
>

[Tom Ogilvy]

Select the cells you want to convert and then run the macro.

Sub converttotext
for each cell in selection
v = cell.Text
cell.NumberFormat = "@"
cell.Value = "'" & cell.Text
Next
End Sub

--
Regards,
Tom Ogilvy

"Ann Mc" <Ann Mc@discussions.microsoft.com> wrote in message
news:920CF17A-3896-4574-B679-69860527FECD@microsoft.com...
> Tom
> I believe this is the problem I have just been struggling with. Can you
> tell me please how I can convert a large chunk of data already entered and
> stored as numbers into text?
> Thanks
> Ann
>
> "Tom Ogilvy" wrote:
>
> > Have you extended the lookup range to include the new data?
> >
> > Often this is caused because you are looking for "123" and the source
range
> > is 123. The string "123" does not match the number 123 (regardless of
which
> > is the term being looked for and the which is in the source data).
> >
> > If a cell is formatted as Text and you enter 123, you actually get "123"
> > If a cell contains the number 123 and you then format it as text, the
numer
> > 123 is still stored in the cell.
> >
> > Perhaps something like this is the problem.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "charlene leblanc" <charlene leblanc@discussions.microsoft.com> wrote in
> > message news:2D40ED2C-BA23-4DD4-A5C7-EF4A1CF1CD00@microsoft.com...
> > > I have inherited two spreadsheets that are linked and a VLOOKUP in one
is
> > > referring to a range in the other. The VLOOKUPs all work on the
existing
> > > data but as when I add new data, bearing in mind the formatting of the
> > cells
> > > and the order of the lookup range, those do not work. I get the #N/A
> > error.
> > >
> > > Any ideas?
> >
> >
> >

[Cyberindio]

When all else has failed for me, Charlene, I have done this.

Insert a new column next to your Vlookup root data column

enter this formula: =(A2&"")+0 (assuming your root data starts in cell A2)

slide a copy paste of the formula all the way down to copy your whole data
column

replace your root data with the special paste:values of the new column

Charlene, I don't have to tell you to back up your file before attempting
anything anyone recommends to you.

Don't ask me why this might work, but it has resolved my vlookup issues in
the past.

Peace



"charlene leblanc" wrote:

> I have inherited two spreadsheets that are linked and a VLOOKUP in one is
> referring to a range in the other. The VLOOKUPs all work on the existing
> data but as when I add new data, bearing in mind the formatting of the cells
> and the order of the lookup range, those do not work. I get the #N/A error.
>
> Any ideas?

[Tom Ogilvy]

If works if you are looking up numbers because the result is a number. See
my previous post in this thread.

--
Regards,
Tom Ogilvy

"Cyberindio" <Cyberindio@discussions.microsoft.com> wrote in message
news:44A71018-DC8F-4733-BAEA-E1E67EB6ABDE@microsoft.com...
> When all else has failed for me, Charlene, I have done this.
>
> Insert a new column next to your Vlookup root data column
>
> enter this formula: =(A2&"")+0 (assuming your root data starts in cell A2)
>
> slide a copy paste of the formula all the way down to copy your whole data
> column
>
> replace your root data with the special paste:values of the new column
>
> Charlene, I don't have to tell you to back up your file before attempting
> anything anyone recommends to you.
>
> Don't ask me why this might work, but it has resolved my vlookup issues in
> the past.
>
> Peace
>
>
>
> "charlene leblanc" wrote:
>
> > I have inherited two spreadsheets that are linked and a VLOOKUP in one
is
> > referring to a range in the other. The VLOOKUPs all work on the
existing
> > data but as when I add new data, bearing in mind the formatting of the
cells
> > and the order of the lookup range, those do not work. I get the #N/A
error.
> >
> > Any ideas?

[charlene leblanc]

It's worth a try, but I'm not sure what you mean by 'root data'. Using the
documented syntax of

"VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)"

Do you mean doing a copy of the lookup_value column or the table_array, or
the range_lookup? I guess I could do all three ....

Thanks,
Charlene

"Cyberindio" wrote:

> When all else has failed for me, Charlene, I have done this.
>
> Insert a new column next to your Vlookup root data column
>
> enter this formula: =(A2&"")+0 (assuming your root data starts in cell A2)
>
> slide a copy paste of the formula all the way down to copy your whole data
> column
>
> replace your root data with the special paste:values of the new column
>
> Charlene, I don't have to tell you to back up your file before attempting
> anything anyone recommends to you.
>
> Don't ask me why this might work, but it has resolved my vlookup issues in
> the past.
>
> Peace
>
>
>
> "charlene leblanc" wrote:
>
> > I have inherited two spreadsheets that are linked and a VLOOKUP in one is
> > referring to a range in the other. The VLOOKUPs all work on the existing
> > data but as when I add new data, bearing in mind the formatting of the cells
> > and the order of the lookup range, those do not work. I get the #N/A error.
> >
> > Any ideas?

[charlene leblanc]

OK I did the formula to replace the contents of both the lookup_value and the
table_array columns with no change in the result. The range_lookup is a text
value.

Charlene

"charlene leblanc" wrote:

> It's worth a try, but I'm not sure what you mean by 'root data'. Using the
> documented syntax of
>
> "VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)"
>
> Do you mean doing a copy of the lookup_value column or the table_array, or
> the range_lookup? I guess I could do all three ....
>
> Thanks,
> Charlene
>
> "Cyberindio" wrote:
>
> > When all else has failed for me, Charlene, I have done this.
> >
> > Insert a new column next to your Vlookup root data column
> >
> > enter this formula: =(A2&"")+0 (assuming your root data starts in cell A2)
> >
> > slide a copy paste of the formula all the way down to copy your whole data
> > column
> >
> > replace your root data with the special paste:values of the new column
> >
> > Charlene, I don't have to tell you to back up your file before attempting
> > anything anyone recommends to you.
> >
> > Don't ask me why this might work, but it has resolved my vlookup issues in
> > the past.
> >
> > Peace
> >
> >
> >
> > "charlene leblanc" wrote:
> >
> > > I have inherited two spreadsheets that are linked and a VLOOKUP in one is
> > > referring to a range in the other. The VLOOKUPs all work on the existing
> > > data but as when I add new data, bearing in mind the formatting of the cells
> > > and the order of the lookup range, those do not work. I get the #N/A error.
> > >
> > > Any ideas?

[Tom Ogilvy]

You say everything is fine and is as it should be for vlookup to work, but
vlookup doesn't work.

Simple logic will tell you that the first part of the statement is then
incorrect.

--
Regards,
Tom Ogilvy

"charlene leblanc" <charleneleblanc@discussions.microsoft.com> wrote in
message news:D38C49E2-056C-46F9-8595-8608385E763F@microsoft.com...
> OK I did the formula to replace the contents of both the lookup_value and
the
> table_array columns with no change in the result. The range_lookup is a
text
> value.
>
> Charlene
>
> "charlene leblanc" wrote:
>
> > It's worth a try, but I'm not sure what you mean by 'root data'. Using
the
> > documented syntax of
> >
> > "VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)"
> >
> > Do you mean doing a copy of the lookup_value column or the table_array,
or
> > the range_lookup? I guess I could do all three ....
> >
> > Thanks,
> > Charlene
> >
> > "Cyberindio" wrote:
> >
> > > When all else has failed for me, Charlene, I have done this.
> > >
> > > Insert a new column next to your Vlookup root data column
> > >
> > > enter this formula: =(A2&"")+0 (assuming your root data starts in cell
A2)
> > >
> > > slide a copy paste of the formula all the way down to copy your whole
data
> > > column
> > >
> > > replace your root data with the special paste:values of the new column
> > >
> > > Charlene, I don't have to tell you to back up your file before
attempting
> > > anything anyone recommends to you.
> > >
> > > Don't ask me why this might work, but it has resolved my vlookup
issues in
> > > the past.
> > >
> > > Peace
> > >
> > >
> > >
> > > "charlene leblanc" wrote:
> > >
> > > > I have inherited two spreadsheets that are linked and a VLOOKUP in
one is
> > > > referring to a range in the other. The VLOOKUPs all work on the
existing
> > > > data but as when I add new data, bearing in mind the formatting of
the cells
> > > > and the order of the lookup range, those do not work. I get the
#N/A error.
> > > >
> > > > Any ideas?