Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a chart depending on different criteria | Charts and Charting in Excel | |||
How to use INDEX in an ARRAY that changes depending on a criteria | Excel Worksheet Functions | |||
Populate, Depending on Criteria | Excel Discussion (Misc queries) | |||
Auto copy down depending on criteria | Excel Worksheet Functions | |||
Delete row depending on criteria | Excel Discussion (Misc queries) |