How to find similar names in Excel formula

In case you won't able to access TEXTJOIN function, because of excel version issues You can make your own function for this. Enjoy !!!

Table of Contents Show

Match type information
Exact match
Approximate match
Wildcard match
INDEX and MATCH
Case-sensitive match

You can get a result like this my_solution

As you can see I have used function Lookup_delimited, Three arguments of Lookup_delimited are

Lookup value
Lookup_Range (Note: Don't choose the entire column, be specific with your lookup range)
Results_Range (The values you want to consolidate)

Steps to get this Lookup_delimited,

Step-1> Open your excel file

Step-2> Press Alt+F11 (shortcut for VBA editor)

Step-3> Insert a new module

Step-4> Paste the below code in there

Step-5> Close your VBA editor, save your file as xlsm (Excel macro-enabled workbook)

Step-6> Your worksheet is ready to use the Lookup_delimited functions

Step-7> Use this formula exactly as mentioned above

Try This:

Option Explicit Function Lookup_delimited(L_Value As Variant, L_Range As Range, R_Range As Range) As String Dim Stg As String 'Results storage Dim StgTmp As String 'Cell value storage Dim Rw As Long 'Row Dim Cl As Long 'Column Const StgDelimiter = " | " Stg = StgDelimiter For Rw = 1 To L_Range.Rows.Count For Cl = 1 To L_Range.Columns.Count If L_Range.Cells(Rw, Cl).Value = L_Value Then StgTmp = R_Range.Offset(Rw - 1, Cl - 1).Cells(1, 1).Value If InStr(1, Stg, StgDelimiter & StgTmp & StgDelimiter) = 0 Then Stg = Stg & StgTmp & StgDelimiter End If End If Next Next Lookup_delimited = Stg 'Return to function End Function

The MATCH function is used to determine the position of a value in a range or array. For example, in the screenshot above, the formula in cell E6 is configured to get the position of the value in cell D6. The MATCH function returns 5 because the lookup value ("peach") is in the 5th position in the range B6:B14:

=MATCH(D6,B6:B14,0) // returns 5

The MATCH function can perform exact and approximate matches and supports wildcards (* ?) for partial matches. There are 3 separate match modes (set by the match_type argument), as described below.

Note: the MATCH function will always returns the first match. If you need to return the last match (reverse search) see the XMATCH function. If you want to return all matches, see the FILTER function.

MATCH only supports one-dimensional arrays or ranges, either vertical and horizontal. However, you can use MATCH to locate values in a two-dimensional range or table by giving MATCH the single column (or row) that contains the lookup value. You can even use MATCH twice in a single formula to find a matching row and column at the same time.

Frequently, the MATCH function is combined with the INDEX function in order to retrieve a value at a certain (matched) position. In other words, MATCH figures out the position, and INDEX returns the value at that position. For a detailed explanation, see How to use INDEX and MATCH.

Below are simple examples of how the MATCH function can be used to return the position of values in a range. Further down the page are more advanced examples of how MATCH can be used to solve real-world problems.

Match type information

Match type is optional. If not provided, match type defaults to 1 (exact or next smallest). When match type is 1 or -1, it is sometimes referred to as "approximate match". However, keep in mind that MATCH will find an exact match with all match types, as noted in the table below:

Match type	Behavior	Details
1	Approximate	MATCH finds the largest value less than or equal to lookup value. Lookup array must be sorted in ascending order.
0	Exact	MATCH finds the first value equal to lookup value. Lookup array does not need to be sorted.
-1	Approximate	MATCH finds the smallest value greater than or equal to lookup value. Lookup array must be sorted in descending order.
	Approximate	When match type is omitted, it defaults to 1 with behavior as explained above.

Caution: Be sure to set match type to zero (0) if you need an exact match. The default setting of 1 can cause MATCH to return results that "look normal" but are in fact incorrect. Explicitly providing a value for match_type, is a good reminder of what behavior is expected.

Exact match

When match type is set to zero, MATCH performs an exact match. In the example below, the formula in E3 is:

=MATCH(E2,B3:B11,0) // returns 4

In the formula above, the lookup value comes from cell E2. If the lookup value is hardcoded into the formula, it must be enclosed in double quotes ("") , since it is a text value:

Note: MATCH is not case-sensitive, so "Mars" and "mars" will both return 4.

Approximate match

When match type is set to 1, MATCH will perform an approximate match on values sorted A-Z, finding the largest value less than or equal to the lookup value. In the example shown below, the formula in E3 is:

=MATCH(E2,B3:B11,1) // returns 5

Wildcard match

When match type is set to zero (0), MATCH can perform a match using wildcards. In the example shown below, the formula in E3 is:

=MATCH(E2,B3:B11,0) // returns 6

This is equivalent to:

INDEX and MATCH

The MATCH function is commonly used together with the INDEX function. The resulting formula is called "INDEX and MATCH". For example, in the screen below, INDEX and MATCH are used to return the cost of a code entered in cell F4. The formula in F5 is:

=INDEX(C5:C12,MATCH(F4,B5:B12,0)) // returns 150

In this example, MATCH is set up to perform an exact match. The MATCH function locates the code ABX-075 and returns its position (7) directly to the INDEX function as the row number. The INDEX function then returns the 7th value from the range C5:C12 as a final result. The formula is solved like this:

=INDEX(C5:C12,MATCH(F4,B5:B12,0)) =INDEX(C5:C12,7) =150

See below for more examples of the MATCH function. For more details on INDEX with MATCH, see: How to use INDEX and MATCH.

Case-sensitive match

The MATCH function is not case-sensitive. However, MATCH can be configured to perform a case-sensitive match when combined with the EXACT function in a generic formula like this:

=MATCH(TRUE,EXACT(lookup_value,array),0))

The EXACT function compares every value in array with the lookup_value in a case-sensitive manner. This formula is explained in with an INDEX and MATCH example here.