The UK's Number 1 for Microsoft Office Training

Sitemap

Bookmark page

Printable version

Course updates
due to adverse
weather conditions

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel INDEX & MATCH Functions

	98.3 % recommend us
	More live stats...

Bookmark this page

Share this with a friend or colleague:

The UK's most regular
instructor-led training courses.

Training information:

Excel INDEX & MATCH Functions

Resolved · High Priority · Version 2003

ReplyFri 10 Oct 2008, 14:51Delegate Dee said...

Excel INDEX & MATCH Functions

I am using the following formula {=INDEX(BankHols,MATCH(Month1&Month2,StartDate&EndDate,0))} to lookup and display the value in column 3 of a table (sample below). The formula works correctly except when it reads a 0 value. If a 0 (zero) value is returned the #NA error is displayed.

How can I get Excel to read the table and correctly display the zero value?

Start Date End date No. of BHs
01-Apr-08 30-Apr-08 0
01-Jun-08 30-Jun-08 0
01-Jun-08 31-Aug-08 1
01-Jun-08 30-Sep-08 1
01-Apr-08 31-May-08 2
01-Apr-08 30-Jun-08 2
01-Apr-08 31-Aug-08 3
01-Apr-08 30-Sep-08 3
01-Jun-08 28-Feb-09 4
01-Apr-08 31-Dec-08 5
01-May-08 31-Mar-09 6

Regards,

Dee

For upcoming training course dates see: Pricing & availability

ReplyThu 23 Oct 2008, 14:40Trainer Laura said...

RE: Excel INDEX & MATCH Functions

Hi Dee

Thank you for the question.

Could you attach an example so I can fully understand the names you've used in your array formula?

Thanks

Laura GB

ReplyThu 30 Oct 2008, 11:16Trainer Rich said...

RE: Excel INDEX & MATCH Functions

Hi Laura,

Delegates can not currently upload files to the forum due to security reasons.

You can request they send files to forum

and they can be forwarded on to you, or can use another e-mail address of your choosing. If this is the case, it's best to mask the e-mail so spam-bots can not grab it.

Regards, Rich

ReplyMon 10 Nov 2008, 08:35Trainer Laura said...

RE: Excel INDEX & MATCH Functions

Thank you Rich for the advice.

Dee, if you send the file to forum

for my attention that would be great.

Regards

Laura GB

ReplyTue 18 Nov 2008, 10:49Trainer Rich said...

RE: Excel INDEX & MATCH Functions

Laura,

I have forwarded you the copy of Dee's file via e-mail.

Will keep this forum question as 'unresolved' for now.

Regards, Rich

ReplyThu 18 Dec 2008, 09:17Trainer Rich said...

RE: Excel INDEX & MATCH Functions

Hi,

We are clearing some of the outstanding questions in the forum. As this post has been inactive for quite some time, the system will automatically set the post to 'resolved' in the next 5 days unless a follow-up post is made within that time.

You can always mark the question as 'unresolved' at any point if you require further assistance with this matter.

Regards,
Best STL Forum Team

Thu 26 Mar 2009: Automatically marked as resolved.

Excel articles

· Using Headers And Footers In Excel 2010
· What Do The Formula Error Messages Mean In Excel?
· How Sharing Your Excel Data Can Help Your Business Flourish
· How Getting Together Is Now Even Easier With Excel
· Creating Charts with Excel

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

Excel tip:

Convert Text to Columns in Excel 2010

If you have a cell in your Excel spreadsheet that contains a lot of text and you want to divide it into separate columns, this can only be done if there is a logical character which separates the text, for example, a comma.

Select the cells you would like to convert. On the Data tab, click Text to Columns. Choose the format of your current data.

Select Delimited if the text contains a logical character otherwise select Fixed Width if there are a certain number of spaces between each field.

Click Next when a preview of the data appears. Then select the type of character that separates the various fields. If the character is not listed, select Other and enter the character.

Click Next again and then choose the format for each of the columns. Select the column heading in the Data preview and then select a data type from the Column data format options.

Click Finish and the text will appear in several columns.

View all Excel hints and tips

Windows Live Gives Computer Clouds A Silver Lining

Clouds are not only precursors to rain but are an important part of today's internet. Windows Live taps into them via Office Web Apps so you're able to share and edit files very easily.

» Article: Microsoft office training london

How do you fix a damaged spry tabbed panel?

» Forum post: Spry

To make sure that your tables if they carry on to subsequent pages have a Table header on each page.
Select the rows at the top of the table which you would like to repeat on subsequent pages, select Table, Heading Rows Repeat. Note this does not work i...