Get first partial match in a range

An excel formula to get first partial match in a range

Related Functions

Sponsored Link

=MATCH("*"&value&"*",rng,0)

If you need to retrieve the position of the first partial match in a range (for example, you're looking for a match based on a substring), you can use the MATCH function with a wildcard.

For example, if you have fruit names (i.e. apples, lemons, limes, oranges, etc) in the range A1:A100, and you want to get the first occurrence of "apple" by searching typing "app" in cell E1, you can use:

 
=MATCH("*"&E1&"*",A1:A100,0)

This will return the position of the first "apple" that appears in the range. If you want to retrieve the actual value at that position, you can add INDEX like so:

 
=INDEX(A1:A100,MATCH("*"&E1&"*",A1:A100,0))

In this case, MATCH provides the row number to INDEX, which returns that value at that location.

Sponsored Link

0 votes. 0 / 5