< Go Back

First match between two ranges

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:

COUNTIF(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”.

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