Position of first partial match

Microsoft Excel is designed to perform various functions. It is equipped with an array of functions, formulae, and miscellaneous options, which enable a user to estimate the position of first partial match in a given row or column. The following tutorial and pictorial discusses how to use ExcelChat for finding out the position of the first partial match on Excel.

Determining the position of first partial match in Excel Sheet:

Figure 1. Example of the text to be tested for finding a position of the first partial match

Formula:

=MATCH("*text*",rng,0)

Explanation:

MATCH is one of the most commonly used Excel functions. It is used for locating or determining the position of the first partial match. Alternatively, it is used for locating the exact position for a required value mentioned in the table, row, or column in an excel sheet. The MATCH function is used for approximate matching. It also supports wildcards (*?) for finding the partial matches in Excel table. Next, it uses the INDEX function in combination with the MATCH function for finding out the value calculated by MATCH.

An Excel user can employ the use of MATCH function for determining the corresponding position of a text or number in an array. The MATCH function is equipped with miscellaneous matching modes, which makes it a suitable and versatile function in comparison to its counterpart lookup functions. When the MATCH function is used in congruence with INDEX function, it is then configured to find the value that matches the position array in Excel sheet.

How does the formula work?

In the example (screenshot) attached below, you can see that the active formula uses the following for making calculations:

=MATCH(“*”&E7&”*”,B6:B11, 0)

A user would be required to specify a word, which a MATCH would return to the user. Using the given example, let’s have a closer look at the workability of the formula. Excel ‘scans’ the range specified by a user then, it informs the user on the position of the word in the given text. For instance, the following example uses six phrases, which starts from B6 and it terminates at B11. The ‘range’ for the text would be condensed to B6:B11 for making the workability of the formula more effective.

Next, it would require a user to indicate two separate columns for ‘match’ and ‘result’. It should specify a word that he is looking for in a column next to match. Taking the following example into consideration, the match is mentioned in D7, whereas, the word to specified i.e., earth, is mentioned in E7. Similarly, result in mentioned in D8, whereas the value would be returned to E8.

Figure 2. Example of the returned value using the MATCH function

The following figure highlights the cells and arrays, which was used for determining the position of the first partial match. As you can see, it uses E7 for specifying ‘earth’, and it uses a combination of ‘text, which ranges from B6:B11. Once the user hits a press button, the value returned to it would be (6). When a user scans through the given list again, it will find the word ‘earth’ mentioned in B11, which is the 6th row in the selection.

Figure 3. Example of the formula set up of MATCH function for finding the position of first partial match in a cell

Similarly, a user can shift the values manually for finding out the position of first partial match in the Excel Sheet. Once the values are entered, and a user enters press, the returned value would be (4) for the word ‘optimization’.

For instance, have a look at the following figure:

Figure 4. Example of changing values of the formula for finding the assorted position of a word

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar