How to compare values in two rows and find the matches in Google Sheets?

I’ll use Hlookup (horizontal lookup) for this. How? That’s what I’m going to elaborate on in this article.

Let’s compare the dates in two rows and find the matches using two different formulas in Google Sheets.

Don’t worry if you have different types of values to match in rows!

You can use text strings as well as numbers instead of date values. The formula will work even in mixed data rows.

In my personal opinion, Hlookup is not much popular in comparison with Vlookup. This is because we tend to format our data in columns, not in rows.

Anyhow, to compare two rows, I am going to use Hlookup. Hlookup will return the matching values, not TRUE/FALSE or YES/NO.

Of course, for the said purpose, I am not denying the fact that we can also use some other functions like Match, Regexmatch, Countif, Vlookup combined with Transpose, etc.

If you don’t want the matching values after comparison, instead you want TRUE/FALSE or tick boxes you can use my Match Formula. That formula also included in this article.

Sample Data:

I want to deploy one store item (Chain Block) to two different project sites. I want to check whether the dates clash.

How to compare the dates in row # 2 with the dates in row #3 or vice versa for finding matches?

Compare Two Rows and Return Matching Values in Google Sheets (Hlookup)

Let’s first compare the dates in row three with the dates in row two.

In cell B5, insert the following Hlookup formula.

=ArrayFormula(ifna(hlookup(B3:3,B2:2,1,0)))

This Hlookup will search across row # 2 for all the dates in row # 3 and will return the matching dates.

The following formula in cell B6 is for comparing the dates in row two with the dates in row three.

=ArrayFormula(ifna(hlookup(B2:2,B3:3,1,0)))

You can use either of the formulas to find the matching (conflicting) dates from two rows.

Note:

The output rows may contain date numbers instead of formatted date.

If so, select the rows 5 and 6 and go to the Format menu and select ‘Date’ from the menu item ‘Number’. It’s like Format > Number > Date.

Compare Two Rows and Return TRUE/FALSE in Google Sheets (Match)

To compare two rows in Google Sheets, we can use the Match function too.

We can very easily transform an Hlookup formula to a Match formula.

First, see the syntax of both Hlookup and Match functions.

HLOOKUP(search_key, range, index, [is_sorted])
MATCH(search_key, range, [search_type])

In these, the argument ‘is_sorted’ and ‘search_type’ are (refers to) almost the same, i.e. whether the values in the rows are sorted or not.

The value 0 as the is_sorted or search_type means the values are unsorted. Further Hlookup has one extra argument, which is ‘index’.

So you can convert/transform an Hlookup formula to Match formula by replacing the function name and then by removing the ‘index’ value from a formula.

See how I have rewritten the above two Hlookup formulas.

In cell B5;

=ArrayFormula(ifna(MATCH(B3:3,B2:2,0)))

In cell B6;

=ArrayFormula(ifna(MATCH(B2:2,B3:3,0)))

Note:

Format back the values in rows 5 and 6 to numbers. Because the above two formulas will return certain numbers (relative position of matching column number).

Using two logical IF statements, convert the numbers (output of Match formulas) to TRUE and blanks to FALSE.

=ArrayFormula(if(ifna(MATCH(B3:F3,B2:F2,0))>0,TRUE,FALSE))
=ArrayFormula(if(ifna(MATCH(B2:F2,B3:F3,0))>0,TRUE,FALSE))

This time I have used a closed range to avoid the above IF statement returns FALSE in unused cells (range).

Convert Matches to Tick Boxes

So you could now easily match dates, texts, or numbers in two rows and either return the matching value or Boolean values TRUE/FALSE.

If you opt for the second option (Match based formulas) to compare two rows and find matches, there is one advantage.

What’s that?

You can place a tick mark against the matches as below.

1. Select the array B5:F6.
2. Go to Insert menu and click on the Tick box.

That’s it. Enjoy!

Comparing Two Tabs, Columns, Lists, Etc. – Additional Resources

1. How to Compare Two Sheets in Google Sheets for Mismatch.
2. How to Compare Two Columns for Matching Values in Google Sheets.
3. Google Sheets: How to Compare Two Tables and Remove Duplicates.
4. Google Sheets: Compare Two Lists and Extract the Differences.
5. Compare Two Sets of Multi-Column Data for Differences in Google Sheets.
6. Compare Two Google Sheets Cell by Cell and Highlight.