Excel Crash Course EXAM 2026/2027 COMPLETE QUESTIONS WITH COMPLETE SOLUTIONS

Excel Crash Course EXAM 2026/2027 COMPLETE QUESTIONS WITH COMPLETE SOLUTIONS 1. When to use HLOOKUP - ANSWER When your comparison values are all in a row across the top of the data table, and you want to look down a specified number of rows 2. When to use a VLOOKUP - ANSWER When your comparison values are located in a col to the left of a data table and you want to look across a specified number of columns 3. INDEX - ANSWER allows you to pinpoint a location of a return value in an array 4. CHOOSE - ANSWER allows you to choose which value in a set of values 5. OFFSET - ANSWER instead of selecting a whole array, you select a reference point which is usually the top left hand corner of the array or above the column if only one column. Then select rows and columns as numbers across and down from the reference point 6. MATCH - ANSWER returns the relative position (number) of an item in an array that matches a specified value 7. Where should the MATCH lookup array start when combined with OFFSET? - ANSWER Start at the OFFSET reference point and -1 after the MATCH function 8. INDIRECT - ANSWER returns whatever is in a cell, but when combined with & becomes a powerful way to dynamically sum a range with certain criteria SUM(INDIRECT( example - ANSWER ("B"&start year - number that equals row number & ":B"& end year - number that equals column number)) 9. When combining MATCH with INDIRECT, what should the format be? - ANSWER R1C1 (not A1) 10. INDIRECT MATCH syntax - ANSWER INDIRECT("B"&MATCH(row lookup and range)&"C"&MATCH(col lookup and range)) 11. Where should MATCH ranges start when combined with INDIRECT? - ANSWER First cell in the worksheet 12. ADDRESS - ANSWER Creates a cell reference as text, given a specified row and column 13. ROWS(array) and COLUMNS(array) - ANSWER tells you how many rows and columns are in that array 14. What are data tables used for? - ANSWER Sensitivity analysis on an output to changes in input variables 15. Should input variables in data tables be hardcoded or dynamically sourced from the input analysis? - ANSWER Hardcoded 16. Run data table - ANSWER Alt + D + T, then hit F9 17. Data table should be in the same worksheet as... - ANSWER the input variables 18. Repeat command - ANSWER Ctrl + Y 19. When would you use self-referencing IF functions in a data table? - ANSWER When you're pulling data from another worksheet 20. Self referencing IF syntax - ANSWER =IF(AND(input variable on x axis = input variable in source data, the output from the source data, the cell itself) 21. Why XLOOKUP not VLOOKUP? - ANSWER Because VLOOKUP breaks if you add new columns 22. How to lookup multiple criteria in data? - ANSWER 2-way XLOOKUP 23. How does 2-way XLOOKUP work? - ANSWER Instead of the one column return array, you nest another XLOOKUP across the entire data table 24. 2-way XLOOKUP syntax - ANSWER =XLOOKUP(lookup value, lookup array, XLOOKUP(all column headers, entire data table)) 25. Which lookup function should you use for scenario analysis? - ANSWER XLOOKUP 26. XMATCH is the same as MATCH but... - ANSWER requires less arguments (no exact match) 27. See Excel's calculation workings - ANSWER F9, then Esc 28. To SUMPRODUCT an array where you only want certain criteria to be output... - ANSWER use the TRUE/FALSE (1,0) test as the first array, then the SUMPRODUCT arrays. Where the data points don't meet the boolean test, they will be multiplied by 0 and therefore not summed 29. Auto-width columns to fit the size - ANSWER Alt + H + O + I 30. Define width of columns - ANSWER Alt + H + O + W 31. Rename a worksheet - ANSWER ALT + H + O + R 32. Add worksheet - ANSWER Alt + H + I + S