Formula Library Test Me Challenges Sandbox

MATCH

Lookup & Referenceintermediate

MATCH searches for a value in a range and returns its position number (1st, 2nd, 3rd, etc.), not the value itself. It's most powerful when combined with INDEX, but also useful for finding where something lives in a list or checking if a value exists.

Syntax

Excel:=MATCH(lookup_value, lookup_array, [match_type])

Google Sheets: Same syntax

Parameters

lookup_valueThe value to find.

lookup_arrayThe range to search (single row or column).

match_type?0 = exact match, 1 = largest value ≤ lookup (sorted ascending), -1 = smallest value ≥ lookup (sorted descending). Always use 0 for exact match.

? = optional parameter

Worked Example

What row position is 'Horizon IT Services' in the vendor column?

Row numbers		B
Header row	position	vendor_name
2	25	NovaTech Solutions
3	26	Clearview Data Systems
4	27 →	Horizon IT Services
5	28	Basecamp Events
6	29	Redstone Marketing

Formula

=MATCH("Horizon IT Services", B2:B6, 0)

Result

MATCH searches B2 through B6 and returns the position of 'Horizon IT Services' within that visible list. In this sample, it is the 3rd item in the range, so the result is 3.

Related:INDEX VLOOKUP XLOOKUP

Practice Exercises

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

Exercise 1 of 2beginner

#MATCH-001

Question

What position in the vendor_name column does 'Greenfield Facilities' first appear? (i.e., is it the 5th vendor listed, the 20th, etc.?)

Dataset

