Download working file 👈👨💻
Here I am going to show you how we can get the lookup result for a lookup value which has partial string in the lookup value and full string in the Table array.
For example in the figure below, we can see that the Lookup value is not matching with the texts mentioned in the table array. In these circumstances we will get an error while applying a VLOOKUP formula.
1st Alternative:
Wildcard Method:
In this method, we just Prefix and Suffix wildcard "*" (asterisk symbol) with the lookup value using "&" operator.
Enter formula: =VLOOKUP("*"&E4&"*",$A$1:$C$9,2,0)
1st Alternative method👇
2nd Alternative:
Using Filter formula
In this method we will use Filter formula to get the result. Now Filter formula doesn't support wildcard, so we will use "ISNUMBER" and "SEARCH" formula in place of wildcard.
Enter formula: =FILTER(B1:B9,ISNUMBER(SEARCH(E4,A1:A9)))
2nd Alternative method👇
