Có thể bạn quan tâm: Kiểm Tra Giá Trị True/false Trong Excel: Hướng Dẫn Toàn Diện Và Ứng Dụng Thực Tiễn

Mục lục bài viết

Mở Đầu

Trong công việc với Excel, việc kiểm tra xem một giá trị có tồn tại trong một dải ô (range) hay không là một trong những thao tác cơ bản nhưng lại vô cùng quan trọng. Từ việc lọc dữ liệu, hợp nhất bảng, đến việc xây dựng các báo cáo phức tạp, khả năng xác định nhanh chóng một giá trị trong một tập hợp dữ liệu sẽ giúp bạn tiết kiệm thời gian và giảm thiểu lỗi. Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, từ các công thức đơn giản nhất cho đến các giải pháp nâng cao, giúp bạn thành thạo việc “excel check if value exists in range”.

Có thể bạn quan tâm: Kiểm Tra Và Xử Lý Dòng Trùng Lặp Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Tổng Quan Về Các Phương Pháp Kiểm Tra Giá Trị

Trước khi đi sâu vào chi tiết, chúng ta hãy liệt kê các phương pháp phổ biến nhất để kiểm tra giá trị trong Excel:

Phương pháp	Hàm chính	Ưu điểm	Nhược điểm
COUNTIF	`COUNTIF(range, criteria)`	Đơn giản, nhanh, hỗ trợ wildcard	Không trả về vị trí, chỉ đếm
MATCH	`MATCH(lookup_value, lookup_array, match_type)`	Trả về vị trí (số thứ tự)	Không trả về giá trị, chỉ hoạt động trên một cột/hàng
VLOOKUP / HLOOKUP	`VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)`	Tìm và trả về giá trị liên quan	Giới hạn cột tìm, không trả về vị trí
XLOOKUP (Excel 365/2026)	`XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)`	Linh hoạt, trả về giá trị và vị trí	Cần phiên bản mới
IF + ISNUMBER + MATCH	`IF(ISNUMBER(MATCH(value, range, 0)), "Exists", "Not Exists")`	Kết hợp logic, hiển thị thông báo	Cú pháp phức tạp hơn
Conditional Formatting	Sử dụng công thức `=COUNTIF($A$1:$A$100, B1)>0`	Đánh dấu trực quan	Chỉ hiển thị, không trả về giá trị trong ô

Mỗi phương pháp có những ưu nhược điểm riêng, và việc lựa chọn phụ thuộc vào mục tiêu cụ thể của bạn: bạn chỉ cần biết có tồn tại hay không, hay bạn cần vị trí, hay thậm chí muốn trả về giá trị liên quan.

Có thể bạn quan tâm: Cách Chuyển Đổi Số Thành Văn Bản Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Sử Dụng COUNTIF Để Kiểm Tra Sự Tồn Tại

2.1. Cú Pháp Cơ Bản

=COUNTIF(range, criteria)

range: Dải ô bạn muốn kiểm tra (ví dụ: A1:A100).
criteria: Giá trị hoặc biểu thức bạn muốn tìm (ví dụ: "Apple" hoặc B1).

Nếu kết quả lớn hơn 0, nghĩa là giá trị tồn tại trong dải ô.

2.2. Ví Dụ Thực Tế

Giả sử bạn có danh sách sản phẩm trong cột A (A2:A20) và muốn kiểm tra xem sản phẩm trong ô D2 có tồn tại hay không:

=IF(COUNTIF($A$2:$A$20, D2) > 0, "Có", "Không")

Kết quả sẽ trả về “Có” nếu D2 xuất hiện trong danh sách, ngược lại “Không”.

2.3. Sử Dụng Wildcard

COUNTIF hỗ trợ ký tự đại diện:

: đại diện cho bất kỳ chuỗi ký tự nào.
? : đại diện cho một ký tự duy nhất.

Ví dụ: Kiểm tra xem có bất kỳ mục nào chứa “phone” trong cột B:

=COUNTIF(B:B, "phone")>0

2.4. Lưu Ý Khi Dùng COUNTIF

Không phân biệt hoa thường: COUNTIF không phân biệt chữ hoa và chữ thường. Nếu cần phân biệt, bạn phải dùng hàm SUMPRODUCT hoặc EXACT.
Không trả về vị trí: Đối với việc cần biết vị trí, bạn sẽ phải dùng MATCH hoặc XLOOKUP.
Giới hạn đối với mảng 2D: COUNTIF chỉ hoạt động trên một dải 1 chiều (cột hoặc hàng). Đối với nhiều cột, bạn cần kết hợp nhiều COUNTIF hoặc dùng SUMPRODUCT.

3. MATCH – Trả Về Vị Trí Của Giá Trị

3.1. Cú Pháp Cơ Bản

=MATCH(lookup_value, lookup_array, match_type)

lookup_value: Giá trị cần tìm.
lookup_array: Dải ô (cột hoặc hàng) chứa giá trị.
match_type: 0 cho tìm chính xác, 1 hoặc -1 cho tìm gần đúng (được sắp xếp).

3.2. Ví Dụ: Tìm Vị Trí

Bạn có danh sách mã khách hàng trong cột C (C2:C50) và muốn biết mã “KH123” nằm ở vị trí nào:

=MATCH("KH123", C2:C50, 0)

Nếu trả về 5, nghĩa là “KH123” là giá trị thứ 5 trong dải đã chọn (tương đương ô C6).

3.3. Kết Hợp VỚI ISNUMBER ĐỂ Kiểm Tra Sự Tồn Tại

Vì MATCH trả về lỗi #N/A khi không tìm thấy, nên thường dùng hàm ISNUMBER để chuyển thành TRUE/FALSE:

=IF(ISNUMBER(MATCH(D2, $A$2:$A$20, 0)), "Có", "Không")

3.4. Khi Dải Ô Là 2 Chiều

MATCH không hỗ trợ dải 2 chiều trực tiếp. Tuy nhiên, bạn có thể kết hợp với hàm INDEX để tạo mảng 1 chiều:

=IF(ISNUMBER(MATCH(D2, INDEX($A$2:$C$20, 0, 2), 0)), "Có", "Không")

Trong ví dụ trên, INDEX($A$2:$C$20, 0, 2) trả về toàn bộ cột thứ 2 (cột B) của dải A2:C20.

4. VLOOKUP & HLOOKUP – Tìm Kiểu Dữ Liệu Liên Quan

4.1. VLOOKUP Cơ Bản

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

col_index_num: Số cột trong table_array mà bạn muốn trả về.

Nếu muốn chỉ kiểm tra sự tồn tại, bạn có thể trả về một giá trị bất kỳ và dùng IFERROR:

=IFERROR(VLOOKUP(D2, $A$2:$B$100, 2, FALSE), "Không")

Nếu không tìm thấy, hàm sẽ trả về “Không”.

4.2. HLOOKUP Cho Dữ Liệu Ngang

Tương tự VLOOKUP nhưng hoạt động theo hàng:

=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)

4.3. Giới Hạn Của VLOOKUP/HLOOKUP

Không thể tìm sang trái: VLOOKUP chỉ tìm từ cột bên trái sang phải.
Hiệu năng: Khi dải dữ liệu lớn, VLOOKUP có thể chậm.
Không trả về vị trí: Chỉ trả về giá trị.

5. XLOOKUP – Giải Pháp Mới, Đầy Đủ

5.1. Cú Pháp

=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)

