Index and match functions help needed.

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)


"Zak" <[email protected]> wrote in message
news:[email protected]...
> 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

yeah i did array enter it but it wont work


"Bob Phillips" wrote:

> 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)
>
>
> "Zak" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

... 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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

... 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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

yeah i did array enter it but it wont work


"Bob Phillips" wrote:

> 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)
>
>
> "Zak" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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)


"Zak" <[email protected]> wrote in message
news:[email protected]...
> 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

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

... 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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

yeah i did array enter it but it wont work


"Bob Phillips" wrote:

> 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)
>
>
> "Zak" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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)


"Zak" <[email protected]> wrote in message
news:[email protected]...
> 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

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

... 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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

yeah i did array enter it but it wont work


"Bob Phillips" wrote:

> 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)
>
>
> "Zak" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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)


"Zak" <[email protected]> wrote in message
news:[email protected]...
> 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