Kiểm tra tồn tại giá trị trong Excel và Google Sheets: Hướng dẫn cách thực hiện

Rate this post

Trong bài viết này, chúng ta sẽ khám phá các công thức được sử dụng phổ biến nhất trong Excel và Google Trang tính để kiểm tra xem một giá trị có tồn tại trong một cột, hàng hay trong một phạm vi ô hay không.

Nếu giá trị tồn tại trong phạm vi trong Excel

Để kiểm tra xem một giá trị nhất định có tồn tại trong một phạm vi ô hay không và nếu có thì trả về một số văn bản và nếu không thì trả về một văn bản khác, bạn có thể sử dụng kết hợp các hàm IF và COUNTIF.

IF(COUNTIF(range, value)>0, “Yes”, “No”)

Trong công thức này, COUNTIF đếm số lần xuất hiện của một giá trị nhất định trong một phạm vi. Nếu số lượng lớn hơn 0, câu lệnh IF trả về “Có”. Nếu không tìm thấy giá trị trong phạm vi, hàm COUNTIF mang về 0 và IF cho kết quả là “Không”.

Công thức chung này hoạt động tốt trong mọi tình huống có thể xảy ra.

Xem thêm:  Ngôi nhà bí ẩn trong rừng làm say lòng người đón cháy.

Kiểm tra xem giá trị có tồn tại trong một cột không

Để kiểm tra xem một giá trị trong C3 có xuất hiện trong cột A hay không (chính xác hơn là trong phạm vi A3:A20), bạn có thể sử dụng công thức sau:

=IF(COUNTIF($A$3:$A$20, C3)>0, “Yes”, “No”)

Hãy lưu ý rằng khóa tham chiếu phạm vi ($A$3:$A$20) bằng cách sử dụng dấu $ để công thức sao chép chính xác trong các ô D4:D7:

Kiểm tra xem giá trị có tồn tại trong một cột không

Kiểm tra xem giá trị có tồn tại trong một hàng không

Để tìm hiểu xem một giá trị trong B5 có xuất hiện trong hàng B2:I2 hay không, công thức có dạng sau:

=IF(COUNTIF($B$2:$I$2, B5)>0, “Yes”, “No”)

Kiểm tra xem giá trị có tồn tại trong một hàng không

Kiểm tra xem giá trị có tồn tại trong một phạm vi không

Để kiểm tra xem một giá trị cụ thể có xuất hiện trong phạm vi 2D hay không, hãy cung cấp tham chiếu phạm vi tương ứng cho đối số đầu tiên của COUNTIF. Ví dụ:

=IF(COUNTIF($A$3:$B$11, D3)>0, “Yes”, “No”)

Kiểm tra xem giá trị có tồn tại trong một phạm vi không

Thêm công thức để tìm giá trị tra cứu trong một phạm vi

Công thức IF + COUNTIF là cách dễ nhất nhưng không phải là cách duy nhất để tìm xem một giá trị nhất định có xuất hiện trong một phạm vi hay không. Dưới đây là một số giải pháp thay thế.

ISNUMBER và MATCH

Một cách khác để kiểm tra xem một giá trị có xuất hiện trong danh sách hay không là sử dụng MATCH cùng với hàm ISNUMBER. Ví dụ:

=IF(ISNUMBER(MATCH(C3, $A$3:$A$20, 0)), “Yes”, “No”)

Hàm MATCH trả về vị trí của giá trị tra cứu (C3) trong phạm vi (A3:A20) dưới dạng số; lỗi #N/A nếu không tìm thấy kết quả trùng khớp. Hàm ISNUMBER chuyển đổi một số thành TRUE và lỗi thành FALSE. Việc gói cấu trúc vào IF sẽ cho kết quả cuối cùng là “Có” hoặc “Không”.

Xem thêm:  Báo giá xây nhà trọn gói Long An chi tiết nhất hiện nay

ISNA và VLOOKUP

Một giải pháp phức tạp hơn một chút nhưng vẫn hiệu quả là sử dụng các hàm IF, ISNA và VLOOKUP trong một công thức:

=IF(ISNA(VLOOKUP(C3, $A$3:$A$20, 1, FALSE)), “No”, “Yes”)

