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.

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.

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.

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.

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.