PK !q@= = [Content_Types].xmlPK ! PK !B;5 docProps/core.xmlHLOOKUP Practice WorkbookXplorExcel.comFree HLOOKUP practice sheets with answer key — https://xplorexcel.com/hlookup-in-excel/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 !锧 # xl/worksheets/_rels/sheet1.xml.relsPK !z xl/worksheets/sheet2.xml24
252627
2829490
3031870
3233720
3435530
3637345
38
39
40
41
42
PK ! xl/worksheets/sheet3.xml4344
252627454647
2829490148
3031870249
3233720350
3435530451
3637345
52
PK !\Va xl/worksheets/sheet4.xml5344
54255556454647
57281215859
60309261
62325363
64342
6536852
66282
673011
68698
70346
713610
PK ! Z Z xl/worksheets/sheet5.xml13
72
7345467475
914876490
92497733
935078345
94517980
1115881490
112618280
11363835880
PK !se e xl/sharedStrings.xmlHLOOKUP — Practice WorkbookXplorExcel.com · free Excel tutorials and practice filesLessonHLOOKUP 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.Greenfield Academy — price list (deliberately unsorted)CodeItemPriceCODE-101TransportCODE-129TuitionCODE-115SportsCODE-108LibraryCODE-122LabWhat is happening here• VLOOKUP searches the FIRST column of the table and returns a column counted from there. The 3rd argument is a column number, not a letter.• FALSE as the 4th argument demands an exact match. Leave it out and an unsorted table like this one returns confident nonsense.• 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.Greenfield Academy — price listSolve the following with the help of the HLOOKUP formula.#QuestionYour AnswerWhat price does CODE-101 carry? Look it up rather than reading it off.Which item is CODE-115?What price does CODE-122 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.Greenfield Academy — 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=VLOOKUP("CODE-101",B$4:D$8,3,FALSE)=VLOOKUP("CODE-115",B$4:D$8,2,FALSE)=VLOOKUP("CODE-122",B$4:D$8,3,FALSE)=VLOOKUP("CODE-999",B$4:D$8,3,FALSE) ← #N/A means not found — the formula is fine, the code is not there#N/A=VLOOKUP(C4,'Practice Sheet 1'!B$4:D$8,3,FALSE) ← lock the table rows with $ before you copy down=VLOOKUP(C11,'Practice Sheet 1'!B$4:D$8,3,FALSE) ← row 11 carries CODE-999, which is not in the price list=D4*VLOOKUP(C4,'Practice Sheet 1'!B$4:D$8,3,FALSE) ← a lookup can sit inside a calculationPK !q@= = [Content_Types].xmlPK !