VLOOKUP

The UK's most regular instructor-led training courses.
Training information: excel training · Excel courses london · Microsoft Excel Training London
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Low Priority · Version Standard

VLOOKUP

John has attended:
Excel Advanced course

by - delegate John [2 posts] (2008 Mar 18 Tue, 16:05) Reply

Is it possible to change the standard error message from #N/A to something more user friednly such as "No match" or "0"

Version	Date	Location	Places available	Book	Next place rate:
Excel VBA 2 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003	2009 Jan 22 Thu + 23 Fri	Southwark	0	FULL
2003	2009 Jan 29 Thu + 30 Fri	Bayswater	4	book now	£475	£485
2007	2009 Feb 5 Thu + 6 Fri	Bloomsbury	5	book now	£485	£490
2003	2009 Feb 19 Thu + 20 Fri	Southwark	4	book now	£475	£485
2003	2009 Feb 26 Thu + 27 Fri	Bayswater	8	book now	£335	£365
2003	2009 Mar 12 Thu + 13 Fri	Bloomsbury	7	book now	£345	£399
Full Schedule: See all 43 Excel VBA course dates. Bookings currently available until 18th March 2010.

RE: VLOOKUP

by - trainer Rich platinum contributer [755 posts] (2008 Mar 20 Thu, 20:27) Reply

try mixing vlookup with IF statements:

=IF(ISERROR(VLOOKUP(B2,Range1,1,FALSE)),0,VLOOKUP(B2,Range1,1,FALSE))

Please find attached an example

Attached files...

Vlookup-example.xls

· Reasons Why VBA for Excel Training Fails
· Microsoft Excel Training Courses or Consultants: Which Is Better For Your Company?
· Why Should I Learn Visual Basic in Excel?
· Microsoft Excel Features and Functions for Accountants
· Tips for Transitioning to Excel 2007

Please browse our web site to find out more about
excel training and other Microsoft training courses.

Excel tip:

Bracketed negative numbers

Often Excel users wish to display negative numbers in colour red and bracketed

Intstructions
Step1. Select Format > Cells menu options. Within Numbers tabsheet, select Category = Custom.
Step 2. Select a type such as #,##0;[Red]-#,##0;; that specifies a colour in square brackets.
Step 3. Amend as follows; #,##0;[Red](#,##0;;

Notes: Excel formatting featues are of the form
"Positive; Negative;Zero;Text" separated by semicolon.

View all Excel hints and tips

Rate this page: