Professional Guide to Excel Lookup & Array Functions
1. VLOOKUP (Vertical Lookup)
Definition:
VLOOKUP stands for "Vertical Lookup". It searches for a value in the first column
of a table and returns a value in the same row from another column.
Syntax:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Arguments:
- lookup_value: The value to search for.
- table_array: The range of cells that contains the data.
- col_index_num: The column number in the table_array from which to retrieve the
value.
- range_lookup: Optional. TRUE for approximate match, FALSE for exact match.
Example:
=VLOOKUP(102, A2:C10, 3, FALSE)
This looks for employee ID 102 in the first column of the range A2:C10 and returns
the corresponding value from the 3rd column.
Use Case: Retrieve employee name or department using an employee ID.
2. XLOOKUP
Definition:
XLOOKUP is a more flexible and powerful replacement for older lookup functions like
VLOOKUP and HLOOKUP.
Syntax:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode],
[search_mode])
Arguments:
- lookup_value: The value to search for.
- lookup_array: The array to search.
- return_array: The array to return the result from.
- if_not_found: Value to return if not found (optional).
- match_mode: Optional. 0 = exact match, -1 = exact match or next smaller, 1 =
exact match or next larger.
- search_mode: Optional. 1 = search from first to last, -1 = search from last to
first.
Example:
=XLOOKUP("John", A2:A10, B2:B10, "Not Found")
This searches for "John" in A2:A10 and returns the value from B2:B10 on the same
row.
Use Case: Find a product's price using its name.
3. HLOOKUP (Horizontal Lookup)
Definition:
HLOOKUP searches for a value in the first row of a table and returns a value in the
same column from a specified row.
Syntax:
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Example:
=HLOOKUP("Q1", A1:D4, 2, FALSE)
Searches for "Q1" in row 1 and returns the corresponding value from row 2.
4. INDEX and MATCH Combination
Definition:
Using INDEX and MATCH together provides a more robust alternative to VLOOKUP,
allowing left-side lookups and avoiding static column indexes.
Syntax:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Example:
=INDEX(B2:B10, MATCH("HR", A2:A10, 0))
Returns the department from column B where the name "HR" is found in column A.
5. CHOOSE Function
Definition:
The CHOOSE function returns a value from a list of values based on an index number.
Syntax:
CHOOSE(index_num, value1, [value2], ...)
Example:
=CHOOSE(2, "HR", "Finance", "IT")
Returns "Finance" as it is the second value in the list.
Use Case: Select a department based on a numeric choice.
6. Array Formulas
Definition:
Array formulas perform multiple calculations on one or more items in an array and
return either a single result or multiple results.
Modern Dynamic Arrays (Excel 365):
=FILTER(A2:B10, B2:B10>50000)
This returns all rows where column B (salary) is greater than 50000.
Legacy CSE (Ctrl+Shift+Enter):
=SUM(A2:A10*B2:B10)
This calculates the sum product. Must be entered using Ctrl+Shift+Enter in older
versions.
7. Cell Referencing in Excel
Types of References
1. Relative Reference
- Definition: Adjusts automatically when copied to another cell.
- Example: =A1 + B1
- Use Case: Basic calculations that need to shift with the formula.
- Behavior: If copied to cell D2, it becomes =A2 + B2.
2. Absolute Reference
- Definition: Remains constant when the formula is copied.
- Syntax: Use $ to fix column or row.
- Example: =$A$1 + B1
3. Mixed Reference
- Definition: Fixes either column or row.
- Examples: $A1 (column fixed), A$1 (row fixed)
Dynamic Named Ranges and Structured References
- Named Ranges: Created via Name Manager (Ctrl+F3), e.g., =SalesData
- Structured References: Use Excel Tables, e.g., =SUM(Table1[Sales])
Best Practices:
- Use absolute references for constants.
- Use structured references with Tables.
- Prefer named ranges for readability.
- Ensure consistency in array formulas.
Best Practices
- Use named ranges or structured tables for clarity.
- Use XLOOKUP instead of VLOOKUP/HLOOKUP for flexibility.
- Validate lookup arrays for uniqueness.
- Prefer INDEX-MATCH for left-side lookups and performance.
References
- Microsoft Excel Documentation: [Link]
- ExcelJet Lookup Guide: [Link]
Prepared by:
[Your Name]
Excel Function Specialist | Corporate Data Solutions