ExcelTips: Powerful Lookup Functions (Table of Contents)

ExcelTips: Powerful Lookup Functions, fourth edition, includes 131 information-packed pages that will help you discover and master the power behind the lookup functions provided by Excel. The following is the Table of Contents:

Introduction

• A Word about Versions
• Macros and Array Formulas
• Need More Tips?
• Sharing this Document

Lookup Function Basics

• Understanding the CHOOSE Function
• Understanding the MATCH Function
• Understanding the INDEX Function
• Understanding the OFFSET Function
• Understanding the VLOOKUP Function
• Understanding the HLOOKUP Function
• Understanding the LOOKUP Function
• Maintaining Text Formatting in a Lookup
• Errors when Copying References to External Cells

Addresses and Cell References

• Cell Address of a Maximum Value
• Finding the Address of the Lowest Value in a Range
• Referring to the Last Cell
• Referencing the Last Cell in a Column
• Relative References within Named Ranges
• Non-adjusting References in Formulas
• Stopping a Formula from Updating References
• Incrementing References by Multiples when Copying Formulas
• Where Is that Text?

Finding Duplicate Values or Unique Values

• Synchronizing Lists
• Comparing Lists for Duplicates
• Cleaning Up Lists
• Ensuring Unique Values in a Column
• Counting Unique Values
• Getting a Count of Unique Names
• Finding Differences Between Lists
• Getting Rid of Everything Except Numbers
• Deleting All Names but a Few

Grabbing Info from a List

• Returning a Weight and a Date
• Retrieving the Last Value in a Column
• Last Non-Zero Value in a Row
• Extracting Numbers within a Range
• Highlighting Cells Containing both Letters and Numbers
• Determining a State from an Area Code
• Figuring Out the Low-Score Winner
• Determining Winners, by Category
• Determining "Highest Since" or "Lowest Since"
• Returning Blanks or Asterisks from a Lookup
• Returning Item Codes Instead of Item Names
• Looking Backward through a Data Table
• Pulling a Phone Number with a Known First and Last Name
• Looking Up Names when Key Values are Identical
• Reversing Integer Values
• Compiling a List of Students in a Course
• Displaying Letter Grades
• Determining a Name for a Week Number
• Adjusting Formulas for Top-Added Rows
• Complex Lookup Formulas

Using Lookup Functions with Dates

• Macro for Month Name
• Automatically Advancing by a Month
• Adding Ordinal Notation to Dates
• Date for Next Wednesday
• Calculating Future Workdays
• Calculating the First Business Day of the Month
• Determining Business Quarters from Dates
• Specifying Different Weekends with NETWORKDAYS
• Finding the Dates for Minimums and Maximums
• Deciphering a Coded Date
• Finding the Date Associated with a Negative Value

Looking Up Random Data

• Picking Different Random Numbers from a Range
• Selecting Random Names
• Generating Random Strings of Characters

Calculations Involving Lookups

• Calculating the Interval between Occurrences
• Adding Dashes between Letters
• Inserting Dashes between Letters and Numbers
• Breaking Up Variable-Length Part Numbers
• Finding the First Non-Digit in a Text Value
• Calculating the Median Age of a Group of People
• Determining a Simple Moving Average
• Averaging the Last Numbers in a Column
• Referencing the Last Six Items in a Formula
• Engineering Calculations

Adjusting the Way VLOOKUP Works

• Making VLOOKUP Trigger a Macro
• Adjusting the VLOOKUP Function
• Making VLOOKUP Case Sensitive
• Returning Blanks with VLOOKUP
• Using VLOOKUP to Access Information to the Left
• Concatenating Values from a Variable Number of Cells

Using Lookup Functions to Enhance Charts

• Easily Changing Chart Data Ranges
• Automatically Updating Charts for Additional Data
• Automatically Creating Charts for Individual Rows in a Data Table
• Creating a Year-to-Date Comparison Chart

Description	Download
ExcelTips: Powerful Lookup Functions	ISBN 978-1-61359-364-6 (1.7 MB) $24.99