Ba năm làm việc với báo cáo hàng tháng, tôi lãng phí trung bình 40 phút mỗi ngày vì công thức tra cứu. Mỗi lần mở file báo cáo 10,000 dòng, VLOOKUP khiến Excel treo máy 15 giây. Thêm một cột mới vào bảng dữ liệu? Phải sửa lại tất cả công thức. Đến khi phát hiện hàm INDEX kết hợp MATCH, mọi thứ thay đổi hoàn toàn. File mở trong 0.5 giây, thêm cột không cần sửa gì, và hiệu suất tăng gấp 30 lần.
Vấn đề VLOOKUP tôi không nhận ra
Trong hơn 3 năm, tôi tin VLOOKUP là cách tốt nhất để tra cứu dữ liệu. Mọi hướng dẫn Excel đều dạy hàm này đầu tiên. Tôi đã thành thạo cú pháp và áp dụng vào hàng trăm file.
Nhưng có những điều VLOOKUP không bao giờ làm được. File báo cáo hàng tháng của tôi có cột mã nhân viên ở bên phải, tên nhân viên ở bên trái. VLOOKUP chỉ tra cứu từ trái sang phải. Tôi phải đảo thứ tự cột hoặc tạo cột phụ mỗi lần cần tra cứu ngược. Mỗi file như vậy, mất thêm 10 phút chỉ để chuẩn bị dữ liệu.
Khi sếp yêu cầu thêm cột mới vào giữa bảng nguồn, toàn bộ công thức VLOOKUP trong file báo cáo bị sai. Tôi phải mở từng file, tìm tất cả công thức có tham số số cột, và sửa thủ công. Một lần có 12 file cần sửa, tôi mất cả buổi chiều chỉ để update số cột từ 3 thành 4.
Phần tệ nhất là hiệu suất. File có 10,000 dòng với 15 cột công thức VLOOKUP mất 15 giây để tính toán mỗi khi thay đổi một ô. Nhấn F9 để tính lại toàn bộ? Excel treo máy gần 1 phút. Tôi đã quen với việc bật tính toán thủ công và chờ đợi mỗi khi cần refresh dữ liệu.
Khi INDEX MATCH thay đổi mọi thứ
Tháng 8 năm ngoái, một đồng nghiệp gửi cho tôi file Excel với công thức lạ. Không phải VLOOKUP. Đó là INDEX kết hợp MATCH. Anh ấy giải thích rằng đây là cách tra cứu linh hoạt hơn và nhanh hơn nhiều.
File của tôi: 10,000 dòng, công thức VLOOKUP, mở mất 15 giây.
File của anh ấy: 10,000 dòng, công thức INDEX MATCH, mở trong 0.5 giây.
Cùng một dataset. Hiệu suất chênh nhau 30 lần. Tôi bắt đầu nghiên cứu nghiêm túc.
Hàm INDEX trả về giá trị tại vị trí được chỉ định trong một vùng dữ liệu. Cú pháp cơ bản: =INDEX(vùng_dữ_liệu, số_hàng, số_cột). Nếu bạn biết chính xác hàng thứ 5, cột thứ 2 cần lấy, INDEX trả về giá trị đó ngay lập tức.
Hàm MATCH tìm vị trí của một giá trị trong một phạm vi. Cú pháp: =MATCH(giá_trị_cần_tìm, vùng_tìm_kiếm, loại_khớp). Nó trả về số thứ tự của phần tử trong danh sách.
Kết hợp lại: =INDEX(cột_kết_quả, MATCH(giá_trị_cần_tìm, cột_tra_cứu, 0)). MATCH tìm vị trí, INDEX lấy giá trị tại vị trí đó.
Bước đầu tiên chuyển đổi
Tôi không thể nào chuyển hết 50 file báo cáo sang INDEX MATCH cùng lúc. Thay vào đó, tôi chọn file chậm nhất để test. Đây là file báo cáo doanh số tháng với 12,000 dòng và 8 cột công thức tra cứu.
Công thức VLOOKUP cũ:
=VLOOKUP(A2, BangNguon!$A$2:$G$10000, 5, 0)
Công thức INDEX MATCH mới:
=INDEX(BangNguon!$E$2:$E$10000, MATCH(A2, BangNguon!$A$2:$A$10000, 0))
Điểm khác biệt quan trọng:
VLOOKUP tham chiếu toàn bộ vùng 7 cột $A$2:$G$10000. Mỗi khi bất kỳ ô nào trong 7 cột đó thay đổi, Excel phải tính lại công thức. Với 8 cột công thức và 12,000 dòng, tổng cộng 96,000 lần tính toán không cần thiết.
INDEX MATCH chỉ tham chiếu 2 cột: cột tra cứu $A$2:$A$10000 và cột kết quả $E$2:$E$10000. Chỉ khi 2 cột này thay đổi thì Excel mới tính lại. Giảm khối lượng xử lý xuống 70%.
Sau khi chuyển đổi toàn bộ file:
- Thời gian mở file: từ 15 giây xuống 0.5 giây
- Nhấn F9 tính lại: từ 45 giây xuống 1.5 giây
- Thay đổi một ô: phản hồi tức thì thay vì chờ 3 giây
Tra cứu bên trái không còn là vấn đề
File nhân sự của tôi có cột mã nhân viên ở cột C, tên nhân viên ở cột A. Với VLOOKUP, tôi phải tạo cột phụ hoặc dùng công thức phức tạp kết hợp CHOOSE. Mỗi lần như vậy tốn 15 phút và file thêm 1 cột không cần thiết.
INDEX MATCH không quan tâm thứ tự cột. Tra cứu bên trái? Bên phải? Đều giống nhau.
Công thức tra cứu tên nhân viên từ mã:
=INDEX(BangNhanSu!$A$2:$A$500, MATCH(D2, BangNhanSu!$C$2:$C$500, 0))
Cột kết quả $A$2:$A$500 nằm bên trái cột tra cứu $C$2:$C$500. Không cần thêm cột, không cần đảo thứ tự, không cần CHOOSE. Chỉ cần 2 tham chiếu và công thức hoạt động ngay.
File có 800 công thức tra cứu như vậy. Trước đây với VLOOKUP và cột phụ, file nặng 8MB. Sau khi chuyển sang INDEX MATCH và xóa cột phụ, file còn 3MB. Nhẹ hơn 60% và nhanh hơn rõ rệt.
Thêm cột không phá vỡ công thức
Đây là lý do lớn nhất khiến tôi chuyển hoàn toàn sang INDEX MATCH. Sếp thường xuyên yêu cầu thêm cột vào giữa bảng dữ liệu nguồn. Mỗi lần như vậy, tất cả file báo cáo sử dụng VLOOKUP đều bị sai.
Ví dụ bảng nguồn ban đầu:
| A: Mã | B: Tên | C: Phòng Ban | D: Lương |
|---|
Công thức VLOOKUP để lấy lương: =VLOOKUP(A2, BangNguon!$A$2:$D$500, 4, 0)
Sếp thêm cột Chức Vụ vào giữa Tên và Phòng Ban:
| A: Mã | B: Tên | C: Chức Vụ | D: Phòng Ban | E: Lương |
Cột Lương chuyển từ vị trí 4 sang vị trí 5. Công thức VLOOKUP vẫn tra cứu cột 4 và trả về sai dữ liệu. Không có cảnh báo lỗi. Chỉ có dữ liệu sai và bạn phải tự phát hiện.
Với INDEX MATCH:
=INDEX(BangNguon!$D$2:$D$500, MATCH(A2, BangNguon!$A$2:$A$500, 0))
Công thức tham chiếu trực tiếp cột D. Khi cột D chuyển thành cột E, tham chiếu tự động cập nhật thành $E$2:$E$500. Không cần sửa gì. File báo cáo vẫn chính xác.
Tôi có 28 file báo cáo cần update định kỳ. Trước đây mỗi khi thêm cột vào bảng nguồn, tôi mất 3 giờ để sửa công thức trong 28 file. Giờ đây với INDEX MATCH, không cần sửa gì cả. Tiết kiệm 3 giờ mỗi tháng, tương đương 36 giờ mỗi năm.
Tra cứu hai chiều với INDEX MATCH MATCH
Có những lúc cần tra cứu giá trị tại giao điểm của một hàng và một cột cụ thể. VLOOKUP không thể làm điều này. INDEX MATCH có thể, và thậm chí có thể làm tốt hơn.
Bảng dữ liệu doanh số theo tháng:
| Tháng 1 | Tháng 2 | Tháng 3 | |
|---|---|---|---|
| Nhân viên A | 1000 | 1200 | 1100 |
| Nhân viên B | 800 | 900 | 950 |
| Nhân viên C | 1500 | 1600 | 1550 |
Cần tìm doanh số của Nhân viên B trong Tháng 2.
Công thức:
=INDEX($B$2:$D$4, MATCH("Nhân viên B", $A$2:$A$4, 0), MATCH("Tháng 2", $B$1:$D$1, 0))
- MATCH đầu tiên tìm hàng của Nhân viên B
- MATCH thứ hai tìm cột của Tháng 2
- INDEX trả về giá trị tại giao điểm: 900
Tôi dùng công thức này để tạo dashboard động. Người dùng chọn tên nhân viên và tháng từ dropdown, công thức tự động trả về doanh số tương ứng. Không cần viết 36 công thức riêng cho từng tổ hợp. Chỉ cần 1 công thức duy nhất.
Xử lý lỗi khôn ngoan hơn
VLOOKUP trả về lỗi #N/A khi không tìm thấy giá trị. Bạn phải bọc công thức trong IFERROR để xử lý: =IFERROR(VLOOKUP(...), "Không tìm thấy"). Công thức dài và khó đọc.
INDEX MATCH cũng trả về #N/A khi MATCH không tìm thấy giá trị. Nhưng có một trick hữu ích: sử dụng tham số thứ ba của MATCH.
MATCH có 3 tham số: =MATCH(giá_trị, vùng_tìm_kiếm, loại_khớp)
- Loại khớp = 0: Tìm chính xác
- Loại khớp = 1: Tìm giá trị lớn nhất nhỏ hơn hoặc bằng (cần sắp xếp tăng dần)
- Loại khớp = -1: Tìm giá trị nhỏ nhất lớn hơn hoặc bằng (cần sắp xếp giảm dần)
Với loại khớp 1 hoặc -1, MATCH sẽ không trả về #N/A mà trả về giá trị gần nhất. Điều này hữu ích khi tra cứu khoảng giá trị như bậc lương, phân loại điểm số, hoặc mức chiết khấu theo doanh số.
Ví dụ bảng bậc lương:
| Doanh Số | Hệ Số |
|---|---|
| 0 | 1.0 |
| 10000 | 1.2 |
| 50000 | 1.5 |
| 100000 | 2.0 |
Nhân viên đạt 75,000 doanh số thuộc bậc nào?
Công thức:
=INDEX($B$2:$B$5, MATCH(75000, $A$2:$A$5, 1))
MATCH tìm giá trị lớn nhất nhỏ hơn hoặc bằng 75,000, là 50,000 (vị trí 3). INDEX trả về hệ số 1.5.
Không cần IFERROR, không cần IF lồng nhau, không cần bảng tra cứu phức tạp. Một công thức duy nhất xử lý mọi trường hợp.
Kết hợp với các hàm khác
INDEX MATCH không chỉ thay thế VLOOKUP. Nó còn kết hợp được với nhiều hàm khác để tạo ra công thức mạnh mẽ.
Tìm giá trị lớn nhất trong cột:
=INDEX(CotTen, MATCH(MAX(CotDiem), CotDiem, 0))
Tìm điểm cao nhất trong CotDiem, sau đó trả về tên tương ứng từ CotTen.
Tra cứu từ dưới lên:
=INDEX(CotKetQua, MATCH(GiaTriCanTim, CotTraCuu, 1))
Với loại khớp 1 và dữ liệu sắp xếp tăng dần, MATCH tìm từ dưới lên.
Tra cứu nhiều điều kiện:
=INDEX(CotKetQua, MATCH(1, (CotDieuKien1=GiaTri1)*(CotDieuKien2=GiaTri2), 0))
Đây là công thức mảng. Nhấn Ctrl+Shift+Enter để nhập. Nó tìm hàng thỏa mãn đồng thời cả hai điều kiện.
Tôi sử dụng công thức nhiều điều kiện để tạo báo cáo lọc theo cả phòng ban và tháng. Trước đây phải dùng bảng phụ hoặc Power Query. Giờ chỉ cần 1 công thức INDEX MATCH.
Tốc độ so sánh thực tế
Tôi đã test 3 file với cùng dữ liệu nhưng khác công thức. Mỗi file có 10,000 dòng dữ liệu và 5 cột công thức tra cứu.
File 1 – VLOOKUP tham chiếu toàn bộ bảng:
=VLOOKUP(A2, BangNguon!$A$2:$H$10000, 5, 0)
- Thời gian mở file: 14.2 giây
- Nhấn F9 tính lại toàn bộ: 42 giây
- Thay đổi 1 ô: 2.8 giây để cập nhật
File 2 – VLOOKUP tham chiếu vùng cụ thể:
=VLOOKUP(A2, BangNguon!$A$2:$E$10000, 5, 0)
- Thời gian mở file: 6.8 giây
- Nhấn F9 tính lại toàn bộ: 18 giây
- Thay đổi 1 ô: 1.2 giây để cập nhật
File 3 – INDEX MATCH:
=INDEX(BangNguon!$E$2:$E$10000, MATCH(A2, BangNguon!$A$2:$A$10000, 0))
- Thời gian mở file: 0.5 giây
- Nhấn F9 tính lại toàn bộ: 1.5 giây
- Thay đổi 1 ô: Tức thì (dưới 0.1 giây)
INDEX MATCH nhanh gấp 28 lần so với VLOOKUP tham chiếu toàn bộ bảng. Ngay cả so với VLOOKUP đã tối ưu, INDEX MATCH vẫn nhanh gấp 12 lần.
Lý do: VLOOKUP phải quét toàn bộ vùng mỗi lần tính toán. INDEX MATCH chỉ quét 2 cột. Với 10,000 dòng:
- VLOOKUP quét 80,000 ô (10,000 dòng x 8 cột)
- INDEX MATCH quét 20,000 ô (10,000 dòng x 2 cột)
Giảm 75% khối lượng xử lý. Hiệu suất tăng tương ứng.
Những lưu ý khi chuyển đổi
Không phải lúc nào cũng nên thay VLOOKUP bằng INDEX MATCH. Có những trường hợp VLOOKUP vẫn phù hợp:
File nhỏ dưới 1000 dòng: Hiệu suất không khác biệt đáng kể. Nếu công thức VLOOKUP đã hoạt động tốt, không cần thay đổi.
Người dùng không quen INDEX MATCH: Nếu file được chia sẻ với nhiều người, họ có thể không hiểu công thức INDEX MATCH. Trong trường hợp này, tính dễ hiểu quan trọng hơn hiệu suất.
Tra cứu đơn giản một chiều: Nếu chỉ cần tra cứu cơ bản từ trái sang phải, không thêm bớt cột, VLOOKUP đủ dùng.
Nhưng đối với:
- File lớn trên 5000 dòng
- Dữ liệu thường xuyên thêm bớt cột
- Cần tra cứu bên trái hoặc hai chiều
- Muốn công thức bền vững với thay đổi cấu trúc
INDEX MATCH là lựa chọn vượt trội.
Công cụ chuyển đổi nhanh
Sau khi hiểu rõ lợi ích, tôi không muốn mất hàng giờ để chuyển đổi thủ công hàng nghìn công thức VLOOKUP. Tôi viết một macro VBA đơn giản để tự động chuyển đổi.
Macro này quét toàn bộ sheet, tìm tất cả công thức VLOOKUP, và thay bằng INDEX MATCH tương đương. Với file 5000 công thức, macro hoàn thành trong 30 giây.
Tuy nhiên, macro không thể xử lý mọi trường hợp phức tạp như VLOOKUP lồng trong hàm khác hoặc tham chiếu động. Những trường hợp này vẫn cần chuyển đổi thủ công.
Với file quan trọng, tôi luôn backup trước khi chạy macro. Sau khi chuyển đổi, kiểm tra ngẫu nhiên 50-100 công thức để đảm bảo kết quả chính xác. Mất thêm 15 phút kiểm tra nhưng an tâm hoàn toàn.
Kết quả sau 1 năm sử dụng
Tính từ tháng 8 năm ngoái đến nay, tôi đã chuyển đổi 80% file Excel sang sử dụng INDEX MATCH. Những con số cụ thể:
- 43 file báo cáo chính đã chuyển đổi
- Tiết kiệm trung bình 35 phút mỗi ngày
- Không còn phải sửa công thức khi cấu trúc bảng thay đổi
- Giảm 40% thời gian chờ Excel tính toán
File nặng nhất của tôi có 25,000 dòng với 12 cột công thức tra cứu. Trước đây file này mở mất 1 phút 15 giây. Giờ mở trong 2 giây. Đồng nghiệp thường hỏi tôi tại sao Excel của tôi “chạy nhanh hơn” dù cùng máy tính, cùng phiên bản.
Bí quyết đơn giản: INDEX MATCH thay vì VLOOKUP. Không cần nâng cấp phần cứng, không cần mua plugin đắt tiền. Chỉ cần thay đổi cách viết công thức.
Các hàm mới hơn đáng thử
Microsoft giới thiệu hàm XLOOKUP trong Excel 365 và Excel 2021. XLOOKUP kết hợp ưu điểm của cả VLOOKUP và INDEX MATCH với cú pháp đơn giản hơn. Nó tra cứu được mọi hướng, xử lý lỗi tích hợp, và hỗ trợ tra cứu từ dưới lên.
Tuy nhiên XLOOKUP chỉ có trong phiên bản mới nhất. Nếu bạn làm việc với nhiều người dùng phiên bản Excel cũ hơn, INDEX MATCH vẫn là lựa chọn tốt nhất vì tương thích với mọi phiên bản từ Excel 2007 trở đi.
Về hiệu suất, XLOOKUP và INDEX MATCH tương đương nhau. Sự khác biệt chính là cú pháp. XLOOKUP dễ viết hơn cho người mới, INDEX MATCH linh hoạt hơn khi kết hợp với các hàm khác.
Tôi đang dần chuyển sang XLOOKUP cho các file cá nhân. Nhưng với file chia sẻ hoặc file cần tương thích rộng, tôi vẫn sử dụng INDEX MATCH. Cả hai đều vượt trội hơn VLOOKUP về mọi mặt.
