VLookup only works on particular entries in the column being looke

[Guest]

I have a vlookup formula in spreadsheet A picking up a value in spreadsheet
B. There are 3 possible entries in the column in Spreadsheet B - N,W or C. If
spreadsheet B is not open and the links are updated in Spreadsheet A, then
only the cells with a N in are picked up, if the cell has a W or a C in it
then #N/A is shown. It only picks up the W or C if spreadsheet B is open. If
I change a W or C in spreadsheet B to a N, save it, shut B down and reopen
spreadsheet A then it now picks up the changed value.

I've checked all formats and formulas and nothing is wrong. The only thing
it doesn't like if the letters W or C in this spreadsheet.

Any suggestions on why this could happen or how to fix it are extremely
appreciated.

 

[Guest]

It would help if you posted the exact formula you are using.

 

[EagleOne]

Becky,

Make sure that the data being searched (SS A) is exactly what is in the Lookup Table in (SS B).

If there is a space and/or unprintable "control characters" in A and not in B there will be no-find.
BTW, do not trust "the way is looks" on the screen.

Many times when data is obtained in a download from a mainframe one or the other critical fields may
well be "garbaged-up"

EagleOne

 

[Guest]

The formula I used was
=VLOOKUP($A8,'S:\Operations_Assembly\PUBLIC FOLDER\Working Document\[Working
Document T500.xls]Actuals Input Sheet'!$1:$22750,6,FALSE).

 

[EagleOne]

Becky,

If you find that manually changing some of the data makes it work, post back and I'll send you a
very easy VBA procedure to run against the data in your W/S to clear out any download garbage.

EagleOne

 

[Guest]

I have checked that. It's picking up the value to lookup ok (the leftmost
cell) as other lookups in the same row looking up different column numbers
are picking up correctly. It's only when this particular column shows a W or
a C that it doesn't work if spreadsheet B isn't open. If it's a N or the
spreadsheet is open then it works ok.

None of the data is downloaded.

 

[Guest]

Changing the value in the cell that I want to return makes it work. Changing
it to N works, changing it to W or C stops it working.