Trong thế giới dữ liệu ngày nay, Excel đã trở thành công cụ phổ biến nhất để quản lý, phân tích và trực quan hóa dữ liệu. Một trong những thao tác quan trọng nhất khi làm việc với bảng tính là tìm kiếm một chuỗi ký tự trong một cột hoặc một vùng dữ liệu. Việc biết cách sử dụng các hàm tìm kiếm như VLOOKUP, HLOOKUP, INDEX + MATCH, SEARCH, FIND, FILTER, XLOOKUP (từ Excel 365) sẽ giúp bạn tiết kiệm thời gian và tránh sai sót khi xử lý dữ liệu lớn. Bài viết này sẽ đi sâu vào các hàm tìm kiếm, cách áp dụng chúng trong những tình huống thực tiễn, những mẹo nhỏ để tránh lỗi phổ biến, và các ví dụ minh họa bằng công thức thực tế.
Có thể bạn quan tâm: Tách Chuỗi Ký Tự Đặc Biệt Trong Excel: Hướng Dẫn Chi Tiết
1. Khi nào chúng ta cần tìm kiếm chuỗi ký tự?
- Xác minh dữ liệu: Kiểm tra xem một tên, mã sản phẩm, hoặc địa chỉ email có tồn tại trong danh sách không.
- Kết hợp dữ liệu: Lấy thông tin liên quan từ bảng khác (ví dụ: giá, mô tả sản phẩm) dựa trên một khóa duy nhất.
- Phân loại dữ liệu: Xác định nhóm, loại, hoặc trạng thái của một mục dựa trên chuỗi ký tự.
- Tìm kiếm bất thường: Tìm các giá trị sai chính tả, thiếu dấu, hoặc có khoảng trắng thừa.
Có thể bạn quan tâm: Hàm Trong Excel Bắt Buộc Phải Có Đối Số: Tất Cả Những Hàm Không Thể Bỏ Qua
2. Các hàm tìm kiếm cơ bản
2.1 VLOOKUP
Hàm VLOOKUP (Vertical Lookup) là một trong những hàm phổ biến nhất trong Excel. Cú pháp:
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
lookup_value: Giá trị cần tìm (chuỗi ký tự hoặc số).table_array: Vùng dữ liệu chứa giá trị cần tìm và giá trị trả về.col_index_num: Số thứ tự cột trongtable_arraychứa giá trị trả về.range_lookup: (TRUE mặc định) tìm kiếm giá trị gần nhất, hoặc (FALSE) tìm kiếm chính xác.
Ví dụ
Giả sử bạn có bảng Bảng A với cột Mã Sản Phẩm (A) và Tên Sản Phẩm (B). Bạn muốn tìm tên sản phẩm dựa trên mã:
=VLOOKUP(A2, $A$2:$B$100, 2, FALSE)
Nếu A2 chứa mã sản phẩm, công thức sẽ trả về tên của nó. Nếu không tìm.uk, sẽ trả về lỗi #N/A.
Lưu ý
VLOOKUPchỉ tìm kiếm cột đầu tiên củatable_array. Nếu bạn cần tìm kiếm trong cột trung tâm, bạn phải xếp lại dữ liệu hoặc dùngINDEX + MATCH.VLOOKUPkhông tìm kiếm trái sang phải; chỉ tìm từ trái sang phải.
2.2 HLOOKUP
Tương tự VLOOKUP, HLOOKUP (Horizontal Lookup) tìm kiếm theo hàng ngang.
HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)
Sử dụng khi dữ liệu của bạn được sắp xếp theo hàng thay vì cột.
2.3 INDEX + MATCH
Kết hợp hai hàm này để linh hoạt hơn trong việc tìm kiếm và lấy dữ liệu.
INDEX(array, row_num, column_num)trả về giá trị tại vị trí hàng/cột.MATCH(lookup_value, lookup_array, match_type)trả về vị trí củalookup_valuetronglookup_array.
Cú pháp:
INDEX(array, MATCH(lookup_value, lookup_array, 0))
Ví dụ:
=INDEX($B$2:$B$100, MATCH(A2, $A$2:$A$100, 0))
Đây là cách tốn ít lỗi hơn VLOOKUP và hỗ trợ tìm kiếm bất kỳ cột nào.
2.4 SEARCH và FIND
Hai hàm này tìm vị trí của một chuỗi con trong một chuỗi lớn.
SEARCH(find_text, within_text, start_num)(không phân biệt chữ hoa/hoa).FIND(find_text, within_text, start_num)(phân biệt chữ hoa/hoa).
Ví dụ:
=SEARCH("abc", "abcde") => 1
=FIND("A", "apple") => #VALUE! (vì không phân biệt)
Bạn có thể dùng kết quả của SEARCH để kiểm tra sự tồn tại:
=IF(ISNUMBER(SEARCH("abc", A1)), "Tìm thấy", "Không tìm thấy")
2.5 FILTER (Excel 365)
Hàm FILTER cho phép trích xuất các dòng thỏa mãn điều kiện.
FILTER(array, include, if_empty)
Ví dụ:
=FILTER(A2:B100, ISNUMBER(SEARCH("abc", A2:A100)), "Không có dữ liệu")
2.6 XLOOKUP (Excel 365 / 2026)
Hàm mới, linh hoạt, thay thế VLOOKUP và HLOOKUP. Cú pháp:
XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)
match_mode: 0 = chính xác, -1 = chính xác hoặc tìm giá trị nhỏ hơn, 1 = chính xác hoặc tìm giá trị lớn hơn.search_mode: 1 = tìm kiếm từ trên xuống dưới, -1 = từ dưới lên trên.
Ví dụ:
=XLOOKUP(A2, $A$2:$A$100, $B$2:$B$100, "Không tìm thấy")
2.7 XLOOKUP với điều kiện nhiều cột
Bạn có thể tìm kiếm bằng nhiều trường:
=XLOOKUP(1, (A2:A100=G2)(B2:B100=H2), C2:C100, "Không tìm thấy")
Có thể bạn quan tâm: Hàm Thống Kê Số Lần Xuất Hiện Trong Excel
3. Đối phó với lỗi phổ biến
3.1 Lỗi #N/A
- Xác nhận rằng giá trị tìm kiếm có tồn tại trong vùng dữ liệu.
- Kiểm tra khoảng trắng thừa (
TRIM). - Sử dụng
IFERRORhoặcIFNAđể hiển thị thông báo tùy chỉnh.
=IFERROR(VLOOKUP(A2, $A$2:$B$100, 2, FALSE), "Không tìm thấy")
3.2 Lỗi #VALUE!
- Sử dụng
SEARCHhoặcFINDđúng cú pháp. - Đảm bảo dữ liệu trong
lookup appliquerlà chuỗi ký tự.
3.3 Lỗi #REF!
- Khi sử dụng
INDEX, hãy chắc chắn rằng chỉ số hàng/cột hợp lệ. - Khi sử dụng
VLOOKUP, hãy chắc chắn rằngcol_index_numkhông vượt quá số cột trongtable_array.
Có thể bạn quan tâm: Hàm Sắp Xếp Từ Cao Đến Thấp Trong Excel: Hướng Dẫn Chi Tiết Và Ứng Dụng Thực Tế
4. Mẹo nâng cao: Tìm kiếm không.Click
4.1 Tìm kiếm phần chứa
Sử dụng SEARCH kết hợp với IF để kiểm tra phần chứa:
=IF(ISNUMBER(SEARCH("abc", A1)), "Có chứa", "Không")
4.2 Tìm kiếm bất kỳ chuỗi nào trong danh sách
Sử dụng công thức mảng (có thể kết hợp với FILTER):
=FILTER(A2:A100, SUMPRODUCT(--ISNUMBER(SEARCH({"abc","def","ghi"}, A2:A100)))>0)
4.3 Tìm kiếm theo chuỗi chính xác với điều kiện phức tạp
Kết hợp INDEX + MATCH với IF:
=IFERROR(INDEX($B$2:$B$100, MATCH(1, ($A$2:$A$100=G2)($C$2:$C$100=H2), 0)), "Không tìm thấy")
4.4 Tìm kiếm dữ liệu lặp lại
Sử dụng COUNTIF để đếm số lần xuất hiện:
=COUNTIF($A$2:$A$100, A2)
5. Tối ưu hóa công thức cho tập dữ liệu lớn
- Tránh công thức mảng trong mỗi ô: Sử dụng công thức mảng một lần và sao chép xuống.
- Sử dụng
INDEX + MATCHthay vìVLOOKUP:INDEX + MATCHthường nhanh hơn khi dữ liệu lớn. - Tính toán thủ công: Tắt tính toán tự động và bật lại khi hoàn thành.
6. Ví dụ thực tiễn
6.1 Tìm kiếm tên khách hàng từ bảng đơn hàng
Giả sử có bảng Đơn Hàng với Mã Khách Hàng ở cột A và Tên Khách Hàng ở cột B. Bảng Khách Hàng chứa thông tin chi tiết. Bạn muốn điền tên khách hàng vào Đơn Hàng.
=VLOOKUP(A2, KhachHang!$A$2:$B$500, 2, FALSE)
Hoặc dùng XLOOKUP:
=XLOOKUP(A2, KhachHang!$A$2:$A$500, KhachHang!$B$2:$B$500, "Không tìm thấy")
6.2 Kiểm tra tồn tại mã sản phẩm trong danh mục
=IF(COUNTIF(DS_SP!$A$2:$A$1000, A2)>0, "Tồn tại", "Không tồn tại")
6.3 Trích xuất dữ liệu dựa trên chuỗi chứa
Bạn muốn lọc tất cả đơn hàng có sản phẩm chứa từ “Laptop”:
=FILTER(DonHang!A2:D100, ISNUMBER(SEARCH("Laptop", DonHang!B2:B100)), "Không có dữ liệu")
6.4 So sánh hai bảng dữ liệu
Bạn có bảng Bảng 1 và Bảng 2. Muốn tìm những giá trị trong Bảng 1 không có trong Bảng 2:
=FILTER(Bang1!A2:A100, ISNA(MATCH(Bang1!A2:A100, Bang2!A2:A200, 0)), "Không có dữ liệu")
7. Kết luận
Tìm kiếm chuỗi ký tự trong Excel không chỉ là một thao tác đơn giản mà là nền tảng cho nhiều quy trình phân tích dữ liệu. Thành thạo các hàm VLOOKUP, XLOOKUP, INDEX + MATCH, SEARCH, FILTER sẽ giúp bạn làm việc nhanh hơn, chính xác hơn, và tránh những sai sót phổ biến. Khi làm việc với dữ liệu lớn, hãy chú ý tới hiệu suất và tính toán tự động. Cuối cùng, luôn kiểm tra dữ liệu đầu vào, loại bỏ khoảng trắng thừa, và sử dụng IFERROR để tránh lỗi hiển thị gây nhầm lẫn.
Hy vọng bài viết này đã cung cấp cho bạn một cái nhìn toàn diện và chi tiết về cách tìm kiếm chuỗi ký tự trong Excel. Hãy thử áp dụng ngay các công thức và mẹo trong bài viết để nâng cao hiệu quả công việc order của bạn!
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ẻ.