Excel is the backbone of investment banking. Whether you are building a three-statement financial model, running a DCF, or putting together a pitchbook, you will spend a significant portion of your time in spreadsheets. Knowing the right Excel functions (and knowing them cold) is what separates an efficient analyst from one who is constantly behind.
This guide covers the most important Excel functions for investment banking, organized by category. If you are preparing for an internship or full-time analyst role, mastering these functions will make your life dramatically easier from day one.
Lookup and Reference Functions
Lookup functions are among the most frequently used in banking. You will use them to pull data across sheets, link models together, and build dynamic outputs.
INDEX MATCH
INDEX MATCH is the gold standard for lookups in investment banking. While VLOOKUP is more widely known, INDEX MATCH is superior for several reasons: it can look left (not just right), it does not break when you insert or delete columns, and it is generally faster on large datasets.
The syntax is: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
For example, if you have a list of company names in column A and their enterprise values in column D, you can use INDEX MATCH to pull the enterprise value for a specific company regardless of where those columns sit in the worksheet. The “0” at the end of the MATCH function specifies an exact match, which is what you want in virtually all banking applications.
VLOOKUP and HLOOKUP
VLOOKUP is still widely used, and you should know it even if INDEX MATCH is your go-to. The syntax is: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE). Always use FALSE for the last argument to force an exact match. HLOOKUP works the same way but searches horizontally across rows instead of vertically down columns.
XLOOKUP
XLOOKUP is a newer function available in Office 365 that combines the best features of INDEX MATCH and VLOOKUP into a single, cleaner formula. The syntax is: =XLOOKUP(lookup_value, lookup_array, return_array). It defaults to exact match, can search in any direction, and handles errors more gracefully. That said, not every bank has adopted the latest version of Excel, so you should still be fluent in INDEX MATCH.
OFFSET
OFFSET returns a reference to a range that is offset from a starting cell by a given number of rows and columns. It is particularly useful in financial models for creating dynamic ranges, for example, when you want to reference a row of data that shifts depending on a scenario toggle. The syntax is: =OFFSET(reference, rows, cols, [height], [width]).
Math and Aggregation Functions
SUM, SUMIF, and SUMIFS
SUM is self-explanatory, but SUMIF and SUMIFS are where things get more useful. SUMIF lets you sum a range based on a single criterion, while SUMIFS allows multiple criteria. These are essential when working with large data sets, for example, summing all revenue from a specific product line across multiple quarters in a comparable company analysis.
SUMPRODUCT
SUMPRODUCT multiplies corresponding elements of two or more arrays and returns the sum of those products. It is incredibly versatile: you can use it for weighted averages (like calculating WACC), conditional sums without helper columns, and array-based calculations. The syntax is: =SUMPRODUCT(array1, array2). For a weighted average, you might use =SUMPRODUCT(weights, values) / SUM(weights).
COUNT, COUNTA, COUNTIF, and COUNTIFS
COUNT counts cells with numbers, COUNTA counts non-blank cells, and COUNTIF/COUNTIFS count cells meeting specific criteria. These are useful for data validation, checking completeness of inputs, and building summary tables in your models.
MIN, MAX, AVERAGE, and MEDIAN
These basic statistical functions come up constantly in valuation work. When presenting a range of trading multiples in a comps table, you will almost always show the min, max, mean, and median for each metric. Know these cold.
Logical Functions
IF, AND, OR, and NOT
IF statements are everywhere in financial models. You will use them for scenario toggles, conditional formatting of outputs, and building flexibility into your models. Nested IFs are common but can get messy: consider IFS (available in newer versions) for cleaner multi-condition logic.
AND, OR, and NOT are typically used inside IF statements to test multiple conditions. For example: =IF(AND(revenue>0, margin>0.1), "Pass", "Fail").
IFERROR and IFNA
IFERROR wraps around any formula and returns a specified value if the formula results in an error. This is critical for keeping your models clean: nobody wants to see #DIV/0! or #N/A scattered across a pitchbook. Use it around division formulas, lookups, and any formula that could break with missing data. A common pattern: =IFERROR(revenue/shares, "").
Date and Time Functions
Date functions are especially important in banking because deal timelines, fiscal year calculations, and stub period adjustments all depend on accurate date math.
DATE, YEAR, MONTH, and DAY
These are the building blocks. DATE constructs a date from year, month, and day components. YEAR, MONTH, and DAY extract those components from a date. You will use them to build fiscal year timelines and calculate periods between dates.
EOMONTH
EOMONTH returns the last day of a month that is a specified number of months before or after a given date. It is very useful for building model timelines. For example, =EOMONTH(start_date, 3) gives you the end of the month three months out, perfect for quarterly model periods.
DAYS and YEARFRAC
DAYS gives you the number of days between two dates. YEARFRAC returns the fraction of the year between two dates, which is essential for stub period calculations in a DCF analysis. When discounting cash flows, you need to know exactly what fraction of a year each period represents.
Text Functions
Text functions are less glamorous but come up more than you might expect, especially when cleaning data for comps or reformatting outputs.
CONCATENATE and the Ampersand (&)
Both combine text strings. The ampersand is faster to type. You will use this to build dynamic labels, merge company names with tickers, or create formatted cell references. For example: =company_name & " (" & ticker & ")".
LEFT, RIGHT, MID, and TEXT
LEFT, RIGHT, and MID extract substrings from text. TEXT formats numbers and dates as text with a specified format, very useful for creating dynamic headers like =TEXT(date_cell, "MMM-YY") to display “Jun-26” in a column header.
TRIM and CLEAN
TRIM removes extra spaces from text, and CLEAN removes non-printable characters. These are essential when you are pulling data from external sources (like Capital IQ or FactSet) that often come with hidden formatting characters that can break your formulas.
Financial Functions
NPV and XNPV
NPV calculates net present value assuming equal periods between cash flows. XNPV is the more flexible version: it lets you specify exact dates for each cash flow, which is important for real-world DCFs where periods are not perfectly even. Most senior bankers will expect you to use XNPV in a full DCF model rather than the basic NPV function.
IRR and XIRR
IRR calculates the internal rate of return for a series of cash flows. Like NPV/XNPV, XIRR is the date-specific version and is preferred in practice. These are particularly important in LBO models where you are calculating sponsor returns.
PMT, PPMT, and IPMT
PMT calculates the periodic payment for a loan with a constant interest rate. PPMT and IPMT break that payment into principal and interest components, respectively. These are useful when building debt schedules in LBO models or modeling amortizing term loans in a leveraged finance context.
Rounding Functions
Rounding matters more than you think in banking. A pitchbook with numbers showing 14 decimal places looks sloppy. These functions keep your outputs clean.
ROUND, ROUNDUP, and ROUNDDOWN
ROUND rounds to a specified number of digits. ROUNDUP and ROUNDDOWN always round in a specific direction. Use ROUND for most applications. A common pattern: =ROUND(enterprise_value / ebitda, 1) to show a multiple to one decimal place.
INT and TRUNC
INT rounds down to the nearest integer. TRUNC simply truncates (removes) decimal places without rounding. Both are useful for share count calculations and other situations where you need whole numbers.
Other Essential Functions
TRANSPOSE
TRANSPOSE flips data from rows to columns or vice versa. This is useful when you receive data in a layout that does not match your model structure. You can also use Paste Special > Transpose for a static version.
CHOOSE
CHOOSE returns a value from a list based on an index number. It is commonly used for scenario analysis, for example, linking a dropdown that says “Base,” “Upside,” or “Downside” to different assumption sets. =CHOOSE(scenario_toggle, base_case, upside_case, downside_case).
INDIRECT
INDIRECT converts a text string into a cell reference. This is powerful for building models that dynamically reference different worksheets or ranges based on user inputs. For example, you can use it to pull data from a sheet whose name is stored in a cell.
Tips for Learning These Functions
Knowing the syntax is only half the battle, and you need to be fast. Here are a few tips:
- Practice in the context of models. Building a full three-statement model will expose you to most of these functions naturally.
- Learn the keyboard shortcuts for entering formulas. Tab to autocomplete a function name, Ctrl+Shift+Enter for array formulas (in older Excel versions), and F4 to toggle absolute references.
- Use F2 to edit formulas in place and trace which cells are being referenced. This is how you will audit other people’s models.
- Do not over-nest. If your formula is five levels deep, break it into helper cells. Clean models are maintainable models.
- Check out our free resources for additional Excel and modeling practice materials.
How These Functions Come Up in Interviews
In investment banking interviews, you are unlikely to be asked “what is the syntax of SUMPRODUCT?” But you may be asked to walk through how you would build a model, and demonstrating fluency with Excel functions shows that you can actually do the job. Some banks include Excel modeling tests as part of their Superday, and in those cases, speed and accuracy with these functions is directly tested.
The bottom line: learn these functions, practice them until they are second nature, and you will be ahead of most candidates walking into an analyst seat.
Want Personalized Interview Coaching?
If you are serious about breaking into investment banking, the best thing you can do is work with someone who has been through the recruiting process and knows exactly what top banks are looking for. At Wall Street Mastermind, we have helped over 2,500 students land offers at every bulge bracket and elite boutique bank on Wall Street. Book a free strategy call to learn how we can help you prepare for your interviews and maximize your chances of landing the offer.
Related Articles
- Essential Excel Shortcuts for Investment Banking
- Building a Three-Statement Financial Model: Complete Guide
- Building an LBO Model from Scratch: Step-by-Step Guide
- Walk Me Through a DCF
- How to Prepare for Investment Banking Interviews
- Error Checking and Auditing Financial Models
Follow Wall Street Mastermind
Stay up to date with the latest investment banking recruiting tips, technical guides, and career advice:
- YouTube
- TikTok
- X (Twitter)
- Podcast: Apple Podcasts | Spotify | SoundCloud
