Đối với những người thành thạo Excel, chắc hẳn bạn đã rất quen thuộc với hàm VLOOKUP. Hàm này được dùng để tìm kiếm một giá trị trong ô khác dựa trên văn bản khớp trong cùng một hàng.
Nếu bạn mới bắt đầu với hàm VLOOKUP, có thể tham khảo bài viết trước về cách sử dụng VLOOKUP trong Excel.
Mặc dù mạnh mẽ, VLOOKUP có hạn chế về cách cấu trúc bảng tham chiếu để công thức hoạt động.
Bài viết này sẽ chỉ ra hạn chế mà VLOOKUP không thể xử lý và giới thiệu hàm INDEX-MATCH trong Excel để giải quyết vấn đề này.
Ví Dụ INDEX MATCH Trong Excel
Sử dụng bảng tính Excel sau, chúng ta có danh sách tên chủ xe và tên mẫu xe. Trong ví dụ này, chúng ta sẽ cố gắng lấy ID Xe dựa trên Mẫu Xe được liệt kê dưới nhiều chủ sở hữu như hình dưới:
Trên một sheet riêng tên CarType, chúng ta có cơ sở dữ liệu xe đơn giản với ID, Mẫu Xe và Màu Sắc.
Với cấu trúc bảng này, hàm VLOOKUP chỉ hoạt động nếu dữ liệu cần truy xuất nằm ở cột bên phải của trường khớp (Mẫu Xe).
Nói cách khác, với cấu trúc này, vì chúng ta khớp dựa trên Mẫu Xe, thông tin duy nhất có thể lấy được là Màu Sắc (không phải ID vì cột ID nằm bên trái cột Mẫu Xe).
Điều này xảy ra vì với VLOOKUP, giá trị tra cứu phải nằm ở cột đầu tiên và các cột tra cứu phải ở bên phải. Không điều kiện nào được đáp ứng trong ví dụ của chúng ta.
Tin tốt là INDEX-MATCH sẽ giúp chúng ta đạt được mục tiêu này. Thực tế, đây là kết hợp hai hàm Excel có thể hoạt động riêng lẻ: hàm INDEX và hàm MATCH.
Tuy nhiên, trong bài này chúng ta chỉ nói về sự kết hợp của hai hàm nhằm mô phỏng chức năng của VLOOKUP.
Công thức có thể hơi dài và đáng sợ lúc đầu. Tuy nhiên, sau khi sử dụng vài lần, bạn sẽ nhớ cú pháp ngay.
Đây là công thức đầy đủ trong ví dụ của chúng ta:
=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))
Phân tích từng phần:
=INDEX( – Dấu "=" biểu thị bắt đầu công thức trong ô và INDEX là phần đầu của hàm Excel chúng ta sử dụng.
CarType!$A$2:$A$5 – Các cột trên sheet CarType chứa dữ liệu chúng ta muốn truy xuất. Trong ví dụ này là ID của từng Mẫu Xe.
MATCH( – Phần thứ hai của hàm Excel chúng ta sử dụng.
B4 – Ô chứa văn bản tìm kiếm chúng ta sử dụng (Mẫu Xe).
CarType!$B$2:$B$5 – Các cột trên sheet CarType chứa dữ liệu để khớp với văn bản tìm kiếm.
0)) – Chỉ định văn bản tìm kiếm phải khớp chính xác với văn bản trong cột khớp (tức CarType!$B$2:$B$5). Nếu không tìm thấy khớp chính xác, công thức trả về #N/A.
Lưu ý: nhớ dấu ngoặc đóng kép ở cuối hàm "))" và dấu phẩy giữa các đối số.
Tôi đã chuyển từ VLOOKUP sang INDEX-MATCH vì nó có khả năng làm nhiều hơn VLOOKUP.
Các hàm INDEX-MATCH còn có lợi ích khác so với VLOOKUP:
- Tính Toán Nhanh Hơn
Khi làm việc với tập dữ liệu lớn mà tính toán mất nhiều thời gian do nhiều hàm VLOOKUP, bạn sẽ thấy thay thế tất cả bằng INDEX-MATCH làm tăng tốc độ tính toán tổng thể đáng kể.
- Không Cần Đếm Cột Tương Đối
Nếu bảng tham chiếu có khóa tìm kiếm ở cột C và dữ liệu cần lấy ở cột AQ, với VLOOKUP bạn phải biết/số đếm bao nhiêu cột giữa C và AQ.
Với INDEX-MATCH, bạn trực tiếp chọn cột index (cột AQ) chứa dữ liệu cần lấy và cột khớp (cột C).
- Trông Phức Tạp Hơn
VLOOKUP khá phổ biến hiện nay, nhưng không nhiều người biết dùng kết hợp INDEX-MATCH.
Chuỗi dài hơn trong hàm INDEX-MATCH giúp bạn trông như chuyên gia xử lý các hàm Excel phức tạp, nâng cao. Hãy tận hưởng!