lookup_array: Dải ô chứa giá trị cần tìm.
return_array: Dải ô trả về giá trị tương ứng.
if_not_found: Giá trị trả về nếu không tìm thấy (mặc định là #N/A).
match_mode: 0 (chính xác), -1 (nhỏ hơn hoặc bằng), 1 (lớn hơn hoặc bằng), 2 (wildcard).
search_mode: 1 (tìm từ đầu), -1 (tìm ngược lại).

5.2. Kiểm Tra Sự Tồn Tại

=IF(ISNUMBER(XLOOKUP(D2, $A$2:$A$100, ROW($A$2:$A$100), "Không tìm thấy", 0, 1)), "Có", "Không")

Giải thích:

XLOOKUP trả về số hàng (ROW) nếu tìm thấy, ngược lại trả về “Không tìm thấy”.
ISNUMBER kiểm tra kết quả có phải là số (tức là đã tìm thấy).
Cuối cùng, IF chuyển thành “Có” hoặc “Không”.

5.3. Trả Về Vị Trí Và Giá Trị Cùng Lúc

Bạn có thể dùng XLOOKUP để trả về cả vị trí và giá trị:

=LET( pos, XLOOKUP(D2, $A$2:$A$100, ROW($A$2:$A$100), "Không tìm thấy", 0, 1), val, XLOOKUP(D2, $A$2:$A$100, $B$2:$B$100, "Không có giá trị", 0, 1), IF(pos="Không tìm thấy", "Không", "Vị trí: "&pos&", Giá trị: "&val)
)

LET giúp lưu trữ kết quả trung gian, làm công thức gọn gàng hơn và tăng hiệu năng.

6. IF + ISNUMBER + MATCH – Phương Pháp Kết Hợp

Đây là một trong những cách phổ biến nhất vì tính linh hoạt và khả năng mở rộng.

=IF(ISNUMBER(MATCH(D2, $A$2:$A$100, 0)), "Có", "Không")

6.1. Khi Dải Ô Là Nhiều Cột

Có thể bạn quan tâm: Cách Tìm Số Trùng Lặp Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Nếu bạn muốn kiểm tra trong toàn bộ bảng (ví dụ A2:C100), bạn có thể dùng công thức mảng (Array Formula) với SUMPRODUCT:

=IF(SUMPRODUCT(--($A$2:$C$100 = D2)) > 0, "Có", "Không")

Lưu ý:

Công thức này không cần Ctrl+Shift+Enter trong Excel 365/2026 vì hỗ trợ dynamic arrays.
-- chuyển TRUE/FALSE thành 1/0.

6.2. Kiểm Tra Nhiều Giá Trị Cùng Lúc

Giả sử bạn có danh sách các giá trị cần kiểm tra trong E2:E10 và muốn trả về “Có” hoặc “Không” cho mỗi giá trị:

=IF(COUNTIF($A$2:$A$100, E2)>0, "Có", "Không")

Sau đó kéo công thức xuống E10.

7. Conditional Formatting – Đánh Dấu Trực Quan

Nếu mục tiêu của bạn chỉ là muốn đánh dấu các ô chứa giá trị trong một danh sách, Conditional Formatting (định dạng có điều kiện) là cách nhanh nhất.

7.1. Bước Thực Hiện

Chọn dải ô muốn kiểm tra (ví dụ A2:A100).
Vào Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
Nhập công thức:

excel =COUNTIF($D$2:$D$20, A2) > 0

Chọn định dạng (màu nền, font, …) và nhấn OK.

Kết quả: Các ô trong A2:A100 chứa bất kỳ giá trị nào trong danh sách D2:D20 sẽ được tô màu.

7.2. Ưu Nhược Điểm

Ưu điểm: Trực quan, không cần cột phụ.
Nhược điểm: Chỉ là biểu thị, không trả về giá trị trong ô.

8. Sử Dụng Power Query – Kiểm Tra Khi Xử Lý Dữ Liệu Lớn

Khi làm việc với hàng triệu bản ghi, các hàm truyền thống trong Excel có thể chậm. Power Query (Get & Transform) cung cấp khả năng xử lý dữ liệu mạnh mẽ.

8.1. Cách Thực Hiện

Load dữ liệu: Chọn Data → Get Data → From Table/Range.
Trong Power Query Editor, Add Column → Custom Column.
Sử dụng công thức M:

m if List.Contains(#"OtherTable"ColumnName, CurrentColumn) then "Có" else "Không"

#"OtherTable" là bảng chứa danh sách kiểm tra.
CurrentColumn là cột hiện tại bạn đang kiểm tra.
Close & Load để đưa kết quả trở lại Excel.

8.2. Lợi Ích

Hiệu năng: Xử lý nhanh hơn với dữ liệu lớn.
Tái sử dụng: Các bước được lưu trong query, dễ dàng cập nhật.

9. Khi Nào Nên Chọn Phương Pháp Nào?

Tình huống	Phương pháp đề xuất
Kiểm tra chỉ có/không có một giá trị trong một cột đơn	`COUNTIF` hoặc `IF + ISNUMBER(MATCH)`
Cần vị trí (số thứ tự) của giá trị	`MATCH` hoặc `XLOOKUP` (kèm ROW)
Cần trả về giá trị liên quan (cột khác)	`VLOOKUP`/`HLOOKUP` (cũ) hoặc `XLOOKUP` (mới)
Muốn đánh dấu trực quan trong bảng	Conditional Formatting
Dữ liệu rất lớn (hàng ngàn/một triệu)	Power Query hoặc `SUMPRODUCT` với mảng
Cần hỗ trợ wildcard (tìm phần chuỗi)	`COUNTIF` với /`?` hoặc `XLOOKUP` với `match_mode = 2`
Cần phân biệt hoa thường	`EXACT` kết hợp `SUMPRODUCT` hoặc `FILTER` (Excel 365)

10. Các Mẹo Nâng Cao

10.1. Phân Biệt Hoa Thường Với COUNTIF

=SUMPRODUCT(--EXACT($A$2:$A$100, D2))

EXACT trả về TRUE nếu hai chuỗi giống hệt (bao gồm hoa thường). SUMPRODUCT cộng các TRUE (1) lại để biết có bao nhiêu lần xuất hiện.

10.2. Tìm Giá Trị Gần Nhất

Nếu bạn muốn kiểm tra giá trị gần nhất (ví dụ: tìm số gần nhất trong dải), dùng XLOOKUP với match_mode = -1 hoặc 1:

=XLOOKUP(D2, $A$2:$A$100, $A$2:$A$100, "Không tìm thấy", -1)

10.3. Kiểm Tra Nhiều Giá Trị Cùng Lúc Với FILTER (Excel 365)

=LET( list, $A$2:$A$100, check, $D$2:$D$10, result, FILTER(check, ISNUMBER(MATCH(check, list, 0))), IF(COUNTA(result)=0, "Không có giá trị nào khớp", result)
)

FILTER trả về danh sách các giá trị trong check mà tồn tại trong list.
LET giúp lưu biến và cải thiện hiệu năng.

11. Xử Lý Lỗi Thường Gặp

Lỗi	Nguyên nhân	Cách khắc phục
`#N/A`	MATCH/VLOOKUP/XLOOKUP không tìm thấy giá trị	Dùng `IFERROR(..., "Không")` hoặc `IF(ISNA(...), ...)`
`#VALUE!`	Sử dụng hàm COUNTIF với dải không hợp lệ (ví dụ: dải 2 chiều)	Đảm bảo dải là 1 chiều hoặc dùng `SUMPRODUCT`
`#SPILL!`	Công thức mảng tràn ra ô đã có dữ liệu	Xóa hoặc di chuyển dữ liệu chướng ngại
`#NAME?`	Tên hàm không tồn tại (phiên bản Excel cũ)	Sử dụng hàm tương thích (ví dụ: thay XLOOKUP bằng VLOOKUP)
Kết quả sai do không phân biệt hoa thường	Dùng COUNTIF/ MATCH chuẩn	Dùng `EXACT` hoặc `BINARY` collations trong Power Query

12. Thực Hành – Bài Tập Tự Làm

Bài 1: Kiểm Tra Sự Tồn Tại Với COUNTIF

Mô tả: Trong cột A (A2:A50) có danh sách tên khách hàng. Kiểm tra xem tên trong B2 có tồn tại không và trả về “Có” hoặc “Không”.
Công thức:

excel =IF(COUNTIF($A$2:$A$50, B2) > 0, "Có", "Không")

Bài 2: Trả Về Vị Trí Với MATCH

Mô tả: Tìm vị trí của mã sản phẩm “SP001” trong cột C (C2:C200). Nếu không có, trả về “Không tồn tại”.
Công thức:

excel =IFERROR(MATCH("SP001", $C$2:$C$200, 0), "Không tồn tại")

Bài 3: Đánh Dấu Các Giá Trị Trùng Lặp

Mô tả: Dùng Conditional Formatting để tô màu các ô trong D2:D100 nếu giá trị đó xuất hiện trong A2:A50.
Công thức Conditional Formatting:

excel =COUNTIF($A$2:$A$50, D2) > 0

Bài 4: Kiểm Tra Nhiều Giá Trị Đồng Loại Với SUMPRODUCT

Mô tả: Kiểm tra xem bất kỳ giá trị nào trong E2:E10 có xuất hiện trong B2:B100 không.
Công thức (ô F2):

excel =IF(SUMPRODUCT(--($B$2:$B$100 = E2)) > 0, "Có", "Không")

Sau đó kéo xuống F10.

Bài 5: Sử Dụng XLOOKUP Để Kiểm Tra Và Trả Về Giá Trị

Mô tả: Từ bảng “Sản phẩm” (cột A: Mã SP, cột B: Tên SP), kiểm tra mã trong G2 và trả về tên SP. Nếu không có, trả về “Không tìm thấy”.
Công thức:

excel =XLOOKUP(G2, $A$2:$A$500, $B$2:$B$500, "Không tìm thấy", 0)

13. Kết Luận

Việc kiểm tra giá trị có tồn tại trong một dải ô là một kỹ năng nền tảng nhưng rất mạnh mẽ trong Excel. Từ các hàm đơn giản như COUNTIF cho đến các giải pháp hiện đại như XLOOKUP, FILTER và Power Query, bạn có thể chọn công cụ phù hợp dựa trên nhu cầu: tốc độ, độ phức tạp, hay khả năng mở rộng.

Hãy áp dụng những ví dụ và mẹo trong bài viết này vào công việc thực tế của bạn. Khi đã nắm vững các công thức, bạn sẽ tiết kiệm được thời gian, giảm thiểu lỗi và tạo ra các bảng tính chuyên nghiệp, chính xác hơn. Đừng ngại thử nghiệm, kết hợp các hàm với nhau và tạo ra những giải pháp tùy chỉnh cho từng trường hợp cụ thể. Chúc bạn thành công và luôn “excel” trong mọi dự án!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.