Vendor Spend Tracker (150 rows total)

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	INV-1016	Clearview Data Systems
18	INV-1017	Apex Office Supply
19	INV-1018	Summit Consulting Group
20	INV-1019	Cloudline Software
21	INV-1020	Atlas Hardware Co.
22	INV-1021	Meridian Travel Co.
23	INV-1022	Atlas Hardware Co.
24	INV-1023	Atlas Hardware Co.
25	INV-1024	Apex Office Supply
26	INV-1025	Redstone Marketing
27	INV-1026	Clearview Data Systems
28	INV-1027	Horizon IT Services
29	INV-1028	Basecamp Events
30	INV-1029	Redstone Marketing
31	INV-1030	BrightPath Analytics
32	INV-1031	Basecamp Events
33	INV-1032	Cloudline Software
34	INV-1033	Clearview Data Systems
35	INV-1034	Meridian Travel Co.
36	INV-1035	Cloudline Software
37	INV-1036	Horizon IT Services
38	INV-1037	BrightPath Analytics
39	INV-1038	Cloudline Software
40	INV-1039	Horizon IT Services
41	INV-1040	Apex Office Supply
42	INV-1041	Summit Consulting Group
43	INV-1042	Clearview Data Systems
44	INV-1043	Forge & Frame Creative
45	INV-1044	NovaTech Solutions
46	INV-1045	Meridian Travel Co.
47	INV-1046	Cloudline Software
48	INV-1047	Apex Office Supply
49	INV-1048	Apex Office Supply
50	INV-1049	Pinecrest Staffing
51	INV-1050	Clearview Data Systems
52	INV-1051	BrightPath Analytics
53	INV-1052	Keystone Legal Advisors
54	INV-1053	Cloudline Software
55	INV-1054	NovaTech Solutions
56	INV-1055	Basecamp Events
57	INV-1056	Cloudline Software
58	INV-1057	NovaTech Solutions
59	INV-1058	Clearview Data Systems
60	INV-1059	Basecamp Events
61	INV-1060	Horizon IT Services
62	INV-1061	Atlas Hardware Co.
63	INV-1062	Forge & Frame Creative
64	INV-1063	Meridian Travel Co.
65	INV-1064	Meridian Travel Co.
66	INV-1065	Cloudline Software
67	INV-1066	Redstone Marketing
68	INV-1067	Horizon IT Services
69	INV-1068	Cloudline Software
70	INV-1069	NovaTech Solutions
71	INV-1070	Apex Office Supply
72	INV-1071	Redstone Marketing
73	INV-1072	NovaTech Solutions
74	INV-1073	Summit Consulting Group
75	INV-1074	Atlas Hardware Co.
76	INV-1075	BrightPath Analytics
77	INV-1076	Clearview Data Systems
78	INV-1077	Meridian Travel Co.
79	INV-1078	Pinecrest Staffing
80	INV-1079	Meridian Travel Co.
81	INV-1080	Greenfield Facilities
82	INV-1081	Keystone Legal Advisors
83	INV-1082	Apex Office Supply
84	INV-1083	Cloudline Software
85	INV-1084	Meridian Travel Co.
86	INV-1085	Horizon IT Services
87	INV-1086	Cloudline Software
88	INV-1087	Apex Office Supply
89	INV-1088	Pinecrest Staffing
90	INV-1089	NovaTech Solutions
91	INV-1090	Meridian Travel Co.
92	INV-1091	Redstone Marketing
93	INV-1092	Cloudline Software
94	INV-1093	Cloudline Software
95	INV-1094	BrightPath Analytics
96	INV-1095	Redstone Marketing
97	INV-1096	BrightPath Analytics
98	INV-1097	Redstone Marketing
99	INV-1098	Cloudline Software
100	INV-1099	Meridian Travel Co.
101	INV-1100	Meridian Travel Co.
102	INV-1101	Forge & Frame Creative
103	INV-1102	Atlas Hardware Co.
104	INV-1103	NovaTech Solutions
105	INV-1104	Meridian Travel Co.
106	INV-1105	NovaTech Solutions
107	INV-1106	Pinecrest Staffing
108	INV-1107	Basecamp Events
109	INV-1108	Redstone Marketing
110	INV-1109	NovaTech Solutions
111	INV-1110	Horizon IT Services
112	INV-1111	Pinecrest Staffing
113	INV-1112	NovaTech Solutions
114	INV-1113	Cloudline Software
115	INV-1114	Meridian Travel Co.
116	INV-1115	Clearview Data Systems
117	INV-1116	Cloudline Software
118	INV-1117	Clearview Data Systems
119	INV-1118	Greenfield Facilities
120	INV-1119	Cloudline Software
121	INV-1120	Horizon IT Services
122	INV-1121	Meridian Travel Co.
123	INV-1122	Redstone Marketing
124	INV-1123	Redstone Marketing
125	INV-1124	NovaTech Solutions
126	INV-1125	Cloudline Software
127	INV-1126	Meridian Travel Co.
128	INV-1127	Horizon IT Services
129	INV-1128	Forge & Frame Creative
130	INV-1129	Cloudline Software
131	INV-1130	BrightPath Analytics
132	INV-1131	Keystone Legal Advisors
133	INV-1132	BrightPath Analytics
134	INV-1133	Meridian Travel Co.
135	INV-1134	NovaTech Solutions
136	INV-1135	Cloudline Software
137	INV-1136	Greenfield Facilities
138	INV-1137	Meridian Travel Co.
139	INV-1138	Apex Office Supply
140	INV-1139	Pinecrest Staffing
141	INV-1140	Pinecrest Staffing
142	INV-1141	Cloudline Software
143	INV-1142	NovaTech Solutions
144	INV-1143	Greenfield Facilities
145	INV-1144	Clearview Data Systems
146	INV-1145	Apex Office Supply
147	INV-1146	Cloudline Software
148	INV-1147	Summit Consulting Group
149	INV-1148	NovaTech Solutions
150	INV-1149	Greenfield Facilities
151	INV-1150	Greenfield Facilities
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

Exercise 2 of 2intermediate

#MATCH-002

Question

Using MATCH, find the position of the maximum invoice amount in the amount column. Then verify by checking: does INDEX at that position return the same value as MAX?