If Cell Contains Text Then Return Value: A Complete Guide
Learn how to use Excel's powerful functions like COUNTIF and SEARCH to return values based on cell text, enhancing your data processing capabilities.
What is XLOOKUP?
Basic Definition
XLOOKUP is an advanced lookup function in Excel that can search for items in a table or range and return corresponding items found in another column, essential for users needing to implement conditional logic like "if cell contains text then return value".
- Supports bi-directional lookup
- Can return multiple matches
- Provides exact and approximate matching
- Built-in error handling
Key Advantages
No need to consider data position, automatically handles relative column positions
Supports left lookup, breaking VLOOKUP's right-only limitation
Built-in multiple matching modes for different scenarios
Function Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_value
The value to search for
lookup_array
The range to search within
return_array
The range containing the return values
if_not_found (Optional)
Value to return if no match is found
match_mode (Optional)
0: Exact match; -1: Exact or next smaller; 1: Exact or next larger
search_mode (Optional)
1: First-to-last; -1: Last-to-first; 2: Binary search
Usage Examples
Basic Example
Looking up contact phone number for a specified customer ID in customer data table
=XLOOKUP(A2, B2:B10, C2:C10)
Learn More
Advanced Example
Cross-sheet lookup with multiple matching results
=XLOOKUP(A2, Sheet2!B:B, Sheet2!C:E, "Not Found", 0)
Learn More
Use Our Product, No Complex Functions to Memorize
Easily complete data lookup and analysis through a simple interface
Try NowTroubleshooting
#N/A Error
Common error when no match is found, can be handled by setting the if_not_found parameter
Data Type Mismatch
Ensure lookup value and range data types match
Performance Optimization
Use binary search mode to improve efficiency with large datasets
Function Comparison
| Feature | XLOOKUP | VLOOKUP | INDEX+MATCH |
|---|---|---|---|
| Lookup Direction | Bi-directional | Right only | Bi-directional |
| Multiple Column Return | Supported | Not supported | Supported |
| Error Handling | Built-in | Additional handling needed | Additional handling needed |
Start Using Our Product
Process data quickly through an intuitive interface without writing complex functions