Index and match functions help needed.

[Guest]

I have the following column headings:

"Names" "Date" "Title Plan Reference Number" "Work Recording System" "Audit
Stage"

Now if I enter the name AND date i want the title to be automatiaclly
displayed for the corresponding date and name that I have entered. I used the
formula from the Excel help article that I read:
=INDEX(A2:C4,MATCH(E2&F2,A2:A4&B2:B4,0),3). In theory having read the help
article this is the formula I need. I have spent such a long time trying to
get the formula to work but it wont. I need help please

 

[Bob Phillips]

Looks good Zak.

Did you array-enter it, that is Ctrl-Shift-Enter rather than just Enter?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

yeah i did array enter it but it wont work

Looks good Zak.

Did you array-enter it, that is Ctrl-Shift-Enter rather than just Enter?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Max]

The formula works ok for me (tested here) ..

Perhaps there's an extra "invisible" space inadvertently entered in the name
input in E2 which is fouling up the match ?

Try, array-entered as before:
=INDEX(A2:C4,MATCH(TRIM(E2)&F2,A2:A4&B2:B4,0),3)

Does this work ?

Another possibility: dates in B2:B4 are not real dates

Select B2:B4, click Data > Text to columns, Next > Next

In step 3 of the wizard:
Under "Column data format"
Check "Date", and select the correct format from the droplist (eg: DMY)
Click Finish

 

[Max]

Resolved via an email exchange ..

The array formula was correctly entered and it correctly returned #N/A due
to no match found, but think this was misconstrued as "not working" by the
OP.

Link to sample file returned to the OP :
http://www.savefile.com/files/4538432
File: Index and match functions help needed_Zak_wksht.xls

 

[Max]

... and a nice closure note received from the OP ...
Date: Tue, 30 Aug 2005 05:16:39 -0700 (PDT)
Subject: Re: need help on excel please: Zak from Microsoft website
To: "Max"

Just wanted to say a big thank you for sorting that
problem out. Inputted the formulae and they work
perfectly. really appreciate the help you provided.

kind regards
Zak