Home » Posts

First match between two ranges

To retrieve the first match in two ranges of values, you can use a formula based on the INDEX, MATCH, and COUNTIF functions. In the example shown, the formula in G5 is: =INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0)) where "range1" is the named range B5:B8, "range2" is the named range D5:D7.

April 14, 2025 · 1 min · 48 words · Casey Odonnell

The core of this formula is INDEX and MATCH.

Finally, INDEX returns the value at that position, “Red”.

it’s possible for you to use INDEX to retrieve individual values, or entire rows and columns.

Excel formula: Get first match cell contains