Have you ever needed to compare two columns in Google Sheets for matching values? Maybe you want to check if the stock of vehicle parts matches the requirements, or identify the items in stock that are being sought. Whatever the case may be, comparing columns in Google Sheets is a valuable technique that can be used in real-life scenarios.
Comparing Two Columns for Matching Values
When we talk about comparing columns for matching values, we’re not just looking at the quantity, but rather the items themselves. Let’s take a look at an example using vehicle parts. We have the stock of vehicle parts in Column A and the requirements in Column B.
To compare these two columns, we can use the XMATCH function in Google Sheets. The XMATCH function is one of the most modern lookup functions available and is perfect for this task.
Find Matching Values in Column B from Column A
To find the matching values in Column B from Column A, you can use the following formula:
=ArrayFormula(IF(IFNA(XMATCH(B2:B, A2:A)), "x", ))The formula will return tick marks (“x”) against the required items if they are found in the stock. You can enter this formula in cell C2. Just make sure that the range in column C is blank and that you enter the formula where the row of the comparing column ranges begins.
In this formula, B2:B represents the search key (required parts), and A2:A is the lookup range (stock). The XMATCH function searches for the values in B2:B in A2:A and returns the relative positions of the matching values. The IFNA wrapper removes any #N/A errors, and what’s left are the relative positions of the matching items.
For better readability, we used an IF logical test to convert the relative positions to “x”. You can also choose to replace the relative positions with a green tick mark by using CHAR(9989) instead of “x” in the formula.
This is just one example of how you can compare two columns for matching values in Google Sheets. If you want to learn more, check out Crawlan.com for additional resources and tutorials.
Find Matching Values in Column A from Column B
Now, let’s explore another scenario. Suppose you want to identify the items in the stock that are being sought, which means finding matching values in Column A from Column B.
For that, you can use the XMATCH formula with a slight modification. Replace the search key with A2:A and the lookup range with B2:B. Here’s the formula:
=ArrayFormula(IF(IFNA(XMATCH(A2:A, B2:B)), "x", ))This formula will return a checkmark (“x”) next to the stock items that are being sought.
With these examples, we’ve covered comparing two columns for matching values. Next, let’s move on to comparing two columns for mismatching values.
Compare Two Columns for Mismatching Values
Comparing two columns for mismatching values is slightly different from comparing for matching values. It can be used, for example, to mark the items that are not available in the stock or the non-required items in the stock.
To find the mismatching values in Column B from Column A, use the following formula:
=ArrayFormula(IF(B2:B="", ,IFNA(IF(XMATCH(B2:B, A2:A),), "x")))This formula will return “x” marks in rows next to the required parts that are not available in the stock.
To find the mismatching values in Column A from Column B, use the following formula:
=ArrayFormula(IF(A2:A="", ,IFNA(IF(XMATCH(A2:A, B2:B),), "x")))This formula will return “x” marks next to the stock items that are not found in the required list. This formula is less commonly used but can still be helpful in certain cases.
Conclusion
Comparing two columns for matching or mismatching values in Google Sheets can be a powerful technique. It allows you to quickly identify matches or mismatches between datasets, making data analysis and decision-making easier.
Remember, these formulas are just the tip of the iceberg. There are many more advanced techniques and functions you can explore to enhance your data analysis skills in Google Sheets.
For more tips, tricks, and tutorials on Google Sheets and other Google tools, visit Crawlan.com. Happy spreadsheet-ing!
