PK !q@= = [Content_Types].xmlPK ! PK ! } docProps/core.xmlINDEX Practice WorkbookXplorExcel.comFree INDEX practice sheets with answer key — https://xplorexcel.com/excel-index-function-download-practice-sheet/PK !0 docProps/app.xmlXplorExcel Practice BuilderPK ! xl/workbook.xmlPK !h h xl/_rels/workbook.xml.relsPK !bI
xl/styles.xmlPK !d xl/worksheets/sheet1.xml0
1
23
45
|
6
78
910
1112
1314
|
15
1617
1819
2021
|
2223
PK !O! ! # xl/worksheets/_rels/sheet1.xml.relsPK !*X< xl/worksheets/sheet2.xml24
252627
2829640
3031285
3233345
3435205
3637695
38
39
40
41
42
PK !a xl/worksheets/sheet3.xml4344
252627454647
2829640148
3031285249
3233345350
3435205451
3637695
52
PK !-ܝN xl/worksheets/sheet4.xml5344
54255556454647
5728415859
60308261
623210363
64348
65361052
66286
67308
68691
70347
71367
PK !)gZ Z xl/worksheets/sheet5.xml13
72
7345467475
914876640
92497733
935078695
94517980
1115881640
112618280
11363832560
PK !jB xl/sharedStrings.xmlINDEX — Practice WorkbookXplorExcel.com · free Excel tutorials and practice filesLessonINDEX Function in ExcelAll Excel lessonshttps://xplorexcel.com/excel-tutorial/What’s insideIllustrationA fully worked example. Click any cell to see the formula.Practice Sheet 1Two tables. Pull the price onto every order line — one code is missing on purpose.Practice Sheet 2An order sheet. Fill the whole price column with one formula copied down.Answer KeyEvery answer plus the exact formula that produces it.How to use it1Read the lesson first, then open the Illustration sheet.2Work through each Practice Sheet. Type formulas into the shaded cells.3Only then open the Answer Key. Compare your formula, not just your number.LicenceFree to use and share for personal study and classroom teaching. Please link back rather than re-hosting the file.Rapid Reach Couriers — price list (deliberately unsorted)CodeItemPriceCODE-122Bulk CartonCODE-101ParcelCODE-108Cold PackCODE-115FragileCODE-129DocumentsWhat is happening here• MATCH finds the position of a value in a column; INDEX returns whatever sits at that position in another column. Together they do a lookup either direction.• INDEX/MATCH does not care which column comes first, so inserting a column will not break it.• This price list is not sorted by code on purpose. Exact-match lookups do not need sorted data, and assuming they do is where people go wrong.• A code that is not in the list returns #N/A. That is the lookup working correctly and telling you the value is missing.Rapid Reach Couriers — price listSolve the following with the help of the INDEX formula.#QuestionYour AnswerWhat price does CODE-122 carry? Look it up rather than reading it off.Which item is CODE-108?What price does CODE-129 carry?Look up CODE-999, which is not in the list. What does Excel show, and why?Type your formula in the shaded cells. Check yourself on the Answer Key sheet.Rapid Reach Couriers — ordersOrderQtyUnit PriceORD-5001Fill Unit Price by looking each Code up in the Practice Sheet 1 price list.→ fill in the tableORD-5002Which order line returns #N/A, and what is wrong with it?ORD-5003What is the value of order ORD-5001 — quantity times the price you looked up?ORD-5004ORD-5005ORD-5006ORD-5007ORD-5008CODE-999ORD-5009ORD-5010Try every question before you read this. Compare the formula, not only the result.SheetFormula to useAnswer=INDEX(D$4:D$8,MATCH("CODE-122",B$4:B$8,0))=INDEX(C$4:C$8,MATCH("CODE-108",B$4:B$8,0))=INDEX(D$4:D$8,MATCH("CODE-129",B$4:B$8,0))=INDEX(D$4:D$8,MATCH("CODE-999",B$4:B$8,0)) ← #N/A means not found — the formula is fine, the code is not there#N/A=INDEX('Practice Sheet 1'!D$4:D$8,MATCH(C4,'Practice Sheet 1'!B$4:B$8,0)) ← lock the table rows with $ before you copy down=INDEX('Practice Sheet 1'!D$4:D$8,MATCH(C11,'Practice Sheet 1'!B$4:B$8,0)) ← row 11 carries CODE-999, which is not in the price list=D4*INDEX('Practice Sheet 1'!D$4:D$8,MATCH(C4,'Practice Sheet 1'!B$4:B$8,0)) ← a lookup can sit inside a calculationPK !q@= = [Content_Types].xmlPK !