[All skill tests](https://jobicy.com/tests.md) Skill assessment # Excel and Google Sheets Lookup Formula Design Skills Test This test evaluates the ability to retrieve, match, and validate data with lookup formulas in Excel and Google Sheets. It focuses on selecting formulas, controlling match behavior, and building reliable references. [Start the test](#test-start) Share Assessment setup Ready ## Choose the level that matches your current experience. * 01 Basic Available * 02 Intermediate Available * 03 Advanced Available Instant result Review your score after submission 20–30 Questions per assessment 15–45 min Estimated completion time 3 levels Choose your difficulty Excel & Google Sheets [View category](https://jobicy.com/test-category/excel-google-sheets.md) Start assessment ## Choose your level and begin. Answer without outside help so the result reflects your current knowledge. You will see your score after completing the selected assessment. Lookup formulas connect related tables, reduce manual data entry, and make reports responsive to changing source data. Strong formula design requires accurate match settings, stable ranges, meaningful error handling, and awareness of how each lookup function returns results. Basic Intermediate Advanced This is a demo version of the test. You may attempt up to 3 questions. To take the full test and save your results, please [log in](https://jobicy.com/dashboard-page.md) or [create an account](https://jobicy.com/dashboard-page#tab2.md). This is a demo version of the test. You may attempt up to 3 questions. To take the full test and save your results, please [log in](https://jobicy.com/dashboard-page.md) or [create an account](https://jobicy.com/dashboard-page#tab2.md). This is a demo version of the test. You may attempt up to 3 questions. To take the full test and save your results, please [log in](https://jobicy.com/dashboard-page.md) or [create an account](https://jobicy.com/dashboard-page#tab2.md). Test details ## Know what to expect. Review the instructions, covered skills, example question themes, and intended audience before beginning. 01 ### 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. 02 ### Examples of questions 1. Which XLOOKUP argument controls the value returned after a match is found? 2. When should VLOOKUP use FALSE as its final argument? 3. What result does MATCH return when it finds an item in a range? 4. Which reference keeps a lookup table fixed when a formula is filled down? 5. How can IFNA improve a customer lookup result? 6. Which function combination can return a value to the left of a key column? 7. What condition is required for an approximate VLOOKUP match? 8. Which XLOOKUP setting searches from the last item toward the first item? 9. How can a lookup formula return an entire matching row? 10. Why might a lookup fail when values appear identical on screen? 03 ### Who this test is best for Analysts, operations coordinators, finance staff, sales teams, and administrative professionals who build or maintain spreadsheet reports using related data tables. Related assessments ## Continue with a similar skill test. Explore assessments from the same professional area to compare adjacent skills and build a broader picture of your knowledge. [Excel & Google Sheets 3 levelsPivot Tables and Data Summarization Skills TestThis test assesses your proficiency with pivot tables in Excel and Google Sheets, focusing on data summarization, grouping, and analysis techniques. Ideal for… Open assessment →](https://jobicy.com/tests/pivot-tables-and-data-summarization-skills-test.md) [Excel & Google Sheets 3 levelsExcel and Google Sheets Skills AssessmentTest your proficiency in Excel and Google Sheets with this challenging skills assessment. Perfect for those looking to validate their data manipulation and… Open assessment →](https://jobicy.com/tests/excel-and-google-sheets-skills-assessment.md) [SQL & Databases 3 levelsPostgreSQL Index Design and Query Plan Analysis Skills TestEvaluate how well you design PostgreSQL indexes and interpret query execution plans. The test focuses on matching index structures to query predicates, joins,… Open assessment →](https://jobicy.com/tests/postgresql-index-design-and-query-plan-analysis-skills-test.md) [Data Interpretation & Visualization 3 levelsTableau Dashboard Design and Visual Encoding Skills TestAssess the ability to design Tableau dashboards that communicate data clearly and support sound decisions. The test focuses on chart selection, visual encodings,… Open assessment →](https://jobicy.com/tests/tableau-dashboard-design-and-visual-encoding-skills-test.md) [Business Metrics & KPI Analysis 3 levelsSaaS Cohort Retention and Churn Analysis Skills TestThis test evaluates the ability to build, interpret, and act on SaaS customer retention cohorts. It focuses on consistent cohort definitions, retention calculations,… Open assessment →](https://jobicy.com/tests/saas-cohort-retention-and-churn-analysis-skills-test.md) [Business Metrics & KPI Analysis 3 levelsRole-Specific KPI Identification Skills TestThis test evaluates your ability to select the most relevant Key Performance Indicators for various digital roles. Master this skill to ensure your… Open assessment →](https://jobicy.com/tests/role-specific-kpi-identification-skills-test.md) [SQL & Databases 3 levelsSQL JOINs and Multi-Table Queries Skills TestMaster the art of combining data from multiple tables using SQL JOINs. This test evaluates your ability to write efficient queries using various… Open assessment →](https://jobicy.com/tests/sql-joins-and-multi-table-queries-skills-test.md) [Data Interpretation & Visualization 3 levelsData Storytelling Techniques Skills AssessmentThis assessment evaluates your ability to craft compelling narratives using data. It covers key techniques for turning raw numbers into actionable insights. Open assessment →](https://jobicy.com/tests/data-storytelling-techniques-skills-assessment.md) [SQL & Databases 3 levelsSQL Window Functions Skills TestTest your knowledge and skills with SQL window functions, a powerful tool for advanced data analysis. This assessment focuses on your ability to… Open assessment →](https://jobicy.com/tests/sql-window-functions-skills-test.md) ## Share the assessment or try another skill. Send this test to a colleague or friend, or return to the assessment library to explore another professional area. Share this test [Browse all tests](https://jobicy.com/tests.md)