Problem with Vlookup in Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

keeno - 13 Feb 2008 05:45 GMT

Please help this beginner! I export a Foxpro database to an Excel
spreadsheet. In a separate (job quote) spreadsheet I try to match a quote
number to it's corresonding row in the exported spreadsheet and fill in cells
in the quote sheet. What I get is #N/A. If I format the column in the
exported spreadsheet as either a number with no decimal places, or
General--the problem persists. But if I re-type the number (in the exported
spreadsheet) the number goes from left justified to right justified and now
the Vlookup formula in the quote sheet works. The exported spreadsheet grows
daily and I'd like to be able to find an automated way to use that data. Why
do I have to re-type the info?

Reply to this Message

Arvi Laanemets - 13 Feb 2008 06:39 GMT

Hi

In exported table, the field is text, i.e values there are interpreted as
strings like "12345". In other sheet, you entered numbers, like 12345. For
Excel, those are different values and don't equal.

Possible solutions:
a) modify the query from dbf-table, so that numeric string in this column is
converted to number;
b) modify another sheet, so that instead numbers numeric strings are in
according column;
c) or change the VLOOKUP formula like
=VLOOKUP("" & SearchValue, RangeToSearch, ColumnNum,0)

PS. For a) or b), I advice to format the leftmost column as Text, and then
requery. Otherwise whenever you happen to activate edit mode in any cell in
this column (double-clicking on it or pressing F2), the value there is
converted to number, and you get #N/A again.

Signature

Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

> Please help this beginner! I export a Foxpro database to an Excel
> spreadsheet. In a separate (job quote) spreadsheet I try to match a quote
[quoted text clipped - 11 lines]
> Why
> do I have to re-type the info?

Reply to this Message

Gord Dibben - 13 Feb 2008 18:06 GMT

Format all to General.

Copy an empty cell.

Select the range of bogus numbers abd Edit>Paste Special>Add>OK>Esc.

Gord Dibben MS Excel MVP

>Please help this beginner! I export a Foxpro database to an Excel
>spreadsheet. In a separate (job quote) spreadsheet I try to match a quote
[quoted text clipped - 6 lines]
>daily and I'd like to be able to find an automated way to use that data. Why
>do I have to re-type the info?

Reply to this Message