Why does INDEX MATCH return the wrong value or #N/A
=INDEX(C2:C8,MATCH(F1,A2:A8,0))Excel · result on the sample sheet: 2231
Describe what you need and get a working INDEX formula for Excel and Google Sheets, tested on real data before you copy it. INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column.
Each formula below was calculated on its own sample sheet. Open one to see the sheet, why it works, and to try it yourself.
=INDEX(C2:C8,MATCH(F1,A2:A8,0))Excel · result on the sample sheet: 2231
=INDEX(A2:A11,MATCH(D1,A2:A11,0)+1)Excel · result on the sample sheet: 1250
=CHOOSE(B2,"Very poor","Poor","Neutral","Good","Excellent")Excel · result on the sample sheet: 6 rows (C2:C7)
=INDEX(B2:F6,I1,K1)Excel · result on the sample sheet: Eli
=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
=XLOOKUP(TRUE,B2:B13<>"",B2:B13,,0,-1)/B2-1Excel · result on the sample sheet: 23.0%
=LET(n,COUNTIF(C2:C7,"<0"),n-1-INDEX(C2:C7,n)/INDEX(B2:B7,n+1))Excel · result on the sample sheet: 3.31
You describe the task in plain English, and it writes the formula, using INDEX 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.
INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column.
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 INDEX function page has a practice exercise.