I have a simple VLOOKUP(A2,Source.xls!production,5,FALSE) in the 'Original'
sheet
and everything is fine except some values. It gives #N/A. But, it's there in
the Source sheet.
The looking values are as A2 = SOS, A3 = STF, 6OC, etc.
When I go to the Sheet1 for the cells with #N/A and copy and paste the value
into the Original sheet I can get rid of all #N/A where it's only text as
STF, etc. But, for the cells where there is the first one is number such as
6OC or 2TT it continues to give me #N/A despite copying and pasting or even
substituting ref with the exact value as
VLOOKUP("2TT",Source.xls!production,5,FALSE).

Could anybody advise anything regarding this phenomenon?

Thanks

I've never seen a LookupTable specified exactly as you have in your sample
formula. If indeed your LookupTable is in the same workbook as where you
have your VLOOKUP formulas, and you have named the range of the table
"Production", then you do not have to specify anything else in the
formula.......

=VLOOKUP(A2,production,5,FALSE) would suffice.

If you have checked and insured against the usual "text vs. numbers"
formatting issues, then I suggest you check the exact settings for your
"Production" range. Some of the values you're looking up may appear in the
same list as the others, but may in fact be out of the "production" named
range.

The only other thing would be, unless your VLOOKUPS are "copied down", I
would review their exact construction, (on the ones that don't work).

hth
Vaya con Dios,
Chuck, CABGx3


"Alex" wrote:

I have a simple VLOOKUP(A2,Source.xls!production,5,FALSE) in the 'Original'
sheet
and everything is fine except some values. It gives #N/A. But, it's there in
the Source sheet.
The looking values are as A2 = SOS, A3 = STF, 6OC, etc.
When I go to the Sheet1 for the cells with #N/A and copy and paste the value
into the Original sheet I can get rid of all #N/A where it's only text as
STF, etc. But, for the cells where there is the first one is number such as
6OC or 2TT it continues to give me #N/A despite copying and pasting or even
substituting ref with the exact value as
VLOOKUP("2TT",Source.xls!production,5,FALSE).

Could anybody advise anything regarding this phenomenon?

Thanks

Thanks, CLR.
The mystery has been resolved. Due to the frozen window I missed some cells
in my 'production' range.

"CLR" wrote:

I've never seen a LookupTable specified exactly as you have in your sample
formula. If indeed your LookupTable is in the same workbook as where you
have your VLOOKUP formulas, and you have named the range of the table
"Production", then you do not have to specify anything else in the
formula.......

=VLOOKUP(A2,production,5,FALSE) would suffice.

If you have checked and insured against the usual "text vs. numbers"
formatting issues, then I suggest you check the exact settings for your
"Production" range. Some of the values you're looking up may appear in the
same list as the others, but may in fact be out of the "production" named
range.

The only other thing would be, unless your VLOOKUPS are "copied down", I
would review their exact construction, (on the ones that don't work).

hth
Vaya con Dios,
Chuck, CABGx3


"Alex" wrote:

I have a simple VLOOKUP(A2,Source.xls!production,5,FALSE) in the 'Original'
sheet
and everything is fine except some values. It gives #N/A. But, it's there in
the Source sheet.
The looking values are as A2 = SOS, A3 = STF, 6OC, etc.
When I go to the Sheet1 for the cells with #N/A and copy and paste the value
into the Original sheet I can get rid of all #N/A where it's only text as
STF, etc. But, for the cells where there is the first one is number such as
6OC or 2TT it continues to give me #N/A despite copying and pasting or even
substituting ref with the exact value as
VLOOKUP("2TT",Source.xls!production,5,FALSE).

Could anybody advise anything regarding this phenomenon?

Thanks

Glad you found it, and thanks for the feedback............

Vaya con Dios,
Chuck, CABGx3



"Alex" wrote:

Thanks, CLR.
The mystery has been resolved. Due to the frozen window I missed some cells
in my 'production' range.

"CLR" wrote:

I've never seen a LookupTable specified exactly as you have in your sample
formula. If indeed your LookupTable is in the same workbook as where you
have your VLOOKUP formulas, and you have named the range of the table
"Production", then you do not have to specify anything else in the
formula.......

=VLOOKUP(A2,production,5,FALSE) would suffice.

If you have checked and insured against the usual "text vs. numbers"
formatting issues, then I suggest you check the exact settings for your
"Production" range. Some of the values you're looking up may appear in the
same list as the others, but may in fact be out of the "production" named
range.

The only other thing would be, unless your VLOOKUPS are "copied down", I
would review their exact construction, (on the ones that don't work).

hth
Vaya con Dios,
Chuck, CABGx3


"Alex" wrote:

I have a simple VLOOKUP(A2,Source.xls!production,5,FALSE) in the 'Original'
sheet
and everything is fine except some values. It gives #N/A. But, it's there in
the Source sheet.
The looking values are as A2 = SOS, A3 = STF, 6OC, etc.
When I go to the Sheet1 for the cells with #N/A and copy and paste the value
into the Original sheet I can get rid of all #N/A where it's only text as
STF, etc. But, for the cells where there is the first one is number such as
6OC or 2TT it continues to give me #N/A despite copying and pasting or even
substituting ref with the exact value as
VLOOKUP("2TT",Source.xls!production,5,FALSE).

Could anybody advise anything regarding this phenomenon?

Thanks