Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with Vlookup in Excel
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with Vlookup in Excel
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. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "keeno" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with Vlookup in Excel
Format all to General.
Copy an empty cell. Select the range of bogus numbers abd EditPaste SpecialAddOKEsc. Gord Dibben MS Excel MVP On Tue, 12 Feb 2008 21:45:01 -0800, keeno wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup problem with Access Query import into Excel 2000 | New Users to Excel | |||
Vlookup problem | New Users to Excel | |||
Vlookup problem.. | Excel Worksheet Functions | |||
Excel Forumla (vlookup problem) | Excel Discussion (Misc queries) | |||
Excel Problem: VLookup | Excel Worksheet Functions |