How to use index and match function for determining the value?

[Eric]

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

 

[Jacob Skaria]

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