Tôi Thay VLOOKUP Bằng Hàm MATCH Và Tiết Kiệm 70% Thời Gian Dò Tìm Dữ Liệu

Năm năm làm việc với Excel, tôi dùng VLOOKUP cho mọi tác vụ dò tìm dữ liệu. Cho đến khi một đồng nghiệp chỉ ra rằng bảng tính của tôi mất 45 giây để tính toán lại mỗi lần thay đổi một ô. Vấn đề là VLOOKUP đang quét toàn bộ 10 cột trong table_array mặc dù tôi chỉ cần 2 cột. Sau khi chuyển sang INDEX MATCH, thời gian tính toán giảm xuống 12 giây.

Mục Lục

Vấn đề tôi gặp với VLOOKUP

Công thức VLOOKUP đầu tiên tôi học là =VLOOKUP(A2,D2:G100,3,0). Đơn giản và hoạt động tốt với bảng nhỏ. Nhưng công thức này chứa ba điểm yếu nghiêm trọng.

Hạn chế về hướng tìm kiếm

VLOOKUP chỉ tìm được giá trị nằm bên phải cột tra cứu. Nếu cột Mã Sản Phẩm nằm ở vị trí E và cột Tên Sản Phẩm ở vị trí C, VLOOKUP hoàn toàn bất lực. Tôi đã từng phải sắp xếp lại toàn bộ cấu trúc bảng chỉ để VLOOKUP hoạt động được.

Lỗi khi thay đổi cấu trúc bảng

Tham số col_index_num là một con số cố định. Công thức =VLOOKUP(A2,D2:G100,3,0) luôn lấy cột thứ 3 trong vùng D2:G100. Khi đồng nghiệp chèn thêm một cột vào giữa, cột tôi cần lấy bây giờ là cột thứ 4, nhưng công thức vẫn trả về cột 3 và cho kết quả sai hoàn toàn. Tôi đã mất 2 giờ để tìm lỗi trong báo cáo tháng 10 vì vấn đề này.

Hiệu suất kém với dữ liệu lớn

VLOOKUP phải quét toàn bộ table_array từ cột đầu tiên đến cột cuối cùng. Trong bảng có 10 cột và 50.000 dòng, mỗi công thức VLOOKUP phải kiểm tra 500.000 ô ngay cả khi chỉ cần 2 cột. File Excel 200MB của tôi mở mất 3 phút và đóng băng mỗi khi chỉnh sửa.

Hàm MATCH trả về vị trí thay vì giá trị

Khác với VLOOKUP trả về giá trị, hàm MATCH trả về vị trí tương đối của giá trị trong một dãy ô. Cú pháp là =MATCH(lookup_value, lookup_array, [match_type]).

Ba kiểu tìm kiếm của MATCH

XEM THÊM: Chèn 100 Ảnh Vào Excel Trong 30 Giây Với Hàm IMAGE Mới

Match_type quyết định cách hàm tìm kiếm giá trị:

Kiểu 0 – Khớp chính xác: Tìm giá trị đầu tiên giống hệt lookup_value. Dữ liệu không cần sắp xếp. Ví dụ với dãy A1:A5 chứa các giá trị 10, 25, 50, 75, 100, công thức =MATCH(50,A1:A5,0) trả về 3 vì 50 nằm ở vị trí thứ 3.

Kiểu 1 – Lớn nhất nhỏ hơn hoặc bằng: Tìm giá trị lớn nhất mà nhỏ hơn hoặc bằng lookup_value. Dữ liệu phải sắp xếp tăng dần. Công thức =MATCH(55,A1:A5,1) trả về 3 vì 50 là giá trị lớn nhất nhỏ hơn 55.

Kiểu -1 – Nhỏ nhất lớn hơn hoặc bằng: Tìm giá trị nhỏ nhất mà lớn hơn hoặc bằng lookup_value. Dữ liệu phải sắp xếp giảm dần. Với dãy 100, 75, 50, 25, 10, công thức =MATCH(55,A1:A5,-1) trả về 2 vì 75 là giá trị nhỏ nhất lớn hơn 55.

