Instructions and covered skills
Read each prompt carefully before selecting a response. Focus on the stated data layout, match requirement, and expected result. Turn off notifications and avoid switching between unrelated tasks while testing. Do not assume source data is sorted unless the prompt says so. Review formula syntax, reference types, and error behavior before submitting an answer. Choose the response that best fits the described spreadsheet situation.
Key Areas
This test covers lookup formula design in Excel and Google Sheets, with emphasis on retrieving related information from structured data. Candidates work with XLOOKUP, VLOOKUP, INDEX, MATCH, XMATCH, IFNA, and IFERROR in realistic reporting and operational scenarios. The material examines exact matching, approximate matching, duplicate keys, lookup direction, search order, and return ranges. It also addresses the distinction between a matched value and the position returned by MATCH or XMATCH.
Reliable references are an important focus. Candidates should understand relative, absolute, and mixed cell references so formulas remain correct when copied across rows or columns. They should also recognize how table growth, inconsistent data types, hidden spaces, and unsorted threshold tables can affect lookup outcomes. Questions include choosing suitable error messages and preventing expected missing values from obscuring genuine spreadsheet problems.
Formula selection matters as much as syntax. XLOOKUP can retrieve values from either side of a key column and offers built-in controls for missing matches and search direction. INDEX with MATCH or XMATCH supports flexible lookup patterns and can be useful when return and key ranges are separate. VLOOKUP remains common in existing workbooks, so candidates should know its column-index requirement and its approximate-match behavior.
Recommended Preparation
Practice with small tables containing product codes, employee identifiers, dates, pricing thresholds, and repeated transaction records. Build formulas that retrieve a single field, return multiple adjacent fields, and identify the latest matching record. Test formulas after filling them down and across to confirm that lookup arrays and return arrays remain aligned. Review how text-form numbers, trailing spaces, and inconsistent date values can block an otherwise valid match.
Use exact matches for identifiers such as invoice numbers and account codes. Use approximate matching only for ordered bands such as commission tiers, tax thresholds, or score ranges. Compare formula results against source records, and make expected missing values clear with an appropriate fallback message. This preparation supports dependable spreadsheet models that remain accurate as data changes.