Cách Sử Dụng Hàm If Kết Hợp Với Kiểm Tra Giá Trị Trong Một Dải Ô (range) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Excel If Contains Partial Text: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Mở Đầu: Vì Sao Việc Kiểm Tra Giá Trị Trong Một Dải Ô Lại Quan Trọng?

Trong môi trường làm việc với dữ liệu, Excel luôn là công cụ “đắc lực” giúp chúng ta xử lý, phân tích và trình bày thông tin một cách nhanh chóng và hiệu quả. Một trong những nhu cầu phổ biến nhất của người dùng Excel là xác định xem một giá trị nào đó có tồn tại trong một dải ô (range) hay không, và dựa vào kết quả đó thực hiện các hành động tiếp theo (ví dụ: đưa ra thông báo, tính toán, gán giá trị mới, …).

Đối với những người mới bắt đầu, việc này có thể gây nhầm lẫn vì Excel không có hàm “contains” (chứa) trực tiếp như một số ngôn ngữ lập trình. Tuy nhiên, nhờ sự kết hợp linh hoạt giữa hàm IF, COUNTIF, MATCH, ISNUMBER, SEARCH, FILTER, XLOOKUP, và một số hàm mảng khác, chúng ta hoàn toàn có thể thực hiện kiểm tra này một cách chính xác và hiệu quả.

Bài viết dưới đây sẽ cung cấp cho bạn:

1. Kiến thức nền tảng về các hàm liên quan.
2. Công thức cơ bản sử dụng IF + COUNTIF để kiểm tra một giá trị trong một dải ô.
3. Các phương pháp nâng cao sử dụng MATCH, XLOOKUP, FILTER, và hàm mảng (dynamic arrays) để đáp ứng các yêu cầu phức tạp hơn.
4. Mô hình thực tế: ví dụ trong quản lý bán hàng, kiểm kê kho, phân loại khách hàng, và các trường hợp thực tế khác.
5. Mẹo tối ưu về hiệu suất, tránh lỗi và cách bảo trì công thức.
6. Bài tập thực hành và giải đáp các câu hỏi thường gặp.

Hãy cùng đi sâu vào từng khía cạnh để nắm vững cách “Excel IF range contains value” một cách toàn diện.

Có thể bạn quan tâm: Cách Tắt Hiển Thị Công Thức Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Tổng Quan Về Các Hàm Cơ Bản

1.1 Hàm IF

IF(logical_test, value_if_true, value_if_false)

• logical_test: điều kiện cần kiểm tra (ví dụ: A1>10).
• value_if_true: giá trị trả về nếu điều kiện đúng.
• value_if_false: giá trị trả về nếu điều kiện sai.

1.2 Hàm COUNTIF / COUNTIFS

COUNTIF(range, criteria)
COUNTIFS(range1, criteria1, range2, criteria2, …)

• Đếm số ô thỏa mãn tiêu chí trong một dải ô.
• Khi kết quả >0 → có ít nhất một ô thỏa mãn → “contains”.

1.3 Hàm MATCH

MATCH(lookup_value, lookup_array, match_type)

• Trả về vị trí (số thứ tự) của giá trị trong một mảng.
• Nếu không tìm thấy, trả về lỗi #N/A.

1.4 Hàm ISNUMBER và ISERROR

