**HOW TO FIND THE FIRST MATCH BETWEEN TWO RANGES**

As it is with using Excel to solve several different analytical problems, there are many ways to find the first match between two ranges using Excel. In this post, we’ll be considering the formula technique of finding the first match between two ranges.

The general formula for finding the first match between two ranges

`=INDEX(array2,MATCH(TRUE,COUNTIF(array1,array2)>0,0))`

***Array1=range1**

** Array2=range2**

**How the formula works**

*Figure 1. Find the First Match Between Two Ranges Example*

This formula is dependent on “INDEX”, “MATCH” & “COUNTIF” function. From the example above, the formula in F6 is:

`=INDEX(D6:D9,MATCH(TRUE,COUNTIF(C6:C9, D6:D9)>0,0))`

Where “array1””and “array2” are cells C6:C9 and D6:D9 respectively.

The key part of the formula above is “MATCH””and “INDEX”. The INDEX function retrieves an item from range2 that is first to be found in range1. It requires an index value (row number) which is produced using the MATCH function. This is set to match the value “TRUE” as shown below:

**MATCH(TRUE,COUNTIF(array1,array2)>0,0)**

The lookup array is generated with COUNTIF in this part of the general formula:

**COUNTI****F****(array1,array2)>0**

The “COUNTIF” returns multiple results of items in range2 (since range2 has multiple items) that appear in range1 as below:

**{0; 0; 1}**

**{FALSE; FALSE; TRUE}**

The “>0” is used to coerce all results either to TRUE or FALSE:

Thereafter, MATCH does its thing and returns the location of the first TRUE (if any) that comes up. For this one, it is position 1 in range2. Lastly, INDEX returns the item at that position, which is “Arizona”.

## Leave a Comment