Compare Two Rows and Find Matches in Google Sheets

Do you want to uncover the power of comparing values in two rows and finding matches in Google Sheets? Well, you’re in luck! In this article, I’ll reveal a secret trick using the Hlookup function that will make it a breeze. So let’s dive right in!

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

While Vlookup steals the spotlight, Hlookup, which stands for horizontal lookup, is the unsung hero when it comes to comparing two rows and finding matches. Especially when dealing with row-based data, Hlookup is your go-to tool.

To compare the dates in row #2 with the dates in row #3, all you need to do is insert this Hlookup formula in cell B5:

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

This formula will scour row #2 for the matching dates in row #3 and only return the matches. If you want to compare the dates in row #3 with the dates in row #2, use this formula in cell B6:

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

With either of these formulas, you can easily uncover conflicting or matching dates from two rows. Just keep in mind that the output may appear as date numbers instead of formatted dates. To format them properly, select rows 5 and 6, go to the Format menu, and choose ‘Date’ under the ‘Number’ section.

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

If you prefer TRUE/FALSE values instead of the matching dates, you can make a simple transformation from Hlookup to Match. Both functions have a similar syntax:

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

To convert the Hlookup formulas to Match formulas, replace the function name and remove the ‘index’ value. Here are the transformed formulas:

In cell B5:

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

In cell B6:

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

Don’t forget to format the values in rows 5 and 6 back to numbers, as the Match formulas will return relative position numbers of the matching columns. You can then use two logical IF statements to convert these numbers 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’ve used a closed range to avoid the IF statement returning FALSE in unused cells.

Convert Matches to Tick Boxes

Now here’s the juiciest secret! With the Match-based formulas, not only can you match dates, texts, or numbers in two rows and return the matching value or Boolean values (TRUE/FALSE), but you can also place a satisfying tick mark against the matches!

To achieve this, follow these simple steps:

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

Voila! Your matches are now adorned with tick marks, making it easier than ever to spot them.

So go ahead and embrace the power of Google Sheets to compare and find matches between two rows. But before you go, remember to visit Crawlan.com for more insightful tips and tricks!

Additional Resources

Looking to compare more than just two rows? Check out these 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

Now armed with all the necessary tools, you can compare, match, and analyze data in Google Sheets like a true pro. Happy comparing!