7.1 Lookup Functions

Lookup Functions in FMVA Excel Work

CFI's FMVA final exam is a three-hour, Excel-required assessment: 50 randomized multiple-choice questions, including modeling case studies. Excel is about 10% of the estimated exam weight, and the modeling cases fail for a more practical reason than a missing shortcut. If a lookup returns the wrong weighted average cost of capital (WACC), the wrong volume driver, or a neighbor's ticker because range_lookup was omitted, the rest of the workbook is arithmetic on a bad input. CFI's Advanced Excel Formulas list puts INDEX MATCH first and treats VLOOKUP as the function an analyst is supposed to outgrow.

This section is the lookup toolkit for those case studies: exact versus approximate match, why VLOOKUP breaks when you insert a column, how INDEX MATCH MATCH does a two-way pull, what XLOOKUP adds, and when CHOOSE or OFFSET is the right — or wrong — tool.

The Comps Table You Will Keep Breaking

Horizon Foods is filling a driver sheet from a small comparable-company range. Tickers sit in column A, and the WACC the discounted cash flow (DCF) needs sits in column E.

Cell H2 contains the ticker BRIO. The task is to pull BRIO's 9.2% WACC into the valuation, then later pull whichever field a column-header toggle asks for.

VLOOKUP Syntax and the Traps CFI Wants You to Name

VLOOKUP looks down the first column of a table and returns a value from a column to the right:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Horizon's naive formula is:

=VLOOKUP(H2, A2:E5, 5, FALSE)

That currently returns 9.2%. Four traps sit inside a formula that works on the demo sheet.

Trap 1 — it cannot look left. The lookup column must be the left edge of table_array. If the ticker is in column E and WACC is in column A, VLOOKUP cannot return it. INDEX MATCH and XLOOKUP can.

Trap 2 — col_index_num is a hardcoded integer. The 5 means fifth column of A2:E5, not the column headed WACC. Insert a Notes column between Segment and Revenue and the sheet becomes A ticker, B segment, C notes, D revenue, E margin, F WACC. VLOOKUP(H2, A2:F5, 5, FALSE) still calculates. It now returns BRIO's 12% EBITDA margin. The DCF discounts cash flow at 12% instead of 9.2%. There is no #REF!. The model is silently wrong. This is the reason CFI prefers INDEX MATCH: the return range is an actual range, so an insert that does not land inside that range leaves the formula pointing at WACC.

Trap 3 — omitted range_lookup defaults to TRUE (approximate). Approximate VLOOKUP requires the first column to be sorted ascending and returns the largest value less than or equal to the lookup. Unsorted tickers can match a neighbor. BRIO might pull APEX's 8.5%. Always pass FALSE (or 0) for a ticker, account name, or scenario label.

Trap 4 — the table must start at the lookup column. VLOOKUP(H2, B2:E5, 4, FALSE) looks for BRIO in the Segment column, fails, and returns #N/A.

HLOOKUP is the horizontal twin and inherits the same index-number and approximate-match traps. Do not use it in a column-oriented CFI model when INDEX MATCH MATCH will do.

MATCH Type 0 Versus Approximate Match

MATCH returns a relative position, not a value:

=MATCH(lookup_value, lookup_array, [match_type])

=MATCH(H2, A2:A5, 0) returns 2, because BRIO is the second row of A2:A5. Combine it with INDEX and you have CFI's preferred lookup.

If you omit the third argument, MATCH defaults to 1. On an unsorted text list that is either #N/A or a nearby row — both are exam failures when the key is a ticker.

Approximate MATCH is not a bug when you intend a bracket. Pretax income is $87,000. Bracket floors in ascending order are $0, $50,000, $100,000 with rates 15%, 25%, 35%. MATCH(87000, bracket_floors, 1) returns 2, and INDEX of the rate list returns 25%. That is the designed behavior. Using type 1 on an unsorted ticker list is the bug.

INDEX MATCH, INDEX MATCH MATCH, and XLOOKUP

CFI's preferred one-way lookup is:

=INDEX(return_range, MATCH(key, lookup_range, 0))

Horizon WACC:

=INDEX($E$2:$E$5, MATCH(H2, $A$2:$A$5, 0))

INDEX returns the nth item of the return range. MATCH supplies n. The return range can sit to the left or the right of the key, which is the look-left problem VLOOKUP cannot solve. Inserting a Notes column does not change $E$2:$E$5 if you inserted outside that range — and if the WACC column moved, you still update a range, not a magic integer that happens to land on EBITDA margin.

Dollar-lock the ranges so copying the formula down a driver board does not crawl off the table. The match type stays 0.

