Lookup table syntax

[abberyfarm]

Hello,

If I had a value of 08:00:00 in cell A1 and value of 3 in cell C1, what would be the correct syntax to look up a value in a table?

Here is the table. It is in range A1:I26 in sheet2.

Sheet2

*	A	B	C	D	E	F	G	H	I
1	*	Distance (miles)
2	Start Time	1	2	3	4	5	6	7	8
3	00:00:00	6	8	10	14	13	17	16	19
4	01:00:00	6	5	10	11	13	38	16	19
5	02:00:00	8	5	8	11	13	38	16	19
6	03:00:00	8	5	8	11	13	38	16	19
7	04:00:00	20	5	8	11	13	38	16	19
8	05:00:00	1	5	8	11	13	38	16	19
9	06:00:00	3	5	8	11	13	38	16	19
10	07:00:00	8	5	8	11	18	15	16	19
11	08:00:00	6	5	11	18	23	27	24	29
12	09:00:00	9	5	12	13	18	21	24	23
13	10:00:00	7	9	10	13	15	16	17	19
14	11:00:00	7	9	12	13	16	21	22	19
15	12:00:00	7	9	11	13	16	20	19	22
16	13:00:00	6	9	10	14	15	17	17	22
17	14:00:00	7	11	10	13	15	18	20	22
18	15:00:00	7	10	12	14	17	20	19	26
19	16:00:00	8	10	11	13	18	15	19	22
20	17:00:00	7	9	13	13	17	19	24	25
21	18:00:00	7	10	11	14	18	19	23	24
22	19:00:00	7	9	11	14	16	18	19	22
23	20:00:00	6	8	10	12	15	15	18	19
24	21:00:00	2	6	6	4	7	11	12	11
25	22:00:00	6	14	12	13	16	16	18	20
26	23:00:00	1	0	3	2	1	7	0	0

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

I want it to return the value 11 in cell D1.

Thanks for the help

 

[VoG]

Try

=INDEX(A2:I26,MATCH(A1,A2:A26,0),MATCH(C1,A2:I2,0))

 

[abberyfarm]

Hi,

It is returning the wrong number. It is returning 9.5 instead of 11.1.

I'm using =INDEX(A4:I28,MATCH(A3,A4:A28,0),MATCH(C1,A4:I4,0))

Any suggestions?

Thanks


Sheet1

*	A	B	C	D	E	F	G	H	I
1	08:00:00	*	3	9.5	*	*	*	*	*
2	*	*	*	*	*	*	*	*	*
3	*	Distance (miles)
4	Start Time	1	2	3	4	5	6	7	8
5	00:00:00	6.3	7.5	9.5	14.2	12.5	16.5	16.3	19.2
6	01:00:00	6.1	5.0	10.0	10.8	12.5	37.5	16.3	19.2
7	02:00:00	7.5	5.0	7.5	10.8	12.5	37.5	16.3	19.2
8	03:00:00	7.5	5.0	7.5	10.8	12.5	37.5	16.3	19.2
9	04:00:00	20.0	5.0	7.5	10.8	12.5	37.5	16.3	19.2
10	05:00:00	1.3	5.0	7.5	10.8	12.5	37.5	16.3	19.2
11	06:00:00	2.5	5.0	7.5	10.8	12.5	37.5	16.3	19.2
12	07:00:00	8.1	5.0	7.5	11.4	17.5	15.2	16.3	19.2
13	08:00:00	6.5	5.0	11.1	18.3	23.0	26.8	23.5	29.0
14	09:00:00	9.4	5.0	11.6	13.5	17.6	21.3	24.3	22.5
15	10:00:00	7.5	9.2	10.4	13.1	15.1	15.6	17.4	19.2
16	11:00:00	6.5	8.6	11.8	12.8	16.4	20.8	21.9	19.3
17	12:00:00	7.4	9.1	11.4	13.3	15.9	20.2	19.5	22.4
18	13:00:00	6.5	9.1	10.4	13.5	15.2	16.6	16.7	21.7
19	14:00:00	6.9	10.5	10.2	12.9	15.2	18.0	20.4	22.3
20	15:00:00	7.4	9.6	12.1	14.0	16.8	19.7	19.2	25.5
21	16:00:00	7.6	9.5	10.8	12.7	17.7	14.9	18.9	21.8
22	17:00:00	7.0	9.2	12.7	13.3	16.8	19.4	24.4	24.7
23	18:00:00	7.4	9.5	11.2	14.3	17.9	19.2	22.6	24.2
24	19:00:00	6.5	8.7	11.2	13.6	15.7	17.5	19.0	22.2
25	20:00:00	6.0	8.5	9.8	12.2	15.0	15.0	18.3	19.1
26	21:00:00	2.3	5.8	6.2	3.9	7.1	11.5	11.7	11.0
27	22:00:00	5.8	14.3	12.0	12.9	15.5	15.5	17.5	20.0
28	23:00:00	0.9	0.3	2.5	2.3	1.3	7.0	0.0	0.0

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:107px;"><col style="width:131px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[VoG]

Surely it should be =INDEX(A4:I28,MATCH(A1,A4:A28,0),MATCH(C1,A4:I4,0))

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	08:00:00	Distance (miles)	3	11
2	Start Time	1	2	3	4	5	6	7	8
3	00:00:00	6	8	10	14	13	17	16	19
4	01:00:00	6	5	10	11	13	38	16	19
5	02:00:00	8	5	8	11	13	38	16	19
6	03:00:00	8	5	8	11	13	38	16	19
7	04:00:00	20	5	8	11	13	38	16	19
8	05:00:00	1	5	8	11	13	38	16	19
9	06:00:00	3	5	8	11	13	38	16	19
10	07:00:00	8	5	8	11	18	15	16	19
11	08:00:00	6	5	11	18	23	27	24	29
12	09:00:00	9	5	12	13	18	21	24	23
13	10:00:00	7	9	10	13	15	16	17	19
14	11:00:00	7	9	12	13	16	21	22	19
15	12:00:00	7	9	11	13	16	20	19	22
16	13:00:00	6	9	10	14	15	17	17	22
17	14:00:00	7	11	10	13	15	18	20	22
18	15:00:00	7	10	12	14	17	20	19	26
19	16:00:00	8	10	11	13	18	15	19	22
20	17:00:00	7	9	13	13	17	19	24	25
21	18:00:00	7	10	11	14	18	19	23	24
22	19:00:00	7	9	11	14	16	18	19	22
23	20:00:00	6	8	10	12	15	15	18	19
24	21:00:00	2	6	6	4	7	11	12	11
25	22:00:00	6	14	12	13	16	16	18	20
26	23:00:00	1	0	3	2	1	7	0	0
Sheet5

 

[abberyfarm]

Thanks very much, I am not familiar with these functions.