Ví dụ cơ bản

Trong bảng danh sách học sinh từ A2 đến A10 với tên Nguyễn Văn A, Trần Thị B, Lê Văn C, công thức =MATCH("Lê Văn C",A2:A10,0) trả về 3. Đây là vị trí tương đối tính từ ô đầu tiên A2, không phải vị trí hàng trong bảng tính.

Kết hợp INDEX MATCH thay thế VLOOKUP

Hàm INDEX có cú pháp =INDEX(array, row_num, [column_num]) và trả về giá trị tại vị trí row_num và column_num trong array. Khi kết hợp với MATCH, công thức trở thành =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)).

Ưu điểm vượt trội

INDEX MATCH có thể tìm kiếm bất kỳ hướng nào. Công thức =INDEX(C2:C100, MATCH(E2, D2:D100, 0)) tìm giá trị E2 trong cột D và trả về giá trị tương ứng từ cột C nằm bên trái. VLOOKUP không thể làm điều này.

Column_num trong INDEX là kết quả tính toán động từ MATCH thay vì số cố định. Khi chèn hoặc xóa cột, công thức tự động điều chỉnh vì nó tìm kiếm theo tên hoặc giá trị chứ không phải vị trí cố định.

Tốc độ xử lý nhanh hơn rõ rệt vì INDEX chỉ quét return_range và MATCH chỉ quét lookup_range, không phải toàn bộ table_array như VLOOKUP. Trong test với 50.000 dòng, INDEX MATCH hoàn thành trong 0.5 giây so với 6.6 giây của VLOOKUP.

XEM THÊM: Cách Loại Bỏ Hàng Trống Trong Excel Tự Động Với Hàm TRIMRANGE Microsoft 365

Dò tìm theo hai điều kiện

Trong bảng đơn giá sản phẩm theo loại và kích cỡ, tôi cần tìm đơn giá của Nồi Cơm loại C. Bảng Đơn Giá nằm ở vùng H3:K8 với hàng đầu tiên là loại A, B, C, D và cột đầu tiên là tên sản phẩm.

Công thức =INDEX($H$4:$K$8, MATCH(B4,$H$4:$H$8,0), MATCH(C4,$H$3:$K$3,0)) hoạt động như sau:

MATCH đầu tiên tìm vị trí hàng của Nồi Cơm trong cột H4:H8, trả về 1.

MATCH thứ hai tìm vị trí cột của loại C trong hàng H3:K3, trả về 3.

INDEX lấy giá trị tại giao điểm hàng 1 cột 3 trong vùng H4:K8, chính là đơn giá của Nồi Cơm loại C.

Copy công thức xuống các dòng khác, tất cả tự động điều chỉnh theo giá trị trong cột B và C. Không cần sửa col_index_num thủ công.

Ứng dụng thực tế tiết kiệm thời gian

So sánh hai cột dữ liệu

Để tìm giá trị trong cột A mà không có trong cột B, công thức =IF(ISNA(MATCH(A2,$B$2:$B$100,0)),"Không có trong cột B","") kiểm tra từng giá trị. MATCH trả về vị trí nếu tìm thấy, trả về lỗi #N/A nếu không tìm thấy. ISNA kiểm tra lỗi #N/A và IF hiển thị thông báo tương ứng.

Trong danh sách 500 nhân viên, tôi tìm ra 23 người có trong file tháng trước nhưng không có trong file tháng này trong 5 giây. Cách thủ công bằng mắt mất 30 phút và dễ bỏ sót.

Lookup động không bị lỗi

File báo cáo doanh thu tháng của tôi có 15 sheet, mỗi sheet tra cứu đơn giá từ sheet Bảng Giá chính. Đồng nghiệp thường xuyên thêm sản phẩm mới vào giữa bảng, làm lỗi toàn bộ công thức VLOOKUP có col_index_num cố định.