Worked check: H2 is BRIO. MATCH of BRIO in A2:A5 is 2. INDEX of E2:E5 at position 2 is 9.2%. Change H2 to CALA and MATCH becomes 3, INDEX returns 8.0%. No column index to forget after the next layout edit.

Two-Way Lookup: INDEX MATCH MATCH

CFI's two-way pattern is:

=INDEX(table, MATCH(row_key, row_range, 0), MATCH(col_key, col_range, 0))

The value grid is C2:E5 (revenue, margin, WACC). Row keys are A2:A5. Column headers are C1:E1. H2 is BRIO. H3 is the header text WACC.

=INDEX($C$2:$E$5, MATCH(H2, $A$2:$A$5, 0), MATCH(H3, $C$1:$E$1, 0))

Row MATCH of BRIO is 2. Column MATCH of WACC is 3. INDEX returns the second row, third column of the grid: 9.2%. Change H3 to the revenue header and the same formula returns 185 without rewriting a column index. That is the toggle you want on a sensitivity board or a football-field driver sheet.

Do not mix MATCH type 0 on the ticker with MATCH type 1 on the headers. Headers are labels; they need exact match. A misspelled header returns #N/A, which is better than a silent approximate grab of the neighboring field.

XLOOKUP: Left, Right, and Missing Values

XLOOKUP is the modern replacement:

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

It can look left or right because the lookup array and the return array are separate, as in INDEX MATCH. Default match_mode is 0 (exact) — the opposite of VLOOKUP's default. The fourth argument handles a missing key without wrapping IFNA:

=XLOOKUP(H2, $A$2:$A$5, $E$2:$E$5, "Missing ticker")

If H2 is ZEUS, INDEX MATCH returns #N/A and a WACC row that feeds a DCF becomes an error. XLOOKUP can return a message, a zero, or a backup rate, depending on what you pass.

FMVA case files must run in Excel 2016 or later. XLOOKUP requires Microsoft 365 or Excel 2021. INDEX MATCH is the portable CFI answer on a mixed-version exam machine. Use XLOOKUP in your own 365 models; be ready to rewrite it as INDEX MATCH in a case workbook that predates XLOOKUP.

CHOOSE for Scenario Toggles

CHOOSE returns the nth argument:

=CHOOSE(index_num, value1, value2, ...)

Put 1 / 2 / 3 in $K$1 for base / upside / downside:

=CHOOSE($K$1, 0.08, 0.09, 0.07)

Revenue growth can sit on the same switch: =CHOOSE($K$1, 0.04, 0.08, 0.00). CFI lists CHOOSE among the ten advanced formulas because a three-case toggle is readable in a live model. Past four or five cases, INDEX of a scenario row (or a dedicated scenario sheet) is cleaner than a 12-argument CHOOSE. Do not hide the live case in an OFFSET from a named cell that no reviewer can see; put the 1/2/3 switch on the cover sheet in blue font as an input.

OFFSET — On CFI's List, Volatile in a Model

OFFSET returns a reference shifted from a starting cell:

=OFFSET(reference, rows, cols, [height], [width])

CFI teaches OFFSET combined with SUM or AVERAGE to build a dynamic range. If H1 holds the number of forecast years to include, =SUM(OFFSET(C2, 0, 0, H1, 1)) sums H1 rows of revenue starting at C2.

OFFSET is volatile. Excel recalculates it whenever any cell in the workbook changes, not only when its inputs change. A three-statement model with OFFSET in every forecast year becomes slow and painful to audit with Trace Precedents, because the precedent is a calculated reference rather than a fixed range.

Non-volatile equivalent:

=SUM(C2:INDEX(C2:C20, H1))

Same dynamic height, no volatility. Use OFFSET in a small dashboard that must expand and contract. Do not use it to walk every line of a 3-statement forecast or inside a circular interest schedule.

Exam Traps Worth Memorizing

• VLOOKUP column index after an insert — silent wrong driver.
• MATCH or VLOOKUP without an exact-match flag on unsorted text.
• Approximate MATCH on a sorted tax table is valid; approximate MATCH on tickers is not.
• XLOOKUP is unavailable in Excel 2016; INDEX MATCH is the version-safe FMVA pattern.
• OFFSET inside a large model is a performance and audit problem, even though it appears on CFI's advanced list.

Lookups do not make a model correct. They make the model pull the right driver. Section 7.2 aggregates actuals, budgets, and cohorts with conditions. Section 7.3 turns dated cash flows into NPV and loan math with XNPV, XIRR, and PMT.