IF or VLOOKUP Function for Assigning Categories with Excel Formula - 8 Examples

Overview

Learn advanced Excel techniques for assigning categories using IF and VLOOKUP functions in this comprehensive 30-minute tutorial. Explore eight practical examples, including assigning race categories to times, sales categories, and product categories for large datasets. Master the use of hard-coded values, cell references, and lookup tables on different sheets. Discover how to implement approximate and exact matches, and learn the trick of hard-coding tables into formulas using the F9 evaluation key. Download the provided Excel start file to follow along and enhance your spreadsheet skills.

Syllabus

min) Overview.
min) IF and Hard Coded Values: Violates Excel's Golden Rule, but it you may not want table on sheet. Assigning Race Categories to Race Times. ISBLANK function..
min) IF and Cell References: Allows you to change Formula Inputs and formulas will update. Assigning Race Categories to Race Times..
min) VLOOKUP: Easier than multiple IFs. First column sorted small to big, column_index = which column has value you want to return to cell, approximate match is default. Assigning Race Categories to Race Times..
min) VLOOKUP for Sales Category, approximate match..
min) VLOOKUP, Exact match. Assigning Categories to Products..
min) VLOOKUP with table hard coded into formula, so you don't have to have table on sheet. F9 = Evaluation key = creates hard coded array..
min) VLOOKUP for Product Category for large data set with lookup table on different Sheet, Exact Match..
min) VLOOKUP for Sales Type for large data set with lookup table on different Sheet, Approximate Match..