If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|