trying to see whats in one list thats not in another with excel
You can rapidly compare 2 lists in Excel for matches using the MATCH function, IF function, or highlighting row difference.
Manually searching for the difference between two lists can both be time-consuming and prone to errors. Yous will finish upwardly wasting a lot of time!
In that location are various inbuilt functions and features in Excel that tin can practice this chore of Excel compare two lists easily. Let'south expect for various options that you can follow:
- Highlight Row Difference
- Compare Row using IF function
- Compare List using Match Role
Allow's look at each method one-past-ane!
Don't forget to download this Excel Workbook to follow along and compare two lists in Excel for matches:
Highlight Row Deviation
Y'all can easily highlight differences in value in each row using the conditional formatting feature in Excel. It volition provide you lot with an idea of how many lines in the columns differ in values.
In the data below, you have two lists in Column A and Column B respectively.
Follow the steps beneath to highlight row divergence:
STEP 1: Select both the columns.
STEP two: Become to Home > Notice & Select > Become To Special or merely press keys Ctrl + G and Select Special to open up the Become To Special dialog box.
STEP three: Select Row Difference and Click OK.
And, Voila!
All the values in Stock List ii that do not lucifer with the corresponding value in Stock List 1 have been highlighted.
STEP 4: Y'all can mark these cells with color too. Go to Dwelling house > Font Color > Select Red.
This will permanently highlight the cells in ruddy font color for future reference.
Compare Row using IF function
You can use the IF Function to compare two lists in Excel for matches in the aforementioned row. If Part will render the value True if the values friction match and FALSE if they don't.
You tin can even add custom text to display the word "Match" when a criterion is met and "Not a Match" when information technology's not met.
Let'due south see how we can compare two lists in Excel for matches using IF Part:
Pace one: We need to enter the IF function in a bare cell.
=IF(
Stride 2: Enter the first argument for the IF role – Logical_Test
What is your condition?
The value in prison cell D12 is equal to the value in cell C12.
=IF(D12=C12,
STEP 3: Enter the second argument for the IF function – Value_if_true
What value should be displayed if the condition is true?
The text displayed should be Friction matchif D12 is equal to C12.
=IF( D12=C12,"Match",
STEP 4: Enter the third argument for the IF function – Value_if_false
What value should be displayed if the condition is fake?
The text displayed should be Not a Matchif D12 is not equal to C12.
=IF( D12=C12,"Friction match","Not a Match")
Step 5: Utilise the same formula to the balance of the cells by dragging the lower correct corner downward.
Compare List using Match Role
Before we sympathise how to compare ii lists in Excel for matches, let's first go through the nuts of what the MATCH function Excel does.
What does it do?
It returns the position of an item in a range.
Formula breakdown:
=MATCH(lookup_value,lookup_array,[match_type])
What it means:
=Match(lookup this value,from this listing or range of cells,return me the Exact Match).
I am certain that yous have come beyond many occasions where yous accept two lists of information and want to know if a specific item in List1 exists in List2 .
Well, I take!
With the Lucifer function, you can verify if a cell´s item in List1 exists in List2 .
The function will return the row position of that item in List2 hence confirming that it exists. If you get a #North/A it means that the cell´s item does non exist in List2.
You can then go alee and filter your List1 with either the values returned or the #Northward/Every bit.
Here are our 2 Lists:
Lookout man How to Match Two Lists in Excel for Matches on YouTube and give information technology a thumbs-upwardly!
STEP 1: Nosotros need to enter the Match function in a blank cell:
=Friction match(
STEP ii: Enter the beginning statement for the MATCH office – Lookup_value
What is the value yous want to bank check?
Select the cell containing the List1 value, as this is what we want to bank check confronting List2.
=MATCH(C12,
Footstep 3: Enter the second argument for the Match part – Lookup_array
What is the list y'all desire to cheque against?
Select the unabridged List2.
And ensure to press F4 to make it an absolute reference.
=Lucifer(C12, list2!$C$12:$C:21,
STEP 4: Enter the third statement for the Friction match role – Match_type
How specific is your matching? We want an exact match so place in 0.
=Lucifer(C12, list2!$C$12:$C:21, 0)
STEP 5: Use the same formula to the rest of the cells by dragging the lower right corner down.
You now have all of the results!
You lot can see which row numbers the items exist in List2. For example, Mon45657 in List1 exists in List2 Row 9! If information technology does not exist in List2, then #Northward/A is displayed.
Using either of the three ways mentioned in this commodity, yous can easily compare two lists in Excel for matches!
HELPFUL Resources:
Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:
You can larn more about how to use Excel by viewing our FREE Excel webinar grooming onFormulas, Pivot Tables, Power Query, andMacros & VBA!
Source: https://www.myexcelonline.com/blog/match-two-lists-with-the-match-function-in-excel/
0 Response to "trying to see whats in one list thats not in another with excel"
Post a Comment