Ở đây, hàm VLOOKUP tìm kiếm giá trị tra cứu là mảng được chỉ định và trả về kết quả khớp chính xác (FALSE) từ cùng một cột (1). Nếu không tìm thấy kết quả trùng khớp, lỗi #N/A sẽ được trả về. (Trong Excel 365 và Excel 2021, bạn có thể sử dụng XLOOKUP thay vì VLOOKUP.) Hàm ISNA kiểm tra kết quả để tìm lỗi #N/A và trả về TRUE nếu dương, FALSE nếu không. Cuối cùng, hàm IF trả về “Không” cho TRUE (không khớp) và “Có” cho FALSE (khớp).

ISNA và VLOOKUP

ISNA và MATCH

Giải pháp này rất giống với giải pháp trước ngoại trừ việc bạn sử dụng MATCH thay vì VLOOKUP để kiểm tra phạm vi tìm kết quả khớp:

=IF(ISNA(MATCH(C3, $A$3:$A$20, 0)), “No”, “Yes”)

Nếu giá trị tồn tại trong phạm vi trong Google Trang tính

Trong Google Trang tính, bạn có thể kiểm tra xem một giá trị có tồn tại trong một phạm vi hay không bằng cách sử dụng chính xác các công thức mà chúng tôi đã sử dụng trong Excel.

Ví dụ: để tìm xem giá trị trong D3 có xuất hiện trong phạm vi A3:B11 hay không, công thức trong E4 là:

=IF(COUNTIF($A$3:$B$11, D3)>0, “Yes”, “No”)

Nếu giá trị tồn tại trong phạm vi trong Google Trang tính

Kiểm tra xem giá trị có tồn tại trong phạm vi không – khớp một phần

Để xem liệu bất kỳ ô nào trong một phạm vi có chứa chuỗi con nhất định hay không, hãy đặt ký tự đại diện (dấu hoa thị) ở cả hai bên của giá trị tra cứu để COUNTIF tìm kiếm nó ở bất kỳ đâu trong ô:

Xem thêm:  "Biến đổi chất lượng cuộc sống từ việc cải tạo ngôi nhà"

IF(COUNTIF(range, “*value*”)>0, “Yes”, “No”)

Ví dụ: công thức dưới đây sẽ kiểm tra xem có ô nào trong phạm vi A3:B11 chứa từ “apple” hay không:

=IF(COUNTIF(A3:B11, “*apple*”)>0, “Yes”, “No”)

Nếu bạn đang đề cập đến một ô nhất định thay vì một giá trị được mã hóa cứng, hãy nối các dấu hoa thị trước và sau tham chiếu ô như thế này:

=IF(COUNTIF($A$3:$B$11,”*”&D3&”*”)>0, “Yes”, “No”)

Kiểm tra xem giá trị có tồn tại trong phạm vi không - khớp một phần

Làm nổi bật các giá trị tồn tại trong phạm vi

Một cách trực quan hơn để xác định các giá trị tồn tại trong một phạm vi nhất định có thể là làm nổi bật chúng bằng Định dạng có điều kiện của Excel. Các bước là:

1. Chọn các ô có giá trị bạn muốn tìm trong một phạm vi (C3:C7).

2. Trên tab Home, trong nhóm Styles, bấm vào Conditional Formatting > New Rule.

3. Trong cửa sổ New Format Rule, chọn Use Formula để xác định ô nào cần định dạng.

4. Nhập một trong các công thức này vào hộp Format value trong đó công thức này là đúng:

=MATCH(C3, $A$3:$A$20, 0)

=COUNTIF($A$3:$A$20, C3)>0


Trong đó C3 là ô trên cùng có các giá trị đích và $A$3:$A$20 là phạm vi cần kiểm tra.

5. Nhấp vào nút Format… và định cấu hình định dạng mong muốn.

6. Nhấn OK hai lần để đóng cả hai cửa sổ hộp thoại.

Xong! Tất cả các giá trị tồn tại trong phạm vi (khớp chính xác) đều được đánh dấu:

Làm nổi bật các giá trị tồn tại trong phạm vi

Để làm nổi bật các giá trị khớp một phần , hãy sửa đổi công thức như hiển thị bên dưới – nối dấu hoa thị ở cả hai bên của tham chiếu giá trị tra cứu:

=MATCH(“*”&C3&”*”, $A$3:$A$20, 0)

=COUNTIF($A$3:$A$20, “*”&C3&”*”)>0

Đánh dấu các giá trị khớp một phần

Bây giờ bạn đã được trang bị kiến ​​thức và kỹ năng để kiểm tra xem một giá trị có tồn tại trong một cột, hàng hoặc phạm vi một cách tự tin và dễ dàng hay không.