Indexi1m1000match1m1m1000b1finda2i1i1000102 indexi1m1000match1m1m1000b1lefti1i1000lena2a202 You could also still use a wildcard if you re-cast the formula using an IF statement-. Please keep in mind that wildcards only work with text not numbers. The reason it fails is that Excel doesnt work well when using wildcards directly after an equals sign in a formula.
Once again use wildcard characters in the lookup value argument.
The wildcard symbol needs to be added to the lookup value as a literal string and that is why the double quotes are necessary. This is because INDEX and MATCH is incredibly flexible you can do horizontal and vertical lookups 2-way lookups left lookups case-sensitive lookups and even lookups based on multiple criteria. And then INDEX use that number to figure out the position of the cell from the value column. Notice that we concatenate or join the wildcard to the end of our lookup value by typing the cell reference of our lookup value followed by the ampersand and then the asterisk in double quotes.