# Lookup table syntax

#### abberyfarm

##### Well-known Member
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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Try

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

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

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

Excel Workbook
ABCDEFGHI
108:00:00Distance (miles)311
2Start Time12345678
300:00:0068101413171619
401:00:0065101113381619
502:00:008581113381619
603:00:008581113381619
704:00:0020581113381619
805:00:001581113381619
906:00:003581113381619
1007:00:008581118151619
1108:00:0065111823272429
1209:00:0095121318212423
1310:00:0079101315161719
1411:00:0079121316212219
1512:00:0079111316201922
1613:00:0069101415171722
1714:00:00711101315182022
1815:00:00710121417201926
1916:00:00810111318151922
2017:00:0079131317192425
2118:00:00710111418192324
2219:00:0079111416181922
2320:00:0068101215151819
2421:00:0026647111211
2522:00:00614121316161820
2623:00:0010321700
Sheet5

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

Replies
6
Views
193
Replies
10
Views
246
Replies
10
Views
258
Replies
2
Views
109
Replies
11
Views
465

1,203,052
Messages
6,053,233
Members
444,648
Latest member
sinkuan85

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back