Để so sánh hai cột theo từng hàng, gõ =B2=C2 cạnh hàng đầu tiên rồi điền xuống: TRUE nghĩa là hai ô khớp nhau, FALSE nghĩa là chúng khác nhau. Để tìm những giá trị của một cột có xuất hiện ở bất kỳ đâu trong cột khác, theo bất kỳ thứ tự nào, hãy dùng =COUNTIF($B$2:$B$8,A2)>0.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Old | New | Same? | Status |
| 2 | Apple | $1.20 | $1.20 | TRUE | Same |
| 3 | Pear | $1.50 | $1.60 | FALSE | Changed |
| 4 | Carrot | $0.80 | $0.80 | TRUE | Same |
| 5 | Bread | $2.40 | $2.20 | FALSE | Changed |
| 6 | Milk | $1.10 | $1.10 | TRUE | Same |
| 7 | Cheese | $4.50 | $4.90 | FALSE | Changed |
D3, D5 và D7 là FALSE, và quy tắc định dạng có điều kiện =$B2<>$C2 tô màu ba hàng đó. Cột E thực hiện cùng phép kiểm tra nhưng hiện chữ thay cho TRUE và FALSE. Đổi C3 thành 1.5 thì hàng 3 chuyển thành Same.
So sánh hai cột bằng IF
=B2=C2 trả về TRUE hoặc FALSE. Bọc nó trong IF để chọn chữ hiển thị: =IF(B2=C2,"Same","Changed"), như ở cột E phía trên. Để để trống các hàng khớp nhau và chỉ đánh dấu điểm khác biệt, dùng =IF(B2<>C2,"Changed",""). Để thấy một con số đã thay đổi bao nhiêu, hãy trừ thay vì so sánh: =C2-B2.
Để đếm số điểm khác biệt mà không cần cột phụ, so sánh hai vùng bên trong SUMPRODUCT: =SUMPRODUCT(--(B2:B7<>C2:C7)) trả về 3 với trang tính phía trên.
Không cần công thức: chọn B2:C7 với B2 là ô đang hoạt động, vào Home > Find & Select > Go To Special, chọn Row differences rồi nhấn OK (trên Windows, Ctrl+\ cũng làm được). Excel sẽ chọn C3, C5 và C7, những ô khác với cột B trong cùng hàng; tô màu nền cho chúng để đánh dấu.
So sánh phân biệt hoa thường bằng EXACT
Phép so sánh = không phân biệt chữ hoa chữ thường: ab12 bằng AB12. Khi chữ hoa thường có ý nghĩa (mã sản phẩm, mật khẩu, mã định danh), hãy dùng EXACT(A2,B2), chỉ trả về TRUE khi hai đoạn văn bản giống hệt nhau đến từng ký tự.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Entered | Equal? | EXACT |
| 2 | AB12 | AB12 | TRUE | TRUE |
| 3 | CD34 | cd34 | TRUE | FALSE |
| 4 | EF56 | EF56 | TRUE | TRUE |
| 5 | GH78 | Gh78 | TRUE | FALSE |
Cột C, dùng phép so sánh =, cho rằng cả bốn đều khớp. EXACT cho biết hàng 3 và hàng 5 khác nhau, vì cd34 và Gh78 dùng chữ thường.
Tìm giá trị có ở cột này nhưng thiếu ở cột kia
Khi hai danh sách không cùng thứ tự, hãy so sánh từng giá trị với toàn bộ cột còn lại. COUNTIF($B$2:$B$8,A2) đếm số lần A2 xuất hiện trong B2:B8, nên >0 nghĩa là "có" còn =0 nghĩa là "thiếu". Các dấu $ giữ cố định vùng tìm kiếm khi công thức được điền xuống.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | January | February | In February? | With MATCH |
| 2 | Ana | Dan | TRUE | TRUE |
| 3 | Ben | Fay | TRUE | TRUE |
| 4 | Cara | Ana | FALSE | FALSE |
| 5 | Dan | Gus | TRUE | TRUE |
| 6 | Eve | Hal | FALSE | FALSE |
| 7 | Fay | Ivy | TRUE | TRUE |
| 8 | Gus | Ben | TRUE | TRUE |
Cara và Eve là FALSE: họ mua hàng vào tháng 1 nhưng không mua vào tháng 2. MATCH cho cùng câu trả lời theo một cách khác: MATCH(A2,$B$2:$B$8,0) trả về vị trí của A2 trong cột B, hoặc #N/A khi không có, và ISNUMBER biến kết quả đó thành TRUE hoặc FALSE. Để kiểm tra chiều ngược lại (khách hàng mới trong tháng 2), đặt cùng công thức cạnh cột B và đổi chỗ hai vùng: =COUNTIF($A$2:$A$8,B2)>0.
So sánh hai danh sách và trả về giá trị tương ứng
Nhiều khi câu hỏi không chỉ là "có hay không" mà là "giá trị bên cạnh có khớp không". Ở đây, các hóa đơn được so với một danh sách thanh toán theo thứ tự khác: XLOOKUP tìm từng hóa đơn trong danh sách thanh toán, trả về số tiền đã trả, và cột D so sánh số đó với số tiền trên hóa đơn.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Invoice | Amount | Paid | Match? | Payment for | Paid | |
| 2 | INV-101 | 120 | 120 | TRUE | INV-103 | 240 | |
| 3 | INV-102 | 85 | Not paid | FALSE | INV-101 | 120 | |
| 4 | INV-103 | 240 | 240 | TRUE | INV-105 | 140 | |
| 5 | INV-104 | 60 | 60 | TRUE | INV-106 | 95 | |
| 6 | INV-105 | 150 | 140 | FALSE | INV-104 | 60 | |
| 7 | INV-106 | 95 | 95 | TRUE |
INV-102 không có khoản thanh toán nào, nên C3 hiện Not paid. INV-105 được trả 140 thay vì 150, nên D6 cũng là FALSE. Đối số cuối cùng của XLOOKUP, "Not paid", thay cho lỗi #N/A mà một giá trị không tìm thấy sẽ gây ra. XLOOKUP cần Excel 2021 hoặc Microsoft 365; trong Excel 2019, dùng =IFERROR(VLOOKUP(A2,$F$2:$G$6,2,FALSE),"Not paid"). Trang XLOOKUP có giải thích các đối số còn lại.
Liệt kê các giá trị thiếu ở cột kia
Thay vì một cột TRUE/FALSE, FILTER có thể trả về các giá trị bị thiếu dưới dạng danh sách. COUNTIF(B2:B8,A2:A8) với đối số thứ hai là một vùng sẽ đếm cùng lúc mọi giá trị của cột A, và FILTER giữ lại những giá trị có số đếm bằng 0.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | January | February | Not in February | |
| 2 | Ana | Dan | ||
| 3 | Ben | Fay | ||
| 4 | Cara | Ana | ||
| 5 | Dan | Gus | ||
| 6 | Eve | Hal | ||
| 7 | Fay | Ivy | ||
| 8 | Gus | Ben |
Đến lượt bạn: Trong D2, liệt kê những khách hàng tháng 1 không có trong danh sách tháng 2.
Đáp án trả ra Cara và Eve. =FILTER(A2:A8,ISNA(MATCH(A2:A8,B2:B8,0))) cũng cho kết quả tương tự. Nếu mọi khách hàng đều quay lại, FILTER trả về #CALC!; hãy thêm đối số thứ ba cho trường hợp đó: =FILTER(A2:A8,COUNTIF(B2:B8,A2:A8)=0,"None"). FILTER cần Excel 2021 hoặc Microsoft 365. Xem FILTER để biết thêm các điều kiện khác.
Tô màu điểm khác biệt giữa hai cột
Các công thức trên cũng dùng được làm quy tắc định dạng có điều kiện. Chọn danh sách đầu tiên, vào Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, rồi nhập công thức cho ô đầu tiên của nó. Ở đây A2:A8 dùng =COUNTIF($B$2:$B$8,A2)=0 còn B2:B8 dùng =COUNTIF($A$2:$A$8,B2)=0: mọi tên chỉ có trong một danh sách đều được tô màu.
| A | B | |
|---|---|---|
| 1 | January | February |
| 2 | Ana | Dan |
| 3 | Ben | Fay |
| 4 | Cara | Ana |
| 5 | Dan | Gus |
| 6 | Eve | Hal |
| 7 | Fay | Ivy |
| 8 | Gus | Ben |
Cara và Eve được tô màu ở cột tháng 1, Hal và Ivy ở cột tháng 2. Với hai cột cần khớp nhau theo từng hàng, quy tắc là =$A2<>$B2 áp dụng cho cả hai cột, như ở trang tính đầu tiên của trang này. Để tô màu những tên có trong cả hai danh sách, hãy dùng >0, như ở trang tô màu giá trị trùng lặp.
Vì sao giá trị giống hệt nhau lại bị coi là khác nhau
Lý do phổ biến nhất là một dấu cách mà bạn không nhìn thấy: Ana có dấu cách ở cuối sẽ không bằng Ana. Dữ liệu dán từ hệ thống khác hoặc từ trang web thường mang theo những dấu cách này. Hãy so sánh các giá trị sau khi đã cắt bỏ khoảng trắng.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Other list | Equal? | Trimmed |
| 2 | Ana | Ana | FALSE | TRUE |
| 3 | Ben | Ben | TRUE | TRUE |
| 4 | Cara | Cara | FALSE | TRUE |
| 5 | Dan | Dan | TRUE | TRUE |
Cột C cho biết hàng 2 và hàng 4 khác nhau; cột D, sau khi TRIM xóa dấu cách ở hai đầu, cho biết cả bốn đều khớp. Lý do thường gặp còn lại là một cột chứa số được lưu dưới dạng văn bản, còn cột kia là số thật: 101 và '101 trông giống nhau, nhưng phép so sánh = của Excel trả về FALSE, và MATCH, VLOOKUP, XLOOKUP đều không tìm thấy giá trị này trong cột kia. COUNTIF là ngoại lệ: nó đọc văn bản trông giống số thành chính con số đó, nên coi chúng là bằng nhau. Một hình tam giác màu xanh ở góc ô đánh dấu phiên bản văn bản; hãy chuyển đổi bằng =VALUE(A2) hoặc =A2*1, hoặc chọn các ô rồi chọn Convert to Number từ biểu tượng cảnh báo.
Câu hỏi thường gặp
Làm sao để so sánh hai cột trong Excel tìm giá trị trùng khớp?
Theo từng hàng: gõ =A2=B2 vào C2 rồi điền xuống; TRUE nghĩa là hai ô khớp nhau. Để kiểm tra mỗi giá trị của cột A có xuất hiện ở bất kỳ đâu trong cột B hay không, dùng =COUNTIF($B$2:$B$8,A2)>0.
Làm sao để so sánh hai cột và trả về giá trị từ cột thứ hai?
Tra cứu giá trị đó: =XLOOKUP(A2,$F$2:$F$7,$G$2:$G$7,"Not found") trả về giá trị tương ứng trong cột G, hoặc Not found. Trong Excel 2019 trở về trước, dùng =IFERROR(VLOOKUP(A2,$F$2:$G$7,2,FALSE),"Not found").
So sánh hai ô trong Excel có phân biệt chữ hoa chữ thường không?
Không. =A2=B2 coi abc và ABC là bằng nhau. Để so sánh có phân biệt hoa thường, dùng =EXACT(A2,B2), chỉ trả về TRUE khi mọi ký tự đều khớp, kể cả chữ hoa chữ thường.
Làm sao để liệt kê các giá trị có ở cột này mà không có ở cột kia?
Trong Excel 365 và 2021, =FILTER(A2:A8,COUNTIF(B2:B8,A2:A8)=0) trả ra mọi giá trị của A2:A8 không xuất hiện trong B2:B8.
Vì sao Excel báo hai giá trị giống hệt nhau là khác nhau?
Thường là do một trong hai có dấu cách thừa hoặc là số được lưu dưới dạng văn bản. So sánh =TRIM(A2)=TRIM(B2) để loại trừ dấu cách, và chuyển số dạng văn bản bằng =VALUE(A2) hoặc =A2*1.
