VLOOKUP & XLOOKUP Skills Test: Workplace Assessment
Dedicated assessment of Excel lookup functions. Learn exact matching, multiple column returns, wildcard queries, and backward lookups.
VLOOKUP Lookup Vector Limitation
In traditional VLOOKUP, which column of the table_array must contain the lookup_value?
Perform Exact Match VLOOKUP for Product Price
In target cell E2, look up the price for Item ID in D2 using VLOOKUP with exact match.
| # | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Item ID | Product | Price ($) | Search ID | Lookup Price |
| 2 | P-101 | Ergonomic Chair | 199 | P-102 | |
| 3 | P-102 | Standing Desk | 450 | ||
| 4 | P-103 | Monitor Lamp | 49 |
XLOOKUP Improvements Over VLOOKUP
Which of the following are distinct advantages of XLOOKUP over traditional VLOOKUP? (Select all that apply)
Modern Lookup with XLOOKUP
In target cell E2, look up the employee Department for ID in D2 using XLOOKUP.
| # | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Emp ID | Name | Department | Search ID | Result Dept |
| 2 | E-01 | Alice | Finance | E-02 | |
| 3 | E-02 | Brandon | Marketing | ||
| 4 | E-03 | Claire | Engineering |
HLOOKUP Direction of Search
In what direction does HLOOKUP search for values in a spreadsheet table?
Left-Lookup with XLOOKUP
In target cell E2, retrieve the Product Code in column A corresponding to the Product Name in D2 using XLOOKUP.
| # | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Code | Product Name | Category | Search Product | Result Code |
| 2 | SKU-99 | Wireless Mouse | Peripherals | Mechanical Keyboard | |
| 3 | SKU-42 | Mechanical Keyboard | Peripherals | ||
| 4 | SKU-18 | Webcam 4K | Video |
What Does the MATCH Function Return?
What output does the MATCH function produce when it locates a match?
Retrieve Row Position with MATCH
In target cell D2, find the 1-based index position of "Chicago" in the city list A2:A5 using MATCH with exact match.
| # | A | B | C | D |
|---|---|---|---|---|
| 1 | City | Region | Target City | Position Index |
| 2 | New York | East | Chicago | |
| 3 | Los Angeles | West | ||
| 4 | Chicago | Midwest | ||
| 5 | Houston | South |
Column Index Number Deletion Fragility
Why can hardcoded column index numbers in =VLOOKUP(A2, B:E, 4, FALSE) break a financial model?
Flexible Two-Way Retrieval with INDEX & MATCH
In target cell D2, retrieve the salary for employee in C2 using INDEX on column B and MATCH on column A.
| # | A | B | C | D |
|---|---|---|---|---|
| 1 | Name | Salary ($) | Search Name | Found Salary ($) |
| 2 | Hannah | 62,000 | Ian | |
| 3 | Ian | 78,000 | ||
| 4 | Julia | 84,000 |
Why VLOOKUP Returns #N/A Error
Which common circumstances cause an exact match VLOOKUP to return the #N/A error? (Select all that apply)
Handle Missing Keys with XLOOKUP Built-in Fallback
In target cell E2, look up Product ID 888 using XLOOKUP. If the ID is not found, return "Item Missing".
| # | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | ID | Product | Price ($) | Search ID | Status |
| 2 | 101 | Cable | 15 | 888 | |
| 3 | 102 | Adapter | 25 |
Case Sensitivity in Standard Excel Lookups
Are standard Excel lookup functions like VLOOKUP and XLOOKUP case-sensitive by default?
Approximate Match VLOOKUP for Volume Discount
In target cell E2, determine the discount rate for order quantity in D2 using approximate match VLOOKUP on table A2:B4.
| # | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Min Qty | Discount Rate | Order ID | Order Qty | Discount Applied |
| 2 | 0 | 0 | ORD-55 | 25 | |
| 3 | 10 | 0.05 | |||
| 4 | 20 | 0.1 |
XLOOKUP Search Direction Mode
Which search_mode argument instructs XLOOKUP to search from the bottom of the list upward to find the last occurrence?
Retrieve Last Transaction with XLOOKUP Reverse Search
In target cell E2, find the most recent balance for account in D2 by searching last-to-first using search_mode -1 in XLOOKUP.
| # | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Date | Account | Balance ($) | Target Account | Latest Balance ($) |
| 2 | 2024-01-01 | ACC-1 | 500 | ACC-1 | |
| 3 | 2024-01-05 | ACC-2 | 900 | ||
| 4 | 2024-01-10 | ACC-1 | 1,250 |
Wildcard Characters in Lookups
Which wildcard characters can be used in Excel lookup functions (VLOOKUP, XLOOKUP, SEARCH) when matching partial text? (Select all that apply)
Lookup with Wildcard Partial Text Match
In target cell E2, find the phone number for contact "Smith*" using VLOOKUP with wildcard exact match.
| # | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Full Name | Phone | Search Pattern | Found Phone | |
| 2 | John Smith Jr. | 555-0199 | John* | ||
| 3 | Sarah Miller | 555-0288 |
INDEX Function Array Form Syntax
What are the core arguments of the 2D array form of the INDEX function?
Extract Intersection Value with 2D INDEX
In target cell D2, extract the value at Row 2, Column 2 of data grid A2:B3 using INDEX.
| # | A | B | C | D |
|---|---|---|---|---|
| 1 | Q1 | Q2 | Target | Intersect Value |
| 2 | 100 | 150 | R2, C2 | |
| 3 | 200 | 250 |
Handling Approximate Match with False Zero
What occurs when the 4th argument [range_lookup] in VLOOKUP is completely omitted by mistake?
Return Whole Row or Multiple Columns with XLOOKUP
In target cell E2, retrieve the Unit Cost from column C for product ID in D2 using XLOOKUP.
| # | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | ID | Product | Cost ($) | Target ID | Found Cost ($) |
| 2 | SKU-01 | Widget | 12 | SKU-02 | |
| 3 | SKU-02 | Gizmo | 34 |
Replacing Nested IFs with Lookup Tables
Why is using a VLOOKUP or XLOOKUP table preferred over 10 deeply nested IF functions? (Select all that apply)
Two-Way Matrix Lookup with MATCH for Row and Column
In target cell F2, retrieve the Q2 sales for "Product B" using INDEX(B2:D3, MATCH(Product), MATCH(Quarter)).
| # | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Product | Q1 | Q2 | Q3 | Search | Q2 Result |
| 2 | Prod A | 100 | 120 | 140 | Prod B | |
| 3 | Prod B | 200 | 240 | 280 |
CHOOSEROWS / CHOOSECOLS Modern Functions
In modern Excel (Microsoft 365), what is the purpose of the CHOOSECOLS function?
Lookup with Dynamic Concatenated Key
In target cell D2, look up price using composite key A2 & "-" & B2 matching column E in reference table.
| # | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Model | Color | Key | Price ($) | Ref Key | Ref Price |
| 2 | Sedan | Red | Sedan-Red | Sedan-Red | 25,000 | |
| 3 | Sedan | Blue | Sedan-Blue | Sedan-Blue | 24,500 |
LOOKUP Vector Form Sorting Prerequisite
What strict prerequisite does the classic legacy LOOKUP(lookup_value, lookup_vector, result_vector) require?
Lookup Country Code with VLOOKUP
In target cell D2, find the Dial Code for Country in C2 from table A2:B4 using VLOOKUP.
| # | A | B | C | D |
|---|---|---|---|---|
| 1 | Country | Code | Target Country | Dial Code |
| 2 | Canada | 1 | Japan | |
| 3 | Japan | 81 | ||
| 4 | Germany | 49 |
Spill Range Error (#SPILL!) in Modern Lookups
Why does an XLOOKUP formula return a #SPILL! error when attempting to return multiple columns?
Retrieve Tax Rate with Approximate Match XLOOKUP
In target cell E2, find the marginal tax rate for Income in D2 using match_mode -1 (exact or next smaller) in XLOOKUP.
| # | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Min Income ($) | Rate | Client | Income ($) | Tax Rate |
| 2 | 0 | 0.1 | Alex | 65,000 | |
| 3 | 40,000 | 0.2 | |||
| 4 | 80,000 | 0.3 |
M. Ichsanul Fadhil
Founder, ExcelClashPre-employment screening exams designed with instant browser-based formula grading.
Related Excel Skill Tests
Excel Test for Accounting & Bookkeeping
Excel Test for Entry-Level Data Analysts
Excel Test for HR & Payroll Specialists
Excel Test for Marketing & Sales Operations
Assessment Discussion & Questions
Ask questions, discuss problem solving techniques, or share feedback on this test.
Loading comments...