I am doing a vlookup in a template that has several year sheet. The common
lookup for this template is in column A and is set up as text ex(0834563)but
when I do the lookup I get na for some of the years and some work okay. I
have tried to set up the column as general but I does not seem to work. Any
suggestions as to when I put in the lookup I get all data for the years I
want? Any help is greatly appreciated.
--
thank you mac

On Jun 15, 5:51 pm, mac wrote:
I am doing a vlookup in a template that has several year sheet. The common
lookup for this template is in column A and is set up as text ex(0834563)but
when I do the lookup I get na for some of the years and some work okay. I
have tried to set up the column as general but I does not seem to work. Any
suggestions as to when I put in the lookup I get all data for the years I
want? Any help is greatly appreciated.
--
thank you mac

Are you absolutely sure the lookup value matches the items in the
first column of the database? No spaces, leading zeroes, letter O
instead of numeral 0, or other typo issues.

Hello Spiky,

Yes, I have checked every column and the all have general as the format. If
I re-enter the cell it fixes it, but I did not want to rekey all the cells
(250 on each sheet).


thank you mac


"Spiky" wrote:

On Jun 15, 5:51 pm, mac wrote:
I am doing a vlookup in a template that has several year sheet. The common
lookup for this template is in column A and is set up as text ex(0834563)but
when I do the lookup I get na for some of the years and some work okay. I
have tried to set up the column as general but I does not seem to work. Any
suggestions as to when I put in the lookup I get all data for the years I
want? Any help is greatly appreciated.
--
thank you mac

Are you absolutely sure the lookup value matches the items in the
first column of the database? No spaces, leading zeroes, letter O
instead of numeral 0, or other typo issues.

If they work after re-entry, then they didn't match prior to re-
entry.

Cell formatting doesn't change the underlying data type - cells
formatted as general can have any data type in them, only the
appearance changes.

If there isn't a compelling reason to format as text, you can convert
the lookup ranges to values which should get your lookups hooked up.
Example:
Sub vert()
With Worksheets("Sheet1").Columns("A")
.NumberFormat = "General"
.Value = .Value
End With
End Sub

To "re-enter" data, you don't have to type it back into the cell -
just select the cell, hit f2 then enter.

Cliff Edwards

On Jun 17, 4:45 am, mac wrote:
Hello Spiky,

Yes, I have checked every column and the all have general as the format. If
I re-enter the cell it fixes it, but I did not want to rekey all the cells
(250 on each sheet).

thank you mac

"Spiky" wrote:
On Jun 15, 5:51 pm, mac wrote:
I am doing a vlookup in a template that has several year sheet. The common
lookup for this template is in column A and is set up as text ex(0834563)but
when I do the lookup I get na for some of the years and some work okay. I
have tried to set up the column as general but I does not seem to work. Any
suggestions as to when I put in the lookup I get all data for the years I
want? Any help is greatly appreciated.
--
thank you mac

Are you absolutely sure the lookup value matches the items in the
first column of the database? No spaces, leading zeroes, letter O
instead of numeral 0, or other typo issues.

Yeah, I didn't mean formats. I meant the actual text/value in the
cell. I always find it best to copy when using Lookups so that I don't
make typos and screw up my calculations. So, enter the Lookup column
data somewhere, then always copy from that to assure 100% matching.

On Jun 17, 8:58 am, ward376 wrote:
If they work after re-entry, then they didn't match prior to re-
entry.


To "re-enter" data, you don't have to type it back into the cell -
just select the cell, hit f2 then enter.

Cliff Edwards

I just wanted to point out that these two comments do not work
together. If "re-entry" fixes a lookup problem because of a typo, then
F2 and Enter are not "re-entry".

You're right, that wasn't very clear. They weren't meant to work
together...

If they work after re-entry, then they didn't match prior to re-
entry.
Re-entering the same info into a cell will prompt Excel to convert the
data into the data type and format it thinks it is, unless the cell is
formatted as text. So re-entering the data may change the data type.

To "re-enter" data, you don't have to type it back into the cell -
just select the cell, hit f2 then enter.
This is a tip, just making sure you knew you didn't have to type every
character into cells, you can just enter edit mode with f2 to "re-
enter" if the issue is the data types not matching between the lookup
value and the lookup table. Of course typos are an entirely different
matter.

Cliff Edwards