Formula Library Test Me Challenges Sandbox

INDEX

Lookup & Referenceintermediate

INDEX retrieves a value from a range based on a row number (and optionally a column number). On its own it's straightforward, but combined with MATCH it becomes one of the most powerful lookup methods in Excel — more flexible than VLOOKUP and available in every Excel version.

Syntax

Excel:=INDEX(array, row_num, [column_num])

Google Sheets: Same syntax

Parameters

arrayThe range to return a value from.

row_numThe row position within the array.

column_num?The column position within the array. Required if array has multiple columns.

? = optional parameter

Worked Example

Return the value in the 5th row of the amount column.

Row numbers		A	E
Header row	position	invoice_id	amount
2	1	INV-1001	$12,500.00
3	2	INV-1002	$487.50
4	3	INV-1003	$18,750.00
5	4	INV-1004	$3,200.00
6	5 →	INV-1005	$7,600.00

Formula

=INDEX(C2:C6, 5)

Result

$7,600.00

INDEX goes to the 5th position in C2 through C6 and returns that value. In this visible sample, the 5th item in the amount range is C6, which contains $7,600.00.

Related:MATCH VLOOKUP XLOOKUP

Practice Exercises

2 exercises - work through each one using the data table below

Exercise 1 of 2beginner

#INDEX-001

Question

What vendor name is in the 10th row of the dataset?

Dataset

Showing rows 1-15

Download CSV

Row numbers	A	B
Header row
2	INV-1001	Cloudline Software
3	INV-1002	Apex Office Supply
4	INV-1003	Summit Consulting Group
5	INV-1004	Meridian Travel Co.
6	INV-1005	Forge & Frame Creative
7	INV-1006	NovaTech Solutions
8	INV-1007	Atlas Hardware Co.
9	INV-1008	Clearview Data Systems
10	INV-1009	Redstone Marketing
11	INV-1010	Keystone Legal Advisors
12	INV-1011	Cloudline Software
13	INV-1012	Basecamp Events
14	INV-1013	Apex Office Supply
15	INV-1014	BrightPath Analytics
16	INV-1015	Meridian Travel Co.
17

Click a column name to sort. Use the filter icon to narrow rows by column value.

Scroll to explore the table, then sort or filter by column as needed.

Hints

Your Answer

Exercise 2 of 2intermediate

#INDEX-002

Question

Combine INDEX and MATCH: find the amount for the invoice from 'Basecamp Events' that appears first in the dataset. What's the amount?

Dataset

Vendor Spend Tracker (150 rows total)

Download CSV

