Does anyone have any suggestions on how to use index and match function for
determining the value?

Under the column A, there is a list of number,
Under the column B, there is a list of date,
Under the column C, there is a list of time,

1 3-Aug-09 10:15 AM [row 14]
2 3-Aug-09 10:30 AM
3 3-Aug-09 10:45 AM
4 3-Aug-09 11:00 AM
5 3-Aug-09 11:15 AM
6 3-Aug-09 11:30 AM
7 3-Aug-09 11:45 AM
8 3-Aug-09 12:00 PM
9 3-Aug-09 12:15 PM [row 22]

There is a given date in cell C1 3-Aug-09, and
there is a given time in cell C2 11:15 PM
I would like to determine the value under column A, which match both of the
date and time, and it should return 5 in cell C10.

Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric

Hi Eric

Suppose your data is as below with your query date and time ni D1 and E1
respectively.

ColA ColB ColC ColD ColE
1 8/7/2009 9:15 8/7/2009 13:15
2 8/7/2009 10:15
3 8/7/2009 11:15
4 8/7/2009 12:15
5 8/7/2009 13:15
6 8/7/2009 16:15
7 8/7/2009 17:15
8 8/7/2009 18:15

---The below formula will pick the value in ColA...
=SUMPRODUCT(--(B1:B10=D1),--(C1:C10=E1),A1:A10)

---If you are looking for a INDEX/MATCH formula with multiple criteria; use
the below instead. Please note that this is an array formula. Within the cell
in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"
=INDEX($A$1:$A$10,MATCH(1,($B$1:$B$10=D1)*($C$1:$C $10=E1),0))


If this post helps click Yes
---------------
Jacob Skaria


"Eric" wrote:

Does anyone have any suggestions on how to use index and match function for
determining the value?

Under the column A, there is a list of number,
Under the column B, there is a list of date,
Under the column C, there is a list of time,

1 3-Aug-09 10:15 AM [row 14]
2 3-Aug-09 10:30 AM
3 3-Aug-09 10:45 AM
4 3-Aug-09 11:00 AM
5 3-Aug-09 11:15 AM
6 3-Aug-09 11:30 AM
7 3-Aug-09 11:45 AM
8 3-Aug-09 12:00 PM
9 3-Aug-09 12:15 PM [row 22]

There is a given date in cell C1 3-Aug-09, and
there is a given time in cell C2 11:15 PM
I would like to determine the value under column A, which match both of the
date and time, and it should return 5 in cell C10.

Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric