Find the position of a value in a list
=MATCH(E1,A2:A11,0)Excel · result on the sample sheet: 4
Describe what you need and get a working MATCH formula for Excel and Google Sheets, tested on real data before you copy it. MATCH returns the position of a value in a row or column: 3 means the third cell.
Each formula below was calculated on its own sample sheet. Open one to see the sheet, why it works, and to try it yourself.
=MATCH(E1,A2:A11,0)Excel · result on the sample sheet: 4
=XMATCH(K1,A1:H1)Excel · result on the sample sheet: 4
=MATCH(F1,A2:A9,0)+ROW(A2)-1Excel · result on the sample sheet: 6
=INDEX(C2:C8,MATCH(F1,A2:A8,0))Excel · result on the sample sheet: 2231
=XLOOKUP(G1,A2:A9,C2:C9)Excel · result on the sample sheet: $129.00
=VLOOKUP(F1,A2:C9,3,FALSE)Excel · result on the sample sheet: $449.00
=MONTH(DATEVALUE(A2&" 1"))Excel · result on the sample sheet: 5 rows (B2:B6)
=CHOOSECOLS(A2:F7,XMATCH(H1:I1,A1:F1))Excel · result on the sample sheet: 6 rows (H2:I7)
You describe the task in plain English, and it writes the formula, using MATCH where that's the right tool, for your table or a sample one. The formula is run in our engine in Excel and Google Sheets mode before you see it, so you get its result, not just a guess.
MATCH returns the position of a value in a row or column: 3 means the third cell.
Yes. Each example below opens a page where you try the formula in a live grid and it's checked as you go, and the MATCH function page has a practice exercise.