Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP format issues?
I am using the VLOOKUP function in Excel (office 2007) and it is not working
for me. I checked the forums and they had the usual things to check: remove special characters, trim the data, format everything as TEXT, copy/paste special all formulas to be values, etc. I have already tried these things before looking for a solution. I was quite well versed on VLOOKUP for Office 2003 and never had any problems. This is the second time I've had problems with 2007 and I do not know any other options to try. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP format issues?
It would help it we could see your formula.
VLOOKUP works in all versions of Excel in exactly the same way best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Craig4321" wrote in message ... I am using the VLOOKUP function in Excel (office 2007) and it is not working for me. I checked the forums and they had the usual things to check: remove special characters, trim the data, format everything as TEXT, copy/paste special all formulas to be values, etc. I have already tried these things before looking for a solution. I was quite well versed on VLOOKUP for Office 2003 and never had any problems. This is the second time I've had problems with 2007 and I do not know any other options to try. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP format issues?
As requested:
=VLOOKUP(D380,'x-ref 54 Germany for TRS parts in Trinidad.xls'!$F$2:$G$39090,2,FALSE) I've also noticed that if I click into the formula bar for a cell and type <return then the format apparently "registers" and the VLOOKUP will work. Obviously I do not want to click into each cell and hit <return as the spreadsheet is over 1000 lines and I regularly have spreadsheets with 30,000 lines! The little green corner that indicates a special format or error situation does not help as the VLOOKUP works when everything is TEXT and the only option is to change my TEXT to NUMBER - the opposite of what I want. I'm sure this is because i have already formatted the column as TEXT. "Bernard Liengme" wrote: It would help it we could see your formula. VLOOKUP works in all versions of Excel in exactly the same way best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Craig4321" wrote in message ... I am using the VLOOKUP function in Excel (office 2007) and it is not working for me. I checked the forums and they had the usual things to check: remove special characters, trim the data, format everything as TEXT, copy/paste special all formulas to be values, etc. I have already tried these things before looking for a solution. I was quite well versed on VLOOKUP for Office 2003 and never had any problems. This is the second time I've had problems with 2007 and I do not know any other options to try. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP format issues?
I found a solution. Even though i've already formatted the column as TEXT
Excel doesn't seem to register the format. Only after i click *into* the cell (or formula bar) and hit <enter will it register and let the VLOOKUP work. However, I inserted a new column, formatted <general and then put a text formula in to reference the column that is not working with VLOOKUP. Formula example: =LEFT(D579,LEN(D579)) This converts the whole cell to TEXT and the VLOOKUP works if it references the new column with the above formula. "Craig4321" wrote: As requested: =VLOOKUP(D380,'x-ref 54 Germany for TRS parts in Trinidad.xls'!$F$2:$G$39090,2,FALSE) I've also noticed that if I click into the formula bar for a cell and type <return then the format apparently "registers" and the VLOOKUP will work. Obviously I do not want to click into each cell and hit <return as the spreadsheet is over 1000 lines and I regularly have spreadsheets with 30,000 lines! The little green corner that indicates a special format or error situation does not help as the VLOOKUP works when everything is TEXT and the only option is to change my TEXT to NUMBER - the opposite of what I want. I'm sure this is because i have already formatted the column as TEXT. "Bernard Liengme" wrote: It would help it we could see your formula. VLOOKUP works in all versions of Excel in exactly the same way best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Craig4321" wrote in message ... I am using the VLOOKUP function in Excel (office 2007) and it is not working for me. I checked the forums and they had the usual things to check: remove special characters, trim the data, format everything as TEXT, copy/paste special all formulas to be values, etc. I have already tried these things before looking for a solution. I was quite well versed on VLOOKUP for Office 2003 and never had any problems. This is the second time I've had problems with 2007 and I do not know any other options to try. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP format issues?
For future reference:
Preexisting numbers in a range where you set the formatting as "Text" will continue to be interpreted as numbers by Excel until you edit the cell or enter new data. Rather than maintain an additional column, create a temporary column with your formula then copy the temp column and paste "Values" only into the original column. Delete the temp column and continue to use the original formula. Best regards, Dave "Craig4321" wrote in message ... I found a solution. Even though i've already formatted the column as TEXT Excel doesn't seem to register the format. Only after i click *into* the cell (or formula bar) and hit <enter will it register and let the VLOOKUP work. However, I inserted a new column, formatted <general and then put a text formula in to reference the column that is not working with VLOOKUP. Formula example: =LEFT(D579,LEN(D579)) This converts the whole cell to TEXT and the VLOOKUP works if it references the new column with the above formula. "Craig4321" wrote: As requested: =VLOOKUP(D380,'x-ref 54 Germany for TRS parts in Trinidad.xls'!$F$2:$G$39090,2,FALSE) I've also noticed that if I click into the formula bar for a cell and type <return then the format apparently "registers" and the VLOOKUP will work. Obviously I do not want to click into each cell and hit <return as the spreadsheet is over 1000 lines and I regularly have spreadsheets with 30,000 lines! The little green corner that indicates a special format or error situation does not help as the VLOOKUP works when everything is TEXT and the only option is to change my TEXT to NUMBER - the opposite of what I want. I'm sure this is because i have already formatted the column as TEXT. "Bernard Liengme" wrote: It would help it we could see your formula. VLOOKUP works in all versions of Excel in exactly the same way best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Craig4321" wrote in message ... I am using the VLOOKUP function in Excel (office 2007) and it is not working for me. I checked the forums and they had the usual things to check: remove special characters, trim the data, format everything as TEXT, copy/paste special all formulas to be values, etc. I have already tried these things before looking for a solution. I was quite well versed on VLOOKUP for Office 2003 and never had any problems. This is the second time I've had problems with 2007 and I do not know any other options to try. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stock Format Issues | Charts and Charting in Excel | |||
Vlookup issues | Excel Discussion (Misc queries) | |||
.txt format issues | Excel Discussion (Misc queries) | |||
Sumproduct and format issues | Excel Worksheet Functions | |||
format saving issues | Excel Discussion (Misc queries) |