Lookup Logic: Harnessing Excel's 17 Most Powerful Functions (Table of Contents)

Summary: Unlock the full potential of your data with Excel's most powerful functions. Lookup Logic: Harnessing Excel's 17 Most Powerful Functions is a key resource in discovering how to use those functions. Here is the complete table of contents for this resource.

Lookup Logic: Harnessing Excel's 17 Most Powerful Functions includes 254 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 Lookup Functions
Need More Tips?
Sharing this Document

Understanding Lookup Functions

Understanding Dynamic Arrays
Wildcards and Regular Expressions
Wildcards
Regular Expressions

Reference Details

CHOOSE
Syntax
Putting CHOOSE to Work
Complementary Functions
Formula Examples
Gotchas and Warnings
FIELDVALUE
Syntax
Putting FIELDVALUE to Work
Complementary Functions
Formula Examples
Gotchas and Warnings
FILTER
Syntax
Putting FILTER to Work
Formula Examples
Gotchas and Warnings
GETPIVOTDATA
Syntax
Putting the Function to Work
Formula Examples
Gotchas and Warnings
GROUPBY
Syntax
Putting GROUPBY to Work
Complementary Functions
Formula Examples
Gotchas and Warnings
HLOOKUP
Syntax
Putting HLOOKUP to Work
Complementary Functions
Formula Examples
Gotchas and Warnings
INDEX
Syntax
Putting INDEX to Work
Complementary Functions
Formula Examples
Gotchas and Warnings
LOOKUP
Syntax
Putting LOOKUP to Work
Formula Examples
Gotchas and Warnings
MATCH
Syntax
Putting MATCH to Work
Complementary Functions
Formula Examples
Gotchas and Warnings
PIVOTBY
Syntax
Putting PIVOTBY to Work
Complementary Functions
Formula Examples
Gotchas and Warnings
RTD
Putting RTD to Work
Formula Examples
Gotchas and Warnings
SORT
Syntax
Putting SORT to Work
Complementary Functions
Formula Examples
Gotchas and Warnings
SORTBY
Syntax
Putting SORTBY to Work
Complementary Functions
Formula Examples
Gotchas and Warnings
UNIQUE
Syntax
Putting UNIQUE to Work
Complementary Functions
Formula Examples
Gotchas and Warnings
VLOOKUP
Syntax
Putting VLOOKUP to Work
Complementary Functions
Formula Examples
Gotchas and Warnings
XLOOKUP
Syntax
Putting XLOOKUP to Work
Formula Examples
Gotchas and Warnings
XMATCH
Syntax
Putting XMATCH to Work
Complementary Functions
Formula Examples
Gotchas and Warnings

Lookup Function Basics

Understanding the CHOOSE Function
Understanding the FIELDVALUE Function
Understanding the FILTER Function
Understanding the GETPIVOTDATA Function
Understanding the GROUPBY Function
Understanding the HLOOKUP Function
Understanding the INDEX Function
Understanding the LOOKUP Function
Understanding the MATCH Function
Understanding the PIVOTBY Function
Understanding the RTD Function
Understanding the SORT Function
Understanding the SORTBY Function
Understanding the UNIQUE Function
Understanding the VLOOKUP Function
Understanding the XLOOKUP Function
Understanding the XMATCH Function
Maintaining Text Formatting in a Lookup
Limiting the Number of Results from a Function
Disabling #SPILL! Errors
Errors when Copying References to External Cells

Addresses and Cell References

Referring to the Last Cell
Returning the Rightmost Value in a Row
Grabbing the Second-to-Last Value in a Column
Cell Address of a Maximum Value
Address of a Cell in Which a Threshold is Exceeded
Conditionally Highlighting a Milestone Cell
Non-adjusting References in Formulas
Stopping a Formula from Updating References
Where Is that Text?
Referencing Every Third External Cell

Finding Duplicate or Unique Values

Deleting Duplicate Text Values
Using the Delete Duplicates Tool
Using Data Filtering
Using a Formula
Using a Macro
Filtering Columns for Unique Values
Finding Unique Rows Based on Two Columns
Ensuring Unique Values in a Column
Synchronizing Lists
Counting Unique Values
Getting a Count of Unique Names
Finding Differences Between Lists
Getting Rid of Everything Except Numbers
Generating a Unique ID Number
Calculating Unique IDs Based on Names and Initials
Extracting Foreign-Language Characters
Deleting All Rows Except for the End of Month

Grabbing Info from a List

Listing the Top Five Transactions
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
Extracting Targeted Records from a List
Determining a State from an Area Code
Filtering Addresses for a Group of States
Using Filtering
Using Advanced Filtering
Filtering in a Table
Using a Formula
Using a Macro
Locating a Single-Occurrence Value in a Column
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
Filtering Based on Comparing Two Cells
Looking Backward through a Data Table
Pulling a Phone Number with a Known First and Last Name
Reversing Integer Values
Displaying Letter Grades
Proper Case Conversion with Exceptions
Sorting Serial Numbers by Their Trailing Characters
Determining a Name for a Week Number
Deriving a List of Albums by a Music Artist
Adjusting Formulas for Top-Added Rows
Values Beginning with a Specific Letter or Digit

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
Determining Business Quarters from Dates
Finding the Dates for Minimums and Maximums
Determining a Zodiac Sign from a Birthdate
Deciphering a Coded Date
Finding the Date Associated with a Negative Value
Generating a 4 On/4 Off Work Schedule
Easily Changing Chart Data Ranges

Looking Up Random Data

Picking Different Random Numbers from a Range
Generating Random Strings of Characters
Generating Numeric Testing Data
Generating a Gift Exchange List

Calculations Involving Lookups

Summing the Four Largest Unique Values
Calculating the Interval between Occurrences
Breaking Up Variable-Length Part Numbers
Finding the First Non-Digit in a Text Value
First Value Less Than or Equal to 100
How Many Times Does Each Name Appear in a List?
Averaging Based on Multiple Criteria
Calculating the Average of Five Values Near the End of a Range
Calculating an Average within a Date Range
Calculating the Median Age of a Group of People
Filtering to a Standard Deviation
Doing Math Using Roman Numerals

Adjusting How VLOOKUP Works

Making VLOOKUP Trigger a Macro
Making VLOOKUP Case Sensitive
Returning Blanks with VLOOKUP
Using VLOOKUP to Access Information to the Left
Returning Values to the Left of a VLOOKUP
Using XLOOKUP
Restructuring Your Content
Using INDEX and MATCH
Using VLOOKUP and CHOOSE

To celebrate the release of Lookup Logic: Harnessing Excel's 17 Most Powerful Functions, for a limited time (through November 11, 2025, 11:59 PM Pacific Time) you can secure your own copy, in any format, for 30% off the regular price. Get your discount; place your order today!