🚧 Under construction — VoTechWorks is being built. A free tech program — complimentary, as our commitment to get America working.
voTechWorksGrade 13 · a vocational technology learning center, by urbanicity
newSchoolWorks.com › VoTechWorks › Business & Cross-Cutting › Bookkeeping / Office Administration › Excel for Bookkeeping and Office Administration › Lesson 5
BUS-BOOK55-05 · Lesson 5 · 14 clock hours · lab

Logical and Lookup Functions: IF, IFERROR, VLOOKUP, and XLOOKUP

Objective. Students use IF, IFERROR, VLOOKUP, and XLOOKUP to automate data retrieval and status flags in a combined accounts-receivable aging and customer-account lookup workbook.

⚠ Pending professional review

This lesson, its worked examples and its practice questions has not yet been verified by a credentialed professional in the field or by a certification or approval body. It is a study draft and may contain errors. Do not use it as instructions for work on a job site or on live equipment. Always follow your instructor, your supervising professional, and current official references and regulations.

VoTechWorks program approval and accreditation are still in progress. Credentials are issued only by the certifying bodies named in each program, after their own exams.

Why Bookkeepers Use Logical and Lookup Functions

Manual cross-referencing between spreadsheets wastes time and introduces errors. Excel's logical and lookup functions let you automate two of the most common bookkeeping tasks: pulling data from a reference table and applying a business rule to flag a result. In this lab you will build a working accounts-receivable (AR) aging sheet that retrieves each customer's credit limit from a separate master table and flags whether the customer's balance is within that limit.

Named Ranges: Your First Step

Before writing any lookup formula, name the customer master table so your formulas are readable and maintainable. Select the entire customer master table (including headers), click the Name Box at the left of the formula bar, type `CustomerMaster`, and press Enter. From this point forward, your formulas can reference `CustomerMaster` instead of an absolute cell address like `$A$2:$C$50`. Named ranges also protect you from mistakes when rows are inserted or the table is moved.

VLOOKUP

VLOOKUP stands for Vertical Lookup. It searches the first column of a range for a value and returns something from a column to the right.

Syntax: `=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`

Example: `=VLOOKUP(A2, CustomerMaster, 3, FALSE)` finds the value in A2 in the first column of `CustomerMaster` and returns whatever is in the third column of that row.

Critical rule: VLOOKUP can only look to the right. If the column you need is to the left of the lookup column, VLOOKUP cannot retrieve it without restructuring your table.

IFERROR

When VLOOKUP cannot find a match it returns `#N/A`, which looks unprofessional and can break downstream formulas. Wrap any lookup inside `IFERROR` to substitute a readable message.

Syntax: `=IFERROR(value, value_if_error)`

Example: `=IFERROR(VLOOKUP(A2, CustomerMaster, 3, FALSE), "ID Not Found")`

If the VLOOKUP succeeds, you see the credit limit. If it fails for any reason, you see ID Not Found instead of an error code.

IF

The IF function applies a logical test and returns one of two values depending on whether the test is TRUE or FALSE.

Syntax: `=IF(logical_test, value_if_true, value_if_false)`

For a three-outcome flag (Over Limit / At Limit / OK) you nest one IF inside another:

`=IF(B2 > C2, "Over Limit", IF(B2 = C2, "At Limit", "OK"))`

Here B2 is the AR balance and C2 is the credit limit returned by your VLOOKUP. Excel evaluates the outer IF first; only if that test is FALSE does it evaluate the inner IF.

XLOOKUP

XLOOKUP is a modern replacement for VLOOKUP available in Excel 2019 and Microsoft 365. It is more flexible and easier to read.

Syntax: `=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])`

Example: `=XLOOKUP(A2, CustomerMaster[CustomerID], CustomerMaster[CreditLimit], "ID Not Found")`

Key advantages over VLOOKUP:

1. The return column can be anywhere — left, right, or non-adjacent — because you specify it directly as a range, not as a column number.

2. The `if_not_found` argument handles missing matches without a separate IFERROR wrapper.

3. Column-number references never break when columns are inserted or reordered.

Building the Combined Workbook

1. Open the provided workbook. Sheet 1 is the AR aging list; Sheet 2 is the customer master table.

2. Name the master table `CustomerMaster` using the Name Box.

3. In the AR aging sheet, write a VLOOKUP wrapped in IFERROR in the Credit Limit column.