• ISNUMBER(value): trả về TRUE nếu value là số.
• ISERROR(value): trả về TRUE nếu value là bất kỳ lỗi nào (bao gồm #N/A).

1.5 Hàm XLOOKUP (Excel 365/2026 trở lên)

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

• Thay thế VLOOKUP/HLOOKUP, có khả năng trả về “not found” mà không cần IFERROR.

1.6 Hàm FILTER (Excel 365)

FILTER(array, include, if_empty)

• Lọc dữ liệu dựa trên điều kiện, trả về mảng các giá trị thỏa mãn.

1.7 Hàm TEXTJOIN (đối với kiểm tra chuỗi)

TEXTJOIN(delimiter, ignore_empty, text1, text2, …)

• Kết hợp nhiều chuỗi thành một, hữu ích khi muốn kiểm tra “có chứa” một phần chuỗi trong một dải ô.

Có thể bạn quan tâm: Cách Thêm Đường Xu Hướng (trend Line) Vào Đồ Thị Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

2. Cách Đơn Giản Nhất: IF + COUNTIF

2.1 Kịch bản Cơ Bản

Bạn có một danh sách sản phẩm trong cột A (A2:A100) và muốn kiểm tra xem “Sản phẩm X” có tồn tại trong danh sách này không. Nếu có, trả về “Có trong kho”, nếu không trả về “Hết hàng”.

Công thức:

=IF(COUNTIF($A$2:$A$100, "Sản phẩm X")>0, "Có trong kho", "Hết hàng")

Giải thích:

• COUNTIF($A$2:$A$100, "Sản phẩm X") đếm số lần “Sản phẩm X” xuất hiện.
• Nếu kết quả >0 → ít nhất một lần xuất hiện → trả về “Có trong kho”.
• Ngược lại → trả về “Hết hàng”.

2.2 Áp Dụng Vào Nhiều Giá Trị Cùng Lúc

Giả sử cột B chứa các mã khách hàng và bạn muốn tạo cột C hiển thị “Đã mua” nếu mã khách hàng xuất hiện trong danh sách đơn hàng (E2:E500), ngược lại “Chưa mua”.

=IF(COUNTIF($E$2:$E$500, B2)>0, "Đã mua", "Chưa mua")

Sao chép công thức xuống cột C để áp dụng cho toàn bộ danh sách.

2.3 Kiểm Tra Nhiều Tiêu Chí (COUNTIFS)

Nếu bạn cần kiểm tra đồng thời hai tiêu chí, ví dụ: “Tên sản phẩm = ‘Bút’ và màu = ‘Đỏ’” trong một bảng có cột A (Tên) và B (Màu), sử dụng COUNTIFS:

=IF(COUNTIFS($A$2:$A$200, "Bút", $B$2:$B$200, "Đỏ")>0, "Có", "Không")

2.4 Khi Giá Trị Được Lấy Từ Ô Khác

Thay vì nhập trực tiếp chuỗi, bạn có thể tham chiếu tới ô chứa giá trị cần tìm.

=IF(COUNTIF($A$2:$A$100, D2)>0, "Tồn tại", "Không tồn tại")

Ở đây, D2 chứa giá trị bạn muốn kiểm tra.

3. Phương Pháp Nâng Cao: IF + MATCH + ISNUMBER

3.1 Khi Bạn Muốn Lấy Vị Trí Của Giá Trị

Nếu bạn không chỉ muốn biết “có tồn tại” mà còn muốn vị trí (số thứ tự) của giá trị trong dải ô, MATCH là lựa chọn tốt.

=IF(ISNUMBER(MATCH(D2, $A$2:$A$100, 0)), "Tìm thấy tại vị trí " & MATCH(D2, $A$2:$A$100, 0), "Không tìm thấy")

• MATCH(D2, $A$2:$A$100, 0) tìm vị trí chính xác.
• ISNUMBER kiểm tra xem MATCH trả về số (có) hay lỗi #N/A (không).

3.2 Xử Lý Nhiều Giá Trị Cùng Lúc Với Mảng

Trong Excel 365, bạn có thể viết công thức mảng để kiểm tra một danh sách các giá trị cùng lúc.

Giả sử D2:D10 chứa danh sách các mã sản phẩm cần kiểm tra trong A2:A100.

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

Khi nhập công thức này và nhấn Enter, Excel sẽ tự động “spill” (tràn) kết quả ra các ô tương ứng (D2:D10). Mỗi ô sẽ hiển thị “Có” hoặc “Không” tùy thuộc vào việc giá trị có trong dải A2:A100 hay không.

4. Sử Dụng XLOOKUP Để Kiểm Tra “Contains”

XLOOKUP không chỉ trả về giá trị mà còn có tùy chọn if_not_found để trả về một thông báo tùy chỉnh nếu không tìm thấy.

=IFERROR( XLOOKUP(D2, $A$2:$A$100, $A$2:$A$100, "Không tồn tại"), "Không tồn tại"
)

Hoặc ngắn gọn hơn:

=IF(NOT(ISERROR(XLOOKUP(D2, $A$2:$A$100, $A$2:$A$100))), "Tồn tại", "Không tồn tại")

4.1 Kết Hợp XLOOKUP Với IF để Trả Về Giá Trị Khác

Có thể bạn quan tâm: Excel If Function Multiple Conditions: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Nếu muốn trả về một giá trị liên quan (ví dụ: giá bán) khi sản phẩm tồn tại, và trả về “0” nếu không:

=IFERROR( XLOOKUP(D2, $A$2:$A$100, $B$2:$B$100, 0), 0
)

Trong đó:

• $A$2:$A$100 là danh sách mã sản phẩm.
• $B$2:$B$100 là danh sách giá bán tương ứng.
• 0 là giá trị trả về khi không tìm thấy.

5. Kiểm Tra “Chứa” Một Phần Chuỗi (Partial Match)

Trong một số trường hợp, bạn không muốn kiểm tra đúng bằng mà muốn kiểm tra có chứa một đoạn văn bản trong mỗi ô (ví dụ: tìm “Apple” trong “Apple iPhone 13”). Các hàm SEARCH, FIND, COUNTIF với ký tự đại diện () hoặc FILTER sẽ giúp.

5.1 Sử Dụng COUNTIF Với Wildcard

=IF(COUNTIF($A$2:$A$100, "" & D2 & "")>0, "Có chứa", "Không chứa")

• "" & D2 & "" tạo mẫu “Apple” → bất kỳ ô nào chứa “Apple” sẽ được đếm.

5.2 SEARCH + ISNUMBER

=IF(ISNUMBER(SEARCH(D2, A2)), "Có chứa", "Không chứa")

• SEARCH trả về vị trí xuất hiện chuỗi, hoặc lỗi #VALUE! nếu không tìm thấy.
• ISNUMBER chuyển lỗi thành FALSE, còn vị trí thành TRUE.

5.3 FILTER Để Lấy Danh Sách Các Ô Chứa Chuỗi

Nếu muốn trả về danh sách các ô chứa từ khóa, dùng FILTER (Excel 365):

=FILTER($A$2:$A$100, ISNUMBER(SEARCH(D2, $A$2:$A$100)), "Không có kết quả")

Kết quả sẽ là một mảng các ô chứa từ khóa D2.

5.4 Kết Hợp FILTER + IFERROR

=IFERROR( TEXTJOIN(", ", TRUE, FILTER($A$2:$A$100, ISNUMBER(SEARCH(D2, $A$2:$A$100)))), "Không có ô nào chứa"
)

Công thức này sẽ liệt kê các ô chứa từ khóa, ngăn cách bằng dấu phẩy; nếu không có, trả về thông báo “Không có ô nào chứa”.

6. Thực Tế Ứng Dụng

6.1 Quản Lý Kho Hàng

• Mục tiêu: Khi nhập mã sản phẩm vào ô B2, tự động hiển thị “Còn hàng” nếu mã tồn tại trong danh sách kho (cột A), ngược lại “Hết hàng”.
• Công thức:

=IF(COUNTIF($A$2:$A$500, B2)>0, "Còn hàng", "Hết hàng")

• Mở rộng: Thêm số lượng tồn kho (cột C) để hiển thị “Còn X đơn vị”:

=IFERROR( "Còn " & XLOOKUP(B2, $A$2:$A$500, $C$2:$C$500, 0) & " đơn vị", "Hết hàng"
)

6.2 Phân Loại Khách Hàng Dựa Trên Danh Sách Đen (Blacklist)

Giả sử danh sách khách hàng bị cấm (cột D) và bạn muốn đánh dấu “Không cho phép” trong cột E cho mỗi khách hàng trong danh sách giao dịch (cột A).

=IF(COUNTIF($D$2:$D$200, A2)>0, "Không cho phép", "Cho phép")

6.3 Kiểm Tra Định Dạng Email Hợp Lệ

Bạn muốn xác định các ô chứa địa chỉ email hợp lệ (có ký tự “@”) trong cột B.

=IF(ISNUMBER(SEARCH("@", B2)), "Email hợp lệ", "Không hợp lệ")

6.4 Tìm Kiếm Đối Tác Cung Cấp Trong Danh Sách

Khi nhập tên công ty vào ô D2, trả về địa chỉ công ty (cột C) nếu tồn tại, nếu không trả về “Không tìm thấy”.

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

6.5 Đánh Giá Điểm Học Sinh

Bạn có danh sách điểm trung bình (cột B) và muốn đặt “Giỏi” nếu điểm ≥8, “Khá” nếu ≥6.5, còn lại “Trung bình”. Đầu tiên, kiểm tra xem học sinh có trong danh sách không.

=IF(COUNTIF($A$2:$A$200, D2)=0, "Không có dữ liệu", IF(VLOOKUP(D2, $A$2:$B$200, 2, FALSE)>=8, "Giỏi", IF(VLOOKUP(D2, $A$2:$B$200, 2, FALSE)>=6.5, "Khá", "Trung bình")))

7. Tối Ưu Hóa Hiệu Suất Khi Dùng IF + COUNTIF

Khi dải ô lớn (hàng chục ngàn, hàng trăm ngàn) và công thức được sao chép hàng nghìn lần, hiệu suất có thể giảm sút. Dưới đây là một số mẹo:

1. Sử Dụng Dải Tĩnh (Static Range): Đặt dải ô cố định (ví dụ $A$2:$A$5000) thay vì toàn bộ cột (A:A). Excel sẽ tính nhanh hơn vì không phải duyệt toàn bộ cột.
2. Sử Dụng Bảng (Table): Khi dữ liệu ở dạng Table, Excel tự tối ưu truy vấn và cập nhật tự động.
3. Tránh Lặp Lại Hàm Đếm: Nếu bạn cần kiểm tra cùng một dải nhiều lần, hãy tính COUNTIF một lần trong một ô phụ và tham chiếu tới ô đó.
   excel G2 = COUNTIF($A$2:$A$5000, D2) H2 = IF(G2>0, "Có", "Không")
4. Sử Dụng XLOOKUP/INDEX-MATCH Khi Cần Truy Xuất Giá Trị: Thay vì kết hợp IF + COUNTIF + VLOOKUP, XLOOKUP thực hiện cả kiểm tra và trả về giá trị trong một hàm duy nhất, giảm số lần tính toán.
5. Bảo Trì Dải Động Với OFFSET hoặc INDEX: Khi dữ liệu thay đổi chiều dài, bạn có thể tạo dải động.
   excel =IF(COUNTIF(OFFSET($A$1,1,0,COUNTA($A:$A)-1,1), D2)>0, "Có", "Không")
6. Sử Dụng Hàm ARRAYFORMULA (Google Sheets) Hoặc @ (Dynamic Arrays) Trong Excel 365: Cho phép tính toàn bộ mảng một lần thay vì từng ô.

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

9. Bài Tập Thực Hành

Bài 1: Kiểm Tra Sản Phẩm Trong Danh Sách

• Dữ liệu: Cột A (Tên sản phẩm), Cột B (Giá), Cột C (Số lượng).
• Yêu cầu: Khi nhập tên sản phẩm vào ô E2, trả về “Còn hàng” nếu số lượng >0, ngược lại “Hết hàng”. Nếu sản phẩm không tồn tại, trả về “Sản phẩm không có”.

Công thức gợi ý:

=IFERROR( IF(VLOOKUP(E2, $A$2:$C$200, 3, FALSE)>0, "Còn hàng", "Hết hàng"), "Sản phẩm không có"
)

Bài 2: Lọc Danh Sách Email Hợp Lệ

• Dữ liệu: Cột A chứa các chuỗi (có thể là email, số điện thoại, …).
• Yêu cầu: Tạo danh sách các email hợp lệ (chứa ký tự “@”) và hiển thị trong một ô.

Công thức:

=TEXTJOIN(", ", TRUE, FILTER($A$2:$A$500, ISNUMBER(SEARCH("@", $A$2:$A$500)), "Không có email"))

Bài 3: Đánh Dấu Khách Hàng Đã Mua

• Dữ liệu: Cột A (Mã khách hàng), Cột B (Tên khách hàng). Danh sách đơn hàng trong Cột D (Mã khách hàng đã mua).
• Yêu cầu: Ở cột C, đánh dấu “Đã mua” nếu mã khách hàng xuất hiện trong danh sách đơn hàng, ngược lại “Chưa mua”.

Công thức:

=IF(COUNTIF($D$2:$D$1000, A2)>0, "Đã mua", "Chưa mua")

Sao chép xuống toàn bộ cột C.

10. Kết Luận

Việc kiểm tra một giá trị có tồn tại trong một dải ô là một trong những thao tác cơ bản nhưng vô cùng quan trọng trong Excel. Nhờ sự linh hoạt của các hàm IF, COUNTIF, MATCH, XLOOKUP, FILTER, và các hàm hỗ trợ khác, bạn có thể:

• Xác định nhanh chóng sự tồn tại của một giá trị.
• Lấy vị trí, giá trị liên quan, hoặc thậm chí lọc ra tất cả các ô thỏa mãn.
• Xây dựng các mô hình tự động hoá công việc như quản lý kho, phân loại khách hàng, kiểm tra dữ liệu nhập liệu, và nhiều hơn nữa.
• Tối ưu hiệu suất và tránh lỗi bằng cách áp dụng các kỹ thuật như dải tĩnh, bảng (Table), công thức mảng và xử lý lỗi.

Hãy thực hành với các ví dụ thực tế ở trên, tùy biến công thức sao cho phù hợp với nhu cầu công việc của bạn. Khi đã nắm vững nền tảng, bạn sẽ thấy Excel là một công cụ mạnh mẽ không chỉ để lưu trữ dữ liệu mà còn để phân tích, kiểm tra, và đưa ra quyết định một cách nhanh chóng và chính xác.

Nếu còn bất kỳ thắc mắc nào về cách áp dụng “Excel IF range contains value” trong các trường hợp đặc biệt, đừng ngần ngại tìm hiểu thêm tài liệu, tham gia cộng đồng người dùng Excel, hoặc đặt câu hỏi trên các diễn đàn chuyên môn. Chúc bạn thành công trong mọi dự án dữ liệu!

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 hì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ẻ.