VLOOKUP format issues?

[Craig4321]

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.

 

[Bernard Liengme]

It would help it we could see your formula.
VLOOKUP works in all versions of Excel in exactly the same way
best wishes

 

[Craig4321]

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.

 

[Craig4321]

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.

 

[Bassman62]

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