Sau khi đổi sang INDEX MATCH, tôi có thể thêm 10 sản phẩm mới ở bất kỳ vị trí nào trong Bảng Giá mà không cần sửa một công thức nào ở 15 sheet còn lại. Công thức tự tìm đúng cột và hàng dựa theo tên sản phẩm và loại.

Tìm kiếm với ký tự đại diện

Hàm MATCH hỗ trợ ký tự đại diện khi match_type là 0. Dấu hỏi chấm đại diện cho một ký tự bất kỳ, dấu sao đại diện cho chuỗi ký tự bất kỳ.

XEM THÊM: Hàm DROP Trong Excel 2024: Cách Tôi Loại Bỏ Hàng Và Cột Trong Vài Giây

Công thức =MATCH("N?i*",$A$2:$A$100,0) tìm vị trí của giá trị bắt đầu bằng N, ký tự thứ hai bất kỳ, ký tự thứ ba là i và theo sau là chuỗi bất kỳ. Nó khớp với Nồi Cơm, Núi Cao, Nói Chuyện nhưng không khớp với Nếu Như vì ký tự thứ ba không phải i.

Điều này giúp tìm kiếm linh hoạt khi không nhớ chính xác tên sản phẩm. Công thức =INDEX($C$2:$C$100, MATCH("*laptop*",$B$2:$B$100,0)) tìm đơn giá của sản phẩm có chứa từ laptop trong tên, dù tên đầy đủ là Laptop Dell Inspiron hay Máy tính xách tay Laptop HP.

Hạn chế cần lưu ý

Hàm MATCH không phân biệt chữ hoa chữ thường khi so sánh chuỗi văn bản. Tìm kiếm MATCH hoặc Match đều cho cùng kết quả. Để phân biệt case-sensitive, kết hợp với hàm EXACT theo cú pháp =MATCH(TRUE, EXACT($A$2:$A$100,"MATCH"),0) và nhấn Ctrl + Shift + Enter để nhập công thức mảng.

Giới hạn 255 ký tự của VLOOKUP không ảnh hưởng đến INDEX MATCH. Tôi đã dùng INDEX MATCH để tìm kiếm với lookup_value dài 500 ký tự không gặp lỗi #VALUE! như VLOOKUP.

MATCH chỉ trả về vị trí của giá trị đầu tiên tìm thấy nếu có nhiều giá trị trùng lặp. Trong danh sách điểm thi có ba học sinh đạt 10 điểm, MATCH chỉ trả về vị trí của học sinh đầu tiên đạt 10 điểm. Để lấy tất cả, cần dùng công thức mảng phức tạp hơn hoặc hàm FILTER trong Excel 365.

Kết quả sau sáu tháng chuyển đổi

File Excel 200MB của tôi bây giờ mở trong 1 phút thay vì 3 phút. Thời gian tính toán lại giảm từ 45 giây xuống 12 giây, tiết kiệm 73%. Quan trọng hơn, tôi không còn lo lắng mỗi khi đồng nghiệp thêm hoặc xóa cột trong bảng chia sẻ.

INDEX MATCH hoạt động trên Excel 2007 trở lên, bao gồm cả Excel 365. Nếu dùng Excel 365 hoặc Excel 2021, hàm XLOOKUP mới là lựa chọn đơn giản hơn với cú pháp dễ dàng nhưng tính năng tương đương INDEX MATCH.

Bắt đầu bằng cách thay thế những công thức VLOOKUP đơn giản nhất trong file hiện tại. Sau khi quen với INDEX MATCH cơ bản, chuyển sang các công thức phức tạp hơn có nhiều điều kiện. Thời gian đầu tư học INDEX MATCH hoàn toàn xứng đáng với thời gian tiết kiệm được mỗi ngày.