Excel VLOOKUP and FIND combination
Categories:
Mastering Excel VLOOKUP with FIND for Dynamic Lookups
Discover how to combine Excel's VLOOKUP and FIND functions to perform powerful, flexible lookups, especially when your lookup value is only part of a cell's content. This guide covers practical examples and common pitfalls.
Excel's VLOOKUP function is a cornerstone for data retrieval, but it typically requires an exact match for the lookup value. What if the value you're searching for is embedded within a larger text string? This is where combining VLOOKUP with the FIND function becomes incredibly powerful. By using FIND, you can locate a substring within a cell and then leverage that information to make VLOOKUP work dynamically, even when exact matches aren't available.
Understanding the Core Problem: Partial Matches with VLOOKUP
VLOOKUP is designed to find a value in the first column of a table array and return a corresponding value from another column in the same row. Its syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The lookup_value must typically match an entry in the first column exactly (or be sorted for approximate matches). When your lookup value is only a portion of the cell's content, a standard VLOOKUP will fail. For instance, if you want to find 'Apple' but the cell contains 'Red Apple Inc.', VLOOKUP alone won't work.
range_lookup argument is crucial. For exact matches (which we'll need here), always set it to FALSE or 0. Omitting it defaults to TRUE, which can lead to incorrect results if your data isn't sorted.Introducing FIND: Locating Substrings
The FIND function helps you locate one text string within another. Its syntax is FIND(find_text, within_text, [start_num]). It returns the starting position of find_text within within_text as a number. If find_text is not found, FIND returns a #VALUE! error. This numerical output is key to our strategy, as we can use it to determine if a substring exists within a cell.
=FIND("Apple", "Red Apple Inc.")
Example of the FIND function returning the starting position of 'Apple'.
This formula would return 5, indicating that 'Apple' starts at the fifth character of 'Red Apple Inc.'. If we searched for 'Banana', it would return a #VALUE! error.
Combining VLOOKUP and FIND for Partial Matches
The trick to combining these functions lies in creating a helper column or using an array formula (for older Excel versions) that evaluates whether the lookup_value is found within each cell of the VLOOKUP's first column. For modern Excel (Excel 365, Excel 2021), dynamic array functions like FILTER or XLOOKUP offer more elegant solutions, but the VLOOKUP/FIND combination remains valuable for compatibility or specific scenarios.
flowchart TD
A[Start]
B["Lookup Value (e.g., 'Apple')"]
C["Table Array (e.g., A:B)"]
D["Iterate through first column of Table Array"]
E{"FIND(Lookup Value, Current Cell) returns a number?"}
F["Yes: Match Found"]
G["No: No Match"]
H["Return corresponding value using VLOOKUP"]
I["End"]
A --> B
B --> D
C --> D
D --> E
E -->|True| F
E -->|False| G
F --> H
G --> D
H --> ILogical flow of combining VLOOKUP and FIND for partial matches.
Practical Example: Finding Product Details by Keyword
Let's say you have a product catalog with full product descriptions in column A and their prices in column B. You want to find the price of a product by searching for a keyword that might be part of the description.
1. Prepare Your Data
Assume your data is in cells A2:B10, where A contains product descriptions and B contains prices. Your lookup keyword is in cell D2.
2. Create a Helper Column (Recommended for Clarity)
In column C (starting C2), enter the formula =ISNUMBER(FIND(D$2,A2)). Drag this formula down to the end of your data. This formula checks if the keyword in D2 is found within the product description in A2. It will return TRUE if found, FALSE otherwise.
3. Perform the VLOOKUP
Now, in the cell where you want the result (e.g., E2), enter the VLOOKUP formula: =VLOOKUP(TRUE,C2:B10,2,FALSE). This VLOOKUP searches for TRUE in your helper column (C) and returns the corresponding value from the second column of your new table array (which is column B, the price).
Helper Column (C2):
=ISNUMBER(FIND(D$2,A2))
Result Cell (E2):
=VLOOKUP(TRUE,C2:B10,2,FALSE)
Formulas for using VLOOKUP with FIND and a helper column.
Advanced: Array Formula Without a Helper Column (Legacy Excel)
For older versions of Excel that don't support dynamic arrays, you can achieve this without a helper column using an array formula. This requires pressing Ctrl+Shift+Enter after typing the formula.
{=VLOOKUP(TRUE,CHOOSE({1,2},ISNUMBER(FIND(D2,A2:A10)),B2:B10),2,FALSE)}
Array formula for VLOOKUP with FIND without a helper column. Remember to press Ctrl+Shift+Enter.
This formula uses CHOOSE to construct an in-memory table where the first column is the ISNUMBER(FIND(...)) result and the second column is your data (prices). VLOOKUP then operates on this virtual table. This method is more complex and less readable than using a helper column or modern dynamic array functions.
Considerations and Alternatives
While VLOOKUP and FIND is a powerful combination, it's essential to be aware of its limitations and consider alternatives, especially with newer Excel versions:
*) or the FILTER function often provides a more straightforward and flexible solution for partial matches. For example, =XLOOKUP("*"&D2&"*",A2:A10,B2:B10,,2) can achieve a similar result.The VLOOKUP/FIND combination is case-sensitive by default (FIND is case-sensitive). If you need a case-insensitive search, you can use SEARCH instead of FIND, as SEARCH is case-insensitive. Also, performance can degrade with very large datasets, as FIND has to evaluate each cell. Always test your formulas on a subset of your data first.