Logic & Lookup Functions: IF, VLOOKUP, INDEX/MATCH

Financial analysts spend a lot of time connecting data across different tables. These functions automate exactly that.

IF — conditional logic

=IF(condition, value_if_true, value_if_false)

Example: flag a customer as “overdue” if their invoice is older than 30 days:

=IF(B2>30, "Overdue", "Current")

You can nest multiple conditions, or combine them with AND / OR:

=IF(AND(B2>30, C2>10000), "Overdue and large", "Normal")

VLOOKUP — vertical lookup

Searches for a value in the first column of a table, and returns a value from another column in that same row:

=VLOOKUP(lookup_value, table_range, column_number, FALSE)

That trailing FALSE means “exact match” — forget it, and Excel may return the closest value instead of an exact one, a very common source of errors.

INDEX + MATCH — the more powerful alternative

VLOOKUP’s weakness is that it only searches rightward and needs the lookup column to be first. INDEX/MATCH solves both:

=INDEX(result_column, MATCH(lookup_value, search_column, 0))

MATCH returns the position where the value was found, and INDEX uses that position to pull a value from any other column — even one to the left of the search column. Most professional analysts prefer INDEX/MATCH over VLOOKUP in large models because it’s faster and more flexible.

Next up: the core financial functions — NPV, IRR, PMT — plus a hands-on interactive lab.