4. In the Status column, write a nested IF comparing the AR balance to the credit limit.

5. In a separate column, write an XLOOKUP performing the same credit-limit lookup.

6. In your lab log, record both formulas side by side and explain one advantage of XLOOKUP.

Accuracy and Data-Integrity Habits

Small formula errors in bookkeeping can misstate receivables and affect financial decisions. Always test lookup formulas with at least one known-good ID, one ID that is absent from the master table, and one boundary value (balance exactly equal to the limit). Document your test cases in the lab log. Never type a credit limit by hand into the aging sheet — always pull it from the authoritative master table so there is a single source of truth.

Key terms8

VLOOKUPAn Excel function that searches the first column of a specified range for a value and returns a result from a column to the right; requires an exact-match argument of FALSE for ID-based lookups.
XLOOKUPA modern Excel lookup function that searches a specified lookup array and returns a value from a separate return array; supports left-side lookups and has a built-in if_not_found argument.
IFA logical function that evaluates a condition and returns one value when the condition is TRUE and another when it is FALSE; functions can be nested to handle more than two outcomes.
IFERRORA function that wraps another formula and substitutes a specified value whenever the inner formula returns any error, such as #N/A or #REF!.
Named RangeA label assigned to a cell or range of cells using the Name Box; makes formulas easier to read and reduces errors caused by inserting or moving data.
Lookup ValueThe value Excel searches for in the first column of a VLOOKUP table or in the lookup array of an XLOOKUP; in AR work this is typically a customer ID.
Nested IFAn IF function placed inside another IF function as the value_if_true or value_if_false argument, allowing a formula to return more than two possible results.
Exact MatchA lookup setting (FALSE in VLOOKUP; 0 or omitted in XLOOKUP) that requires the lookup value to match the table entry precisely, which is required for account-number and ID lookups.

Worked examples4

1. Cell A2 contains customer ID 1042. The named range CustomerMaster has customer IDs in column A and credit limits in column C (the third column of the range). Write a VLOOKUP wrapped in IFERROR to return the credit limit, showing 'ID Not Found' if the ID is missing.
  1. Start the formula: =IFERROR( — this will catch any error the VLOOKUP produces.
  2. Write the VLOOKUP inside: VLOOKUP(A2, CustomerMaster, 3, FALSE) — A2 is the lookup value, CustomerMaster is the named table, 3 is the column containing the credit limit, and FALSE forces an exact match.
  3. Close the IFERROR with the error text: , "ID Not Found") — this is what displays if VLOOKUP returns any error.
  4. Complete formula: =IFERROR(VLOOKUP(A2, CustomerMaster, 3, FALSE), "ID Not Found")
