Excel Magic Trick 1134

IF or VLOOKUP function for Assigning Categories w/ Excel Formula 8 Examples

DESCRIPTION

See how to assign product and sales categories using various methods:
Overview
(00:44) Overview
(01:30) 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.
(08:32) IF and Cell References: Allows you to change Formula Inputs and formulas will update. Assigning Race Categories to Race Times.
(13:42) 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.
(18:19) VLOOKUP for Sales Category, approximate match.
(19:32) VLOOKUP, Exact match. Assigning Categories to Products.
(21:50) VLOOKUP with table hard coded into formula, so you don’t have to have table on sheet. F9 = Evaluation key = creates hard coded array.
(23:59) VLOOKUP for Product Category for large data set with lookup table on different Sheet, Exact Match.
(27:47) VLOOKUP for Sales Type for large data set with lookup table on different Sheet, Approximate Match.

WORKBOOKS




Be the first to comment

Leave a Reply