Row numbers	A	B	C
Header row
2	INV-1001	Cloudline Software	$12,500.00
3	INV-1002	Apex Office Supply	$487.50
4	INV-1003	Summit Consulting Group	$18,750.00
5	INV-1004	Meridian Travel Co.	$3,200.00
6	INV-1005	Forge & Frame Creative	$7,600.00
7	INV-1006	NovaTech Solutions	$9,800.00
8	INV-1007	Atlas Hardware Co.	$2,150.00
9	INV-1008	Clearview Data Systems	$15,300.00
10	INV-1009	Redstone Marketing	$4,250.00
11	INV-1010	Keystone Legal Advisors	$8,900.00
12	INV-1011	Cloudline Software	$8,500.00
13	INV-1012	Basecamp Events	$8,256.80
14	INV-1013	Apex Office Supply	$713.88
15	INV-1014	BrightPath Analytics	$2,645.33
16	INV-1015	Meridian Travel Co.	$4,702.01
17	INV-1016	Clearview Data Systems	$3,400.74
18	INV-1017	Apex Office Supply	$531.83
19	INV-1018	Summit Consulting Group	$1,847.86
20	INV-1019	Cloudline Software	$7,800.00
21	INV-1020	Atlas Hardware Co.	$2,524.82
22	INV-1021	Meridian Travel Co.	$2,934.55
23	INV-1022	Atlas Hardware Co.	$2,912.75
24	INV-1023	Atlas Hardware Co.	$4,124.04
25	INV-1024	Apex Office Supply	$672.77
26	INV-1025	Redstone Marketing	$5,371.63
27	INV-1026	Clearview Data Systems	$3,796.96
28	INV-1027	Horizon IT Services	$1,773.30
29	INV-1028	Basecamp Events	$4,601.68
30	INV-1029	Redstone Marketing	$5,414.85
31	INV-1030	BrightPath Analytics	$2,704.87
32	INV-1031	Basecamp Events	$2,351.63
33	INV-1032	Cloudline Software	$9,200.00
34	INV-1033	Clearview Data Systems	$4,086.27
35	INV-1034	Meridian Travel Co.	$1,642.50
36	INV-1035	Cloudline Software	$2,867.94
37	INV-1036	Horizon IT Services	$4,586.87
38	INV-1037	BrightPath Analytics	$2,679.33
39	INV-1038	Cloudline Software	$2,284.90
40	INV-1039	Horizon IT Services	$3,224.17
41	INV-1040	Apex Office Supply	$604.96
42	INV-1041	Summit Consulting Group	$9,500.00
43	INV-1042	Clearview Data Systems	$3,871.90
44	INV-1043	Forge & Frame Creative	$5,726.55
45	INV-1044	NovaTech Solutions	$3,596.33
46	INV-1045	Meridian Travel Co.	$5,657.73
47	INV-1046	Cloudline Software	$8,000.00
48	INV-1047	Apex Office Supply	$396.69
49	INV-1048	Apex Office Supply	$523.99
50	INV-1049	Pinecrest Staffing	$5,843.41
51	INV-1050	Clearview Data Systems	$5,091.38
52	INV-1051	BrightPath Analytics	$6,200.00
53	INV-1052	Keystone Legal Advisors	$10,496.53
54	INV-1053	Cloudline Software	$3,873.77
55	INV-1054	NovaTech Solutions	$2,131.07
56	INV-1055	Basecamp Events	$4,659.43
57	INV-1056	Cloudline Software	$7,500.00
58	INV-1057	NovaTech Solutions	$3,255.52
59	INV-1058	Clearview Data Systems	$2,889.22
60	INV-1059	Basecamp Events	$2,943.88
61	INV-1060	Horizon IT Services	$3,917.25
62	INV-1061	Atlas Hardware Co.	$2,958.71
63	INV-1062	Forge & Frame Creative	$5,190.48
64	INV-1063	Meridian Travel Co.	$3,800.00
65	INV-1064	Meridian Travel Co.	$3,248.96
66	INV-1065	Cloudline Software	$6,800.00
67	INV-1066	Redstone Marketing	$4,583.65
68	INV-1067	Horizon IT Services	$4,154.73
69	INV-1068	Cloudline Software	$1,994.85
70	INV-1069	NovaTech Solutions	$5,988.79
71	INV-1070	Apex Office Supply	$701.90
72	INV-1071	Redstone Marketing	$1,522.67
73	INV-1072	NovaTech Solutions	$8,100.00
74	INV-1073	Summit Consulting Group	$33,000.00
75	INV-1074	Atlas Hardware Co.	$3,618.53
76	INV-1075	BrightPath Analytics	$5,484.82
77	INV-1076	Clearview Data Systems	$2,190.73
78	INV-1077	Meridian Travel Co.	$4,915.98
79	INV-1078	Pinecrest Staffing	$5,337.06
80	INV-1079	Meridian Travel Co.	$5,217.40
81	INV-1080	Greenfield Facilities	$4,199.01
82	INV-1081	Keystone Legal Advisors	$7,880.94
83	INV-1082	Apex Office Supply	$738.04
84	INV-1083	Cloudline Software	$9,500.00
85	INV-1084	Meridian Travel Co.	$3,706.27
86	INV-1085	Horizon IT Services	$3,934.09
87	INV-1086	Cloudline Software	$2,463.38
88	INV-1087	Apex Office Supply	$404.67
89	INV-1088	Pinecrest Staffing	$1,909.04
90	INV-1089	NovaTech Solutions	$2,885.01
91	INV-1090	Meridian Travel Co.	$5,922.08
92	INV-1091	Redstone Marketing	$3,693.48
93	INV-1092	Cloudline Software	$4,986.06
94	INV-1093	Cloudline Software	$31,500.00
95	INV-1094	BrightPath Analytics	$4,248.05
96	INV-1095	Redstone Marketing	$3,264.04
97	INV-1096	BrightPath Analytics	$5,977.55
98	INV-1097	Redstone Marketing	$3,214.26
99	INV-1098	Cloudline Software	$8,200.00
100	INV-1099	Meridian Travel Co.	$2,709.54
101	INV-1100	Meridian Travel Co.	$3,516.17
102	INV-1101	Forge & Frame Creative	$3,122.47
103	INV-1102	Atlas Hardware Co.	$4,530.21
104	INV-1103	NovaTech Solutions	$4,352.17
105	INV-1104	Meridian Travel Co.	$3,137.30
106	INV-1105	NovaTech Solutions	$4,559.89
107	INV-1106	Pinecrest Staffing	$7,400.00
108	INV-1107	Basecamp Events	$2,724.75
109	INV-1108	Redstone Marketing	$3,957.64
110	INV-1109	NovaTech Solutions	$2,398.15
111	INV-1110	Horizon IT Services	$5,015.14
112	INV-1111	Pinecrest Staffing	$30,500.00
113	INV-1112	NovaTech Solutions	$3,646.25
114	INV-1113	Cloudline Software	$5,200.86
115	INV-1114	Meridian Travel Co.	$1,878.57
116	INV-1115	Clearview Data Systems	$4,287.78
117	INV-1116	Cloudline Software	$5,505.30
118	INV-1117	Clearview Data Systems	$4,658.66
119	INV-1118	Greenfield Facilities	$5,042.80
120	INV-1119	Cloudline Software	$7,000.00
121	INV-1120	Horizon IT Services	$3,893.42
122	INV-1121	Meridian Travel Co.	$5,864.21
123	INV-1122	Redstone Marketing	$3,920.27
124	INV-1123	Redstone Marketing	$4,600.00
125	INV-1124	NovaTech Solutions	$1,602.74
126	INV-1125	Cloudline Software	$5,250.09
127	INV-1126	Meridian Travel Co.	$4,417.14
128	INV-1127	Horizon IT Services	$5,900.00
129	INV-1128	Forge & Frame Creative	$4,618.54
130	INV-1129	Cloudline Software	$8,800.00
131	INV-1130	BrightPath Analytics	$4,003.25
132	INV-1131	Keystone Legal Advisors	$11,892.88
133	INV-1132	BrightPath Analytics	$2,800.34
134	INV-1133	Meridian Travel Co.	$4,365.68
135	INV-1134	NovaTech Solutions	$2,264.09
136	INV-1135	Cloudline Software	$5,344.04
137	INV-1136	Greenfield Facilities	$1,733.29
138	INV-1137	Meridian Travel Co.	$2,251.07
139	INV-1138	Apex Office Supply	$367.30
140	INV-1139	Pinecrest Staffing	$3,235.55
141	INV-1140	Pinecrest Staffing	$4,538.25
142	INV-1141	Cloudline Software	$7,200.00
143	INV-1142	NovaTech Solutions	$2,487.53
144	INV-1143	Greenfield Facilities	$4,398.73
145	INV-1144	Clearview Data Systems	$4,282.05
146	INV-1145	Apex Office Supply	$300.36
147	INV-1146	Cloudline Software	$9,000.00
148	INV-1147	Summit Consulting Group	$4,697.90
149	INV-1148	NovaTech Solutions	$2,613.37
150	INV-1149	Greenfield Facilities	$4,097.38
151	INV-1150	Greenfield Facilities	$1,846.11
152

Click a column name to sort. Use the filter icon to narrow rows by column value.

Scroll to explore the table, then sort or filter by column as needed.

Hints

Your Answer

Want more practice?

Test Me mode randomizes exercises across all formulas without telling you which formula to use.

Try Test Me ->