Answer: =IFERROR(VLOOKUP(A2, CustomerMaster, 3, FALSE), "ID Not Found")
2. Cell B2 holds an AR balance of $4,800. Cell C2 holds a credit limit of $5,000 returned by the VLOOKUP. Write a nested IF in cell D2 to display 'Over Limit' if the balance exceeds the limit, 'At Limit' if it equals the limit, and 'OK' if it is below the limit.
  1. Begin with the outer IF that tests the most severe condition first: =IF(B2 > C2, "Over Limit", — if the balance exceeds the limit, return 'Over Limit'.
  2. Add the inner IF for the equal condition as the value_if_false of the outer IF: IF(B2 = C2, "At Limit", "OK") — if balance equals limit return 'At Limit'; otherwise return 'OK'.
  3. Close both IF parentheses: =IF(B2 > C2, "Over Limit", IF(B2 = C2, "At Limit", "OK"))
  4. Test: $4,800 is less than $5,000, so neither outer nor inner test is TRUE; the formula returns 'OK'.
Answer: =IF(B2 > C2, "Over Limit", IF(B2 = C2, "At Limit", "OK")) returns 'OK' for a $4,800 balance against a $5,000 limit.
3. Rewrite the credit-limit lookup using XLOOKUP. Customer IDs are in the named column CustomerMaster[CustomerID] and credit limits are in CustomerMaster[CreditLimit]. The lookup value is in A2. Display 'ID Not Found' if no match exists.
  1. Begin: =XLOOKUP( — XLOOKUP takes the lookup value first.
  2. Enter the lookup value and lookup array: A2, CustomerMaster[CustomerID], — Excel searches this single column for the value in A2.
  3. Enter the return array: CustomerMaster[CreditLimit], — Excel returns the value from this column in the matched row.
  4. Enter the if_not_found argument and close: "ID Not Found") — no separate IFERROR wrapper is needed.
  5. Complete formula: =XLOOKUP(A2, CustomerMaster[CustomerID], CustomerMaster[CreditLimit], "ID Not Found")
Answer: =XLOOKUP(A2, CustomerMaster[CustomerID], CustomerMaster[CreditLimit], "ID Not Found")
4. A student's VLOOKUP returns #N/A for customer ID 2099, but the master table clearly contains that ID. What are two likely causes to check first?
  1. Check for leading or trailing spaces: the ID in the aging sheet may be stored as '2099 ' (with a space) while the master stores '2099'. Use TRIM() or verify formatting.
  2. Check data type mismatch: the aging sheet may store 2099 as a number while the master stores it as text (or vice versa). Numbers and text that look identical will not match in an exact-match lookup.
  3. Verify the range_lookup argument is FALSE: if it is TRUE or omitted, VLOOKUP uses approximate match logic and may return wrong results or errors for unsorted data.
  4. Confirm the named range CustomerMaster still includes row 2099 and has not been accidentally shortened.
Answer: The two most common causes are a data-type mismatch (number vs. text) between the two sheets and leading or trailing spaces in one of the ID cells.
In class: Instructor demonstrates VLOOKUP syntax step by step on a projected customer master table; students then write their own VLOOKUP to pull each customer's credit limit into the AR aging sheet using customer ID as the lookup value, naming the reference range 'CustomerMaster' using the Name Box. · Students add an IF formula to flag each AR balance as 'Over Limit', 'At Limit', or 'OK' by comparing balance to credit limit, then wrap the VLOOKUP inside IFERROR to display 'ID Not Found' rather than #N/A when a customer ID is missing from the master table. · Students replace their VLOOKUP with XLOOKUP performing the same lookup, compare the two formulas side by side in a written lab log entry that explains one advantage of XLOOKUP over VLOOKUP (such as default error handling or column-order flexibility), then demonstrate both to the instructor.
Materials: Excel, AR aging + customer master workbook file, lab log template, MOS Excel Study Guide
Check: Lab log and live workbook: VLOOKUP, IFERROR, IF, and XLOOKUP all return correct results on instructor test values; written comparison entry present and accurate (pass = all four functions return correct results on 5 instructor test cases; written entry identifies a valid XLOOKUP advantage).

Practice check8

Answer each question, then check it. Nothing is recorded; this is your own practice.

Microsoft Office Specialist – Excel: formulas and functions
1. In a VLOOKUP formula, what does setting the range_lookup argument to FALSE do?
Microsoft Office Specialist – Excel: formulas and functions
2. A VLOOKUP formula returns #N/A. A bookkeeper wraps it in IFERROR with the second argument "ID Not Found". What will the cell display when the lookup value is absent from the table?
Microsoft Office Specialist – Excel: formulas and functions
3. A student needs to pull a customer's phone number from column B of CustomerMaster, but the customer ID is in column C of that same table. Which statement is correct?
Microsoft Office Specialist – Excel: formulas and functions
4. An AR balance in B2 is $5,000 and the credit limit in C2 is $5,000. The formula =IF(B2>C2,"Over Limit",IF(B2=C2,"At Limit","OK")) returns which result?
Microsoft Office Specialist – Excel: formulas and functions
5. Which of the following XLOOKUP arguments makes a separate IFERROR wrapper unnecessary?
Microsoft Office Specialist – Excel: managing workbook data
6. A bookkeeper creates a named range called CustomerMaster for the customer table. What is the primary benefit of using that name in formulas instead of the absolute range address?
AIPB Certified Bookkeeper: adjustments and error correction
7. According to the AIPB Certified Bookkeeper standards, which practice best supports accuracy when an accounts-receivable aging sheet references a separate customer master table?
Microsoft Office Specialist – Excel: formulas and functions
8. A student tests her VLOOKUP with a customer ID that exists in the master table but receives #N/A. After checking, she finds the aging sheet stores the ID as the number 1042, while the master table stores it as the text '1042'. What is the most direct fix?
Record this lesson on my transcript. Sign in with your key and enrollment email to record lessons (enroll free if you have no key). This page stays open to read and practise.
← Previous lessonProgram planNext lesson →