I have a very simple table of cost centre numbers and associated cost centre
names in the next column.
This table in a spreadsheet worksheet, links directly to our accounts package
through a template function supplied our accounts package supplier. This
table is fine.

V lookup is used in other workbooks to put the name in the next cell to the
cost centre number.
Vlookup will work, up to a certain cost centre number, but then, for no
apparent reason will displace the names by one line number. therefore, after
Cost centre 500, the name that applies to cost centre 490, the cost centre
immediately preceeding 500, appears in the adjacent cell, where you would
expect the name for CC500 to appear.

These lookups all worked fine, until I loaded Office 2003 SP 3 when the
problems started.

I suspect the last parameter in your VLOOKUP function. It should be set to
false if you want only exact matches.

Check Help for descriptions of the parameters. If the last one is omitted
then it defaults to True and If an exact match is not found, the next largest
value that is less than lookup_value is returned.

If this doesn't answer your question then please post a copy of your
formula. (Highlight it in the formula bar and copy. Press Enter and then
paste it into this post.)

Regards,

OssieMac

"POD27" wrote:

I have a very simple table of cost centre numbers and associated cost centre
names in the next column.
This table in a spreadsheet worksheet, links directly to our accounts package
through a template function supplied our accounts package supplier. This
table is fine.

V lookup is used in other workbooks to put the name in the next cell to the
cost centre number.
Vlookup will work, up to a certain cost centre number, but then, for no
apparent reason will displace the names by one line number. therefore, after
Cost centre 500, the name that applies to cost centre 490, the cost centre
immediately preceeding 500, appears in the adjacent cell, where you would
expect the name for CC500 to appear.

These lookups all worked fine, until I loaded Office 2003 SP 3 when the
problems started.

Thanks Ossie,

Still no luck, I tried False and True in the formula.

I can't help but think that something corrupted, as why would it work
differently with the same formula in different cells.

=VLOOKUP(I7,'F:\COMMUNAL FOLDER\Accounts\Information\CHARITY\[Full Chart Of
Accounts Write Back.xls]CAT_1_CC'!$A$6:$B$245,2,FALSE)

There is the formula above.
Category 1 Category 1name
111 GLOUCESTER H.M/M BUILDINGS
190 LANSDOWNE HOUSE
191 MINSTER HOUSE
506 #N/A
511 #N/A
521 #N/A
523 #N/A
526 #N/A
860 #N/A
870 #N/A
995 #N/A
106 EARL SHILTON SENIOR CITS.CEN.
111 GLOUCESTER H.M/M BUILDINGS
190 LANSDOWNE HOUSE
191 MINSTER HOUSE
506 #N/A
511 #N/A

A selection of results above.

The source is below.
Category Code Category Name
000 BALANCE SHEET
100 EXEC.DIR,SECRETARIAL & RECEPTN
101 HEAD OF INFO & ADVICE
102 HEAD OF FIELDWORK
103 SERVICE MANAGER SOUTH
104 SERVICE MANAGER EAST
105 NORTH CHARNWOOD C.M.H.T.
106 EARL SHILTON SENIOR CITS.CEN.
107 NORTH WEST C.M.H.T.
108 SOUTH LEICS C.M.H.T.
109 SERVICE MANAGER NORTH
111 GLOUCESTER H.M/M BUILDINGS
112 PATHFINDER HINCKLEY SCHEME
113 WEST LEICS C.M.H.T.
114 MELTON MOWBRAY N'HOOD CARE
115 MOHABATTEIN DANCE EVENT
116 NWL COALVILLE GENERAL & ADMIN
117 GLENFIELD CARERS J.FORBES
118 SERVICE MANAGER WEST
119 TRANSPORT/PURCHASING MANAGER
120 X556OGO LDV CONVOY MINIBUS
121 H741WCH TALBOT MINIBUS
122 FL51EMJ IVECO VAN MAINTENANCE
125 J757NNR FORD M/HARBORO
128 C76BKK RENAULT BIRSTALL
129 Y851UPY SHOPS VAN (EX H91FJU)
139 L84CRY
140 GENERAL ADMINISTRATION
141 VOLUNTARY INCOME/EXPENDITURE
142 FINANCIAL INCOME/EXPENDITURE
143 HOLIDAY I & E ACCOUNT
144 AC LOTTERY COMMISSION
145 TRANSPORT & TRADING GIFT AID DONATIONS
146 INSURANCE CO. GIFT AID DONATIONS
147 BUSINESS DEV.OFF.(Barclays)
148 E. SHILTON N'HOOD CARE SCHEME
149 HOME CARE/HELP COMPANIES/WELFARE
150 PROPERTY MANAGEMENT
151 3M SPONSORSHIP
154 B-SEEN PROJECT
155 TRUSTEES
156 FP56 TFF FORD MONDEO ESTATE (TONY)
157 FM04 BNJ FORD MONDEO ESTATE
158 BU52BWJ VAUXHALL ZAFIRA 2.0
159 SPARE
160 FY53 FKE LANDROVER FREELANDER
162 FE07NRF FORD MONDEO EDGE (PAUL)
168 SPARE
170 FP05 ZHE FORD FOCUS 1.6 EDGE (DENISE)
171 SPARE
172 SPARE
173 SPARE
174 SPARE
175 BANK OF IRELAND ASSET MANAGEMENT INV
176 YORKSHIRE BANK MLC INVESTMENT
178 SPARE
179 SPARE
180 SPARE
181 ADVICE & ADVOCACY (COMM.FUND)
182 NWL INFORMATION & ADVICE SERVICE (COALFIELD)
183 UPPINGHAM DEVELOPMENT WORKER
184 FIELDWORK ASSISTANT
185 CO-ORDINATOR (ASIAN SERVICES)
186 FIELDWORKER (CEASED)
187 ADVICE AND INFO.WORKERS
188 E.M.E.S.O.
189 COMMUNITY FOOD WORKER
190 LANSDOWNE HOUSE
191 MINSTER HOUSE
192 MAINTENANCE DEPARTMENT
193 MEASHAM TEMPERANCE HALL
194 INVESTORS IN PEOPLE COSTS
195 CO-ORDINATOR (TRAINING)
196 PERSONNEL
197 FINANCE & PAYROLL
198 PURCHASING DEPARTMENT
199 IT & DATA PROTECTION
200 FIELDWORK (ASST. DIRECTOR FIELDWORK)
201 BOTTESFORD (PF) THUR DC
202 COTTESMORE (PF) MON DC/LC
203 GLOS HOUSE (PF) TUES DC
204 GLOS HOUSE (PF) FRI DC
205 WESTGATE OAKHAM (PF) MON DC/LC
206 BURTON LAZARS (PF) TUES DC (EX BRMPTN RD)
207 FLECKNEY (PF) TUES DC
208 CONNECTIONS M/H (PF) M T TH DC
209 UPPINGHAM (PF) WEDS/THUR DC/LC
210 UPPINGHAM WEDS (CEASED)
211 ACORN OAKHAM (PF) TUE DC/LC
212 GLOS HOUSE (LD) (Horizons) WED/THUR DC
214 BILLESDON (PF) WEDS DC
215 G/HOUSE (MF) 4/5 DAY PERI DC
216 EMPINGHAM DAY CENTRE
230 CASTLE DONINGTON (MF) MON DC
231 M'SORREL PARISH RMS(PF) WED DC
232 ANPURNA(SRK) (PF) M W Th F DC
234 FREDERICK ST LBRO (PF) WED DC
235 FOREST ROAD (PF) FRID DC
236 MOIRA WOULDS CT (PF) THURS DC
238 BIRSTALL (PF) TUES DC
239 THURMASTON (PF) THUR DC
241 CASTLE DONINGTON (PF) WEDS DC
242 M'SORREL MEM HALL (PF) MON DC
243 ANAND GRIHO(YWCA) (PF) TUES DC
244 FREDERICK ST (MF) THURS DC
245 BIRSTALL (MF) MON WED DC
246 MEASHAM (PF) TUES FRI DC
248 ASHBY WOULDS (PF) MON DC
249 KEGWORTH (PF) FRID DC
250 ST JAMES (AC B'TLL) THUR DC
260 NWL CHOICE DC
261 NWL COALVILLE DC
270 EARL SHILTON (PF) TUES DC
271 EARL SHILTON WEDS (CEASED)
272 EARL SHILTON (MF) WEDS THUR DC
273 EARL SHILTON (PF) FRI DC
274 BARLESTONE (PF) MON DC
275 NARBOROUGH (PF) MON & WEDS DC
276 BARLESTONE (PF) TUES DC/LC
277 BARWELL (PF) THURS DC
278 ST.FRANCIS (PF) WED DC
279 LFE WINSTANLEY (PF) MON DC/LC
281 EARL SHILTON (LD) M-F DC
283 STH LEICS (MF) TUES WEDS DC
284 PEVERAL CT (PF) TUES WEDS DC
285 ORCHID L/HOUSE (LD) M-F DC
286 EARL SHILTON (PF) MON DC
287 HINCKLEY (PF) TUES DC
288 BHAVIC OADBY (PF) FRI DC
312 MKT.HARBORO LC SA613
320 SAPCOTE LC SA616
321 UPPINGHAM MON.LC NA616
323 STH CHARNWOOD PATHFINDER L/C
324 UPPINGHAM L/C FRIDAY
330 NWL SALTERSFORD LC
351 EARL SHILTON A.C.LUNCH CLUB
360 OAKHAM LC
361 RHYALL LC
362 WHISSENDINE LC
363 NORTH LUFFENHAM LC
364 EXTON LC (Closed 6/5/06)
370 OAKHAM ACTIVITIES & CLUBS
371 OAKHAM OFFICE
380 UPPINGHAM ACTIVITIES & CLUBS
405 WINTER WARMTH
406 PROMOTING HEALTHY LIVING FOR OLDER PEOPLE
407 SAFER HOMES PROGRAMME
408 ENERGY RIGHT (EON)
410 BME RESEARCH
422 BME CDW MH SERVICES
425 HANDYMAN SERVICE
438 BIRSTALL BATHING
450 CO-ORDINATOR (DAY BREAK)
461 RUTLAND BEFRIENDERS
465 NWL VOLUNTEER VISITING SCHEME
481 PASANDGI GROUP (OADBY)
482 SATHI DROP IN GROUP
484 RAANG TALI GROUP
486 NAMASTE EXERCISE GROUP (EX SWAGAT)
487 SHAKTI EXERCISE GROUP
489 OADBY EXERCISE GROUP
490 SOCIAL REHABILITATION PILOT
500 SHOPS UNALLOCATED EXPENSES
501
502
503 BLABY CLOTHES SHOP
504 MARKET HARB CLOTHES SHOP
505 ANSTEY CLOTHES SHOP
506 UPPINGHAM SHOP(13 HIGH ST EAST)
507 FURNITURE MANAGER COSTS
508 SYSTON CLOTHES SHOP
509
510 WIGSTON CLOTHES SHOP
511 BLABY FURNITURE SHOP
512
513 BIRSTALL FURNITURE SHOP
514 MELTON MOWBRAY MIXED SHOP
515 LOUGHBORO BOOKSHOP
516
517 SYSTON NEW (Under Dev)
518 HINCKLEY (exBIG SHOP)
519 EARL SHILTON CLOTHES
520 UPPINGHAM CLOTHES SHOP
521 SHEPSHED CLOTHES SHOP
522 LUTTERWORTH MIXED SHOP
523 QUEENS ROAD BOOKSHOP
524
525 MARKET HARBOROUGH BOOKSHOP
526 OADBY SHOP FURNITURE
527 OADBY CLOTHES SHOP
528
599 HOUSE CLEARANCE SERVICE
600 CLEARING ACCOUNTS
651 S510KNR TRANSIT VAN (EARL SHILTON)
654 FP 53 BGU Mkt Harb Vehicle
655 FG03 CMY VAN (BLABY)
656 FJ54WLK IVECO MINIBUS
657 YU02MBF HOUSE CLEARANCE VAN
659 FL51OMH HANDYMAN VAN
660 IVECO LUTON VAN FN06FZS(B'STALL)
700 A.C LEICS INSURANCE (ACLIS)
750 D NUTTALL HOLIDAY PROMOTIONS
770 MERGER COSTS
803 RUTLAND DIRECT PAYMENTS SUPPORT
804 OLDER PRISONER'S ADVOCACY
810 LINK TO WORK LE/001/04
815 LSC/ESF LEARNING OPPORTUNITIES
820 NURSING HOME ADVISORY SERVICE
821 MONEY TRAIL
822 CARE HOMES ADVOCACY PROJECT
823 DIRECT PAYMENTS ADVOCACY SERVICE
824 ADVISOR TO AGE DISCRIMINATION
825 EAST MIDLANDS COMMON RECORDING SYSTEM
826 CLS - LEGAL INFORMATION
827 OLDER PERSONS CONSULTATION
828 MOUNTSORREL BENEFITS PROJECT
829 REAPING BENEFITS
830 TELECARE PROJECT (SIGNAL BUS)
833 MEASHAM OUTINGS CLUB
840 AGE RESOURCE DESK
842 COMPUTER CLASSES LANSDOWNE SUITE
843 WIRELESS OUTREACH NETWRK
844 BT SILVER SURFER PROGRAMME
845 LCC - SLA DIRECT PAYMENTS
850 D.A.L.E PROJECT
857 VOLUNTEERING FOR ALL
860 28 THE SQUARE MARKET HARB
870 45 NOTTINGHAM STREET MELTON
900 ASHBY HOOD COURT OLD(CEASED)
901 ASHBY COURT MEALS ON WHEELS
910 ASIAN COMMUNITY MEALS
930 HOME OFFICE ACTIVE COMM. UNIT
955 EAST MIDLANDS FORUM
960 GOLF DAY
964 ASIAN LANGUAGE SCHEME
995 RESOURCE CENTRE (L. H.)
996 WELFARE COMPANY
997 TRANSPORT AND TRADING CO.
998 HOMEHELP
999 HOMECARE

I know that the formats are consistent, I have had trouble with these before.

many thanks for your help




OssieMac wrote:
I suspect the last parameter in your VLOOKUP function. It should be set to
false if you want only exact matches.

Check Help for descriptions of the parameters. If the last one is omitted
then it defaults to True and If an exact match is not found, the next largest
value that is less than lookup_value is returned.

If this doesn't answer your question then please post a copy of your
formula. (Highlight it in the formula bar and copy. Press Enter and then
paste it into this post.)

Regards,

OssieMac

I have a very simple table of cost centre numbers and associated cost centre
names in the next column.
[quoted text clipped - 12 lines]
These lookups all worked fine, until I loaded Office 2003 SP 3 when the
problems started.

Hi Again,

I reloaded the the spreadsheet that the vlookup feeds off from a back up tape,
and this corrected things.

When I refreshed this table, that links into our accounts package through an
ODBC arrangment, the same problem was encountered. I assume therefore, the
underlying problem must be with data extracted from the accounts package,
which although it looks fine, creates a problem when interrogated through
vlookup.

I have contacted our accounts package dealers.

many thanks

OssieMac wrote:
I suspect the last parameter in your VLOOKUP function. It should be set to
false if you want only exact matches.

Check Help for descriptions of the parameters. If the last one is omitted
then it defaults to True and If an exact match is not found, the next largest
value that is less than lookup_value is returned.

If this doesn't answer your question then please post a copy of your
formula. (Highlight it in the formula bar and copy. Press Enter and then
paste it into this post.)

Regards,

OssieMac

I have a very simple table of cost centre numbers and associated cost centre
names in the next column.
[quoted text clipped - 12 lines]
These lookups all worked fine, until I loaded Office 2003 SP 3 when the
problems started.