In excel, in the "formula" ribbon, in the "formula auditing" section, there is a tool called "Evaluate Formula. It will allow you to step through the formula in a cell and see how it is calculating.
WHAT IS WRONG WITH THE FORMULA:
Looking at the formula you are using, you are almost always going to get an error because you have a string multiplied by another string as your search term.
The syntax for search is:
=SEARCH(FIND TEXT, WITHIN TEXT, OPTIONAL START POINT)
Looking at your formula:
Your search term is:
For starters, too many brackets. It could be simplified to:
So you are telling excel to look for the first 2 letters on the left side of D6 multiplied by the last letter of D6. Meaning:
Try doing the math on that yourself. IF you have two numbers on the left of the string, your math will work, and the string will automatically convert to numbers for the math operation.
When you have letters, an error is generated and due to that error, you are getting ISERROR resulting in true. Therefor you are always getting MATCH with the exception of the case of D6 starting with 2 numbers in the string and D6 ending in a number.
HOW TO IMPROVE WHAT YOU WANT TO DO
Basically you are looking for two condition to be true. You want the start of both to be equal and you want to the end of both to be equal. Sounds like an AND would work here. IF both check are TRUE you want a MATCH result to be displayed. That concept could look like:
=IF(AND(CONDITION1, CONDITION2), "MATCH", "NO MATCH")
Therefore we will look at breaking down your formula see how to make it work:
So lets break this down. You will notice that in the AND section there are three condition checks.
This is looking for the first two characters from D6 in the combination of H6 with D6 added to the end of it and seeing if the result is 1. The result of one indicate that the first two characters were found in the first position of D6. The reason D6 is added to the text to search in is so that if the first two letters are not found in H6, they will be found in D6 which avoids a potential error on the search. Now if H6 happens to be blank, it means your could wind up with a true result. This "False positive" is dealt with the third condition in the AND function.
Similar to Check 1, this time the last character of D6 is being searched for in H6 with D6 tacked on the end to avoid the same potential error as check 1, and again the same "False Positive" possibility will exist which is caught by the third condition check. The variation this time is we start looking at the last character in H6 instead of the default first character. this is done by the addition of:
And instead of checking to see if the result of the search is returning 1 for the first position, the search results are check to see if they are equal to the last position of H6. If you were looking at the last two characters from D6, you would have to change
LEN(H6)-1 to start looking at the second last chater and to see if your string was found at the second last character from the end.
This is a quick check to see if H6 is blank. If H6 is blank, there is nothing to match with and therefore "No Match" would be the result.
Only when all the checks are TRUE, will the IF function return "True". Otherwise IF will return FALSE
THERE IS STILL ROOM FOR IMPROVEMENT FOR THE GENERAL CASE OF WHAT YOU ARE TRYING TO DO!
The above will check for first two characters matching in both columns and last character match in both columns.