INDEX MATCH là gì và vì sao nên biết cách dùng hàm này?
Cách dùng hàm INDEX MATCH trong Excel mang lại nhiều lợi ích hơn VLOOKUP khi bảng dữ liệu thay đổi thường xuyên. Nếu bạn đã quen với VLOOKUP, đây là lúc thử một cách tra cứu linh hoạt hơn.
Hàm MATCH tìm xem một giá trị nằm ở dòng nào. Sau đó, hàm INDEX lấy đúng ô ở vị trí đó. Hai hàm ghép lại tạo thành cặp tra cứu rất linh hoạt.
Cú pháp cơ bản là =INDEX(vùng_kết_quả, MATCH(giá_trị_tra, cột_tra, 0)). Bạn chỉ cần nhớ cấu trúc này, phần còn lại là thay tên vùng cho phù hợp với bảng của mình.
Chuẩn bị dữ liệu trước khi lập công thức
Trước khi viết bất kỳ công thức nào, hãy nhìn lại bảng nguồn. Hàm chỉ cho kết quả đúng khi dữ liệu đã đúng. Bảng nên có dòng tiêu đề rõ ràng. Đừng để dòng trống chen giữa các bản ghi, vì Excel sẽ hiểu sai phạm vi tra cứu.
Một lỗi rất hay gặp là khoảng trắng thừa ở cuối mã số. Hai ô trông giống hệt nhau nhưng Excel coi là hai giá trị khác nhau. Hãy dùng hàm TRIM hoặc làm sạch cột trước khi tra.
Ngoài ra, cần kiểm tra xem số đang được lưu dưới dạng chữ hay dạng số. MATCH sẽ không khớp nếu một bên là chữ còn bên kia là số.
Các bước lập công thức từng bước
Bạn có thể làm theo trình tự sau. Mỗi bước đều kiểm tra được ngay trên bảng của mình.
- Bước 1: xác định ô cần lấy kết quả và giá trị cần tra. Ví dụ, mã học sinh nằm ở ô F2.
- Bước 2: dùng MATCH để tìm vị trí dòng của mã đó trong cột mã học sinh.
- Bước 3: lồng MATCH vào INDEX và chọn đúng cột điểm cần lấy.
- Bước 4: nhấn Enter, kéo công thức xuống và kiểm tra lại vài dòng bằng mắt.
Tham số 0 ở cuối MATCH nghĩa là tìm khớp chính xác. Đây là chi tiết dễ bỏ sót nhất, và cũng là nguyên nhân khiến nhiều người nhận kết quả sai mà không hiểu vì sao.
Ví dụ tra cứu bảng điểm và bảng giá
Hãy hình dung một bảng điểm có cột Mã HS ở cột A và cột Toán ở cột C. Để lấy điểm Toán theo mã nằm ở ô F2, bạn viết =INDEX(C2:C100, MATCH(F2, A2:A100, 0)). Kết quả trả về đúng điểm của học sinh đó, dù mã nằm ở dòng nào trong bảng.
Với bảng giá sản phẩm, cách làm tương tự. Đặt mã hàng ở một ô tra, rồi lấy đơn giá từ cột đơn giá. Khi nhập mã mới, đơn giá tự cập nhật, bạn không cần sửa lại công thức.
Tra cứu hai chiều khi bảng có nhiều môn học
Có những bảng cần tra theo cả tên học sinh lẫn tên môn học. Lúc này, bạn dùng MATCH cho cả hàng và cột. Công thức sẽ có hai phần MATCH: một phần chọn dòng, một phần chọn cột. Cách này phù hợp khi bảng điểm có nhiều môn nằm ngang.
Nếu tiêu đề cột bị đổi thứ tự, công thức vẫn đúng. MATCH tự tìm vị trí, không phụ thuộc việc bạn nhớ cột nào nằm đâu. Đây là lý do nhiều người chuyển sang INDEX MATCH khi làm những bảng thường xuyên thay đổi.
Các lỗi thường gặp và cách xử lý
Lỗi #N/A là lỗi phổ biến nhất. Nó có nghĩa là giá trị tra không tồn tại trong cột. Hãy kiểm tra khoảng trắng thừa, ký tự sai, hoặc số đang bị lưu thành chữ.
Lỗi #REF! xuất hiện khi vùng tham chiếu bị xóa, hoặc kích thước hai vùng không khớp nhau. Bạn chỉ cần chỉnh lại vùng cho đúng kích thước là xong.
Nếu quên tham số 0, MATCH có thể tìm gần đúng và cho kết quả sai mà không báo lỗi. Đây là lỗi khó phát hiện nhất, nên hãy luôn kiểm tra lại tham số này.
INDEX MATCH so với VLOOKUP: nên chọn cái nào?
Ưu điểm lớn nhất của INDEX MATCH là tra sang trái được. Cột kết quả nằm bên trái cột tra vẫn lấy được, điều mà VLOOKUP không làm được. Khi chèn hoặc xóa cột, INDEX MATCH cũng ít khi làm vỡ công thức hơn.
VLOOKUP vẫn đủ dùng cho bảng nhỏ, cấu trúc cố định và chỉ tra một chiều từ trái sang phải. Điều quan trọng là chọn công cụ phù hợp với bài toán của mình.
Mẹo làm việc với bảng lớn
Khi kéo công thức xuống, hãy dùng tham chiếu tuyệt đối với dấu $, ví dụ $A$2:$A$100, để vùng tra không bị lệch. Đặt tên vùng cho cột dữ liệu cũng giúp công thức dễ đọc hơn khi bạn quay lại sửa sau vài tháng.
Bạn cũng có thể bọc công thức trong IFERROR để thay lỗi #N/A bằng thông báo như “Không tìm thấy”. Bảng sẽ gọn gàng hơn khi chia sẻ cho người khác xem.
Nếu muốn hiểu rõ hơn các hàm tra cứu cơ bản trước khi học INDEX MATCH, bạn có thể đọc thêm bài hướng dẫn hàm VLOOKUP trong Excel từ cơ bản đến nâng cao. Khi đã nắm vững cả hai, bạn sẽ tự chọn được cách làm phù hợp với từng bảng.
Nếu công việc văn phòng của bạn cần một giải pháp email gọn nhẹ để trao đổi và gửi báo cáo bảng tính, bạn có thể tham khảo monamail.vn.
Ôn luyện thêm để làm chủ Excel
Nếu bạn đang chuẩn bị cho các kỳ thi tin học, thành thạo hàm tra cứu là phần quan trọng. Bạn có thể tham khảo cấu trúc đề thi MOS và cách ôn tập hiệu quả để biết cần luyện những kỹ năng nào.
Ngoài ra, nếu hay thao tác nhanh trên bảng tính, đừng quên các tổ hợp phím tắt. Bạn có thể xem tổng hợp phím tắt Excel giúp tăng tốc công việc để tiết kiệm thời gian mỗi ngày.
Câu hỏi thường gặp
INDEX MATCH có nhanh hơn VLOOKUP không?
Với bảng vài nghìn dòng, sự khác biệt gần như không đáng kể. Với bảng rất lớn, INDEX MATCH có thể nhanh hơn một chút. Lợi ích thực sự nằm ở tính linh hoạt, không phải tốc độ.
Có thể tra cứu với nhiều hơn một điều kiện không?
Có. Bạn có thể ghép hai điều kiện bằng công thức mảng, hoặc tạo một cột phụ nối hai giá trị lại với nhau. Cách tạo cột phụ dễ làm và dễ kiểm tra hơn cho người mới bắt đầu.
Vì sao công thức của tôi trả về #N/A?
Thường là do giá trị tra không có trong cột, hoặc có khoảng trắng thừa, hoặc một bên là số còn bên kia là chữ. Hãy kiểm tra ba điểm này trước, phần lớn trường hợp sẽ được giải quyết.
Có cần sắp xếp dữ liệu trước khi dùng INDEX MATCH không?
Không cần. Khi dùng tham số 0, MATCH tìm khớp chính xác và không yêu cầu bảng phải được sắp xếp. Đây là điểm khác biệt với một số cách tìm gần đúng.