Explore ChatGPT
Excel Cheat Sheet: Lookups and Dynamic VLOOKUPs
This cheat sheet provides a concise guide to mastering lookup functions in Excel, complete with dynamic use cases.
Watch your workshop now
Lookup Functions Overview
VLOOKUP Syntax and Use Cases
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Description: Looks up a value in the first column of a table and returns data from a specified column.
Inputs:
  • lookup_value: The value you're searching for.
  • table_array: The range containing the lookup data. The first column must contain the lookup_value.
  • col_index_num: The column number from which to return the data (starting from 1).
  • range_lookup: Use FALSE or 0 for exact matches and TRUE or 1 for approximate matches.
Example:
Data: ID, Name, Age. Find the age for ID 3:
=VLOOKUP(3, A1:C10, 3, FALSE)
Limitations of VLOOKUP and HLOOKUP
VLOOKUP Limitations
  • Directionality: Can only look up values from left to right.
  • Static Column Index: Requires a fixed column index, which can cause errors if columns are added/removed.
  • Exact Match Errors: Returns #N/A if the value isn't found.
HLOOKUP Syntax and Use Cases
Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Description: Similar to VLOOKUP, but works with horizontal data.
Inputs: Same as VLOOKUP, but row_index_num instead of col_index_num.
Example: Data: ID, Name, Age (horizontally arranged). Find the age for ID 3:
=HLOOKUP(3, A1:C10, 3, FALSE)
Handling Errors and Dynamic VLOOKUP
Handling Errors in Lookup Functions
Dynamic VLOOKUP with COLUMN
Problem: Apply one formula across multiple columns without manually changing col_index_num.
Dynamic VLOOKUP Syntax: =VLOOKUP(lookup_value, table_array, COLUMN(B1)+2, FALSE)
Dynamically adjusts the column index as the formula is copied across.
Example: Data: Shipping Mode, Customer Name, Segment, Country. Use:
=VLOOKUP(A2, Table_Array, COLUMN(B1)+2, FALSE)
Real-World Use Cases
A. Handling Returns
Find customer names for returned orders:
=VLOOKUP(order_id, orders_data, 6, FALSE)
Add a Return Status column:
=IFERROR(VLOOKUP(order_id, returns_data, 1, FALSE), "Not Returned")
Further refinement:
=IF(VLOOKUP(order_id, returns_data, 1, FALSE) = order_id, "Returned", "Not Returned")
B. Extracting Data for Returned Orders
Fetch multiple columns dynamically:
=VLOOKUP(order_id, orders_data, COLUMN(B1)+3, FALSE)
Tips for Efficient Lookup Usage
Freeze Ranges
Use $ to freeze ranges and prevent them from shifting during copying ($A$1:$C$10).
Use Named Ranges
Assign names to your data ranges for easier formula readability.
Validate Data
Use Data > Data Validation to prevent errors in lookup values.
Combine with Other Functions
IFERROR for error handling. IF for conditional checks. COLUMN for dynamic adjustments.
Common Troubleshooting