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.
Excel MVP "Mike Girvin" has helped me understand these unique set of array formulas through his You tube channel "Excel is Fun"
1st Alternative:
Using Index-Match
In this method we will use Index-Match formula to get the result. Additionally, we will use "ISNUMBER" and "SEARCH" formula to identify the position of our lookup value in the Table array
Enter formula:
=INDEX(B2:B9,MATCH(TRUE,ISNUMBER(SEARCH(A2:A9,E3)),0))
1st Alternative method👇
2nd Alternative:
Using XLOOKUP
In this method we will use XLOOKUP formula to get the result. Just like in previous alternative, we will use "ISNUMBER" and "SEARCH" formula to identify the position of our lookup value in the Table array
Enter formula:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(A2:A9,E3)),B2:B9)
3rd Alternative:
Using FILTER
In this method we will use FILTER formula to get the result. Just like in previous two alternatives, we will use "ISNUMBER" and "SEARCH" formula to identify the position of our lookup value in the Table array
Enter formula:
=FILTER(B2:B9,ISNUMBER(SEARCH(A2:A9,E3)))
3rd Alternative method👇
