Lookup depending upon 2 criteria
 

I am having a little difficulty with the Index / match functions.

I have a spreadsheet set out as follows
Date / Time CC Phase
14/11/2008 20:00:00 INTERRUPT
14/11/2008 20:00:00 B13 RELEASE
14/11/2008 21:00:00 SETUP
14/11/2008 21:10:00 B1B RELEASE

I would like to return the CC for for only when Phase=Release and Date /
Time = 14/11/2008: 20:00:00

Any Advice would be greatly appreciated.

Lookup depending upon 2 criteria
 

Assuming data as posted in A2:C5
try this in say E2, normal ENTER:
=INDEX(B2:B5,MATCH(1,INDEX((A2:A5="14/11/2008
20:00:00")*(C2:C5="RELEASE"),),0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"storm warden" wrote:
I am having a little difficulty with the Index / match functions.

I have a spreadsheet set out as follows
Date / Time CC Phase
14/11/2008 20:00:00 INTERRUPT
14/11/2008 20:00:00 B13 RELEASE
14/11/2008 21:00:00 SETUP
14/11/2008 21:10:00 B1B RELEASE

I would like to return the CC for for only when Phase=Release and Date /
Time = 14/11/2008: 20:00:00

Any Advice would be greatly appreciated.

Lookup depending upon 2 criteria
 

Max, how come and this formula does not require array-entering?

One more thing, if A2:A5 contain actual date/time then a conversion
would be needed (at least is needed in mine):

=INDEX(B2:B5,MATCH(1,INDEX((A2:A5=--"14/11/2008 20:00:00")*
(C2:C5="RELEASE"),),0))

Regards,
Kostis

On Nov 19, 6:25*pm, Max wrote:
Assuming data as posted in A2:C5
try this in say E2, normal ENTER:
=INDEX(B2:B5,MATCH(1,INDEX((A2:A5="14/11/2008
20:00:00")*(C2:C5="RELEASE"),),0))
--
Max
Singaporehttp://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---

"storm warden" wrote:
I am having a little difficulty with the Index / match functions.

I have a spreadsheet set out as follows
Date / Time * * * * * * * * *CC * * * * * *Phase
14/11/2008 20:00:00 * * * * * * * * * *INTERRUPT
14/11/2008 20:00:00 * * B13 * * * * *RELEASE
14/11/2008 21:00:00 * * * * * * * * * *SETUP
14/11/2008 21:10:00 * * B1B * * * * *RELEASE

I would like to return the CC for for only when Phase=Release and Date /
Time = 14/11/2008: 20:00:00

Any Advice would be greatly appreciated.

Lookup depending upon 2 criteria
 

.. this formula does not require array-entering?

Inspiration on usage came from reading some posts by Teethless Mama. I'm not
sure why it works w/o array-entering, but it does. And that in itself, is
quite an advantage, IMO.

.. if A2:A5 contain actual date/time then a conversion would be needed ..

Agreed. Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---
"vezerid" wrote in message
...
Max, how come and this formula does not require array-entering?

One more thing, if A2:A5 contain actual date/time then a conversion
would be needed (at least is needed in mine):

=INDEX(B2:B5,MATCH(1,INDEX((A2:A5=--"14/11/2008 20:00:00")*
(C2:C5="RELEASE"),),0))

Regards,
Kostis

Lookup depending upon 2 criteria
 

Thanks Max,

This worked a dream

"Max" wrote:

Assuming data as posted in A2:C5
try this in say E2, normal ENTER:
=INDEX(B2:B5,MATCH(1,INDEX((A2:A5="14/11/2008
20:00:00")*(C2:C5="RELEASE"),),0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"storm warden" wrote:
I am having a little difficulty with the Index / match functions.

I have a spreadsheet set out as follows
Date / Time CC Phase
14/11/2008 20:00:00 INTERRUPT
14/11/2008 20:00:00 B13 RELEASE
14/11/2008 21:00:00 SETUP
14/11/2008 21:10:00 B1B RELEASE

I would like to return the CC for for only when Phase=Release and Date /
Time = 14/11/2008: 20:00:00

Any Advice would be greatly appreciated.

Lookup depending upon 2 criteria
 

Welcome, glad it helped. Thanks for the rating.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---
"storm warden" wrote in message
...
Thanks Max,

This worked a dream