INDEX MATCH: Phương Pháp Tra Cứu Vượt Trội

Nếu bạn đã quen dùng VLOOKUP cho mọi tác vụ tra cứu trong Excel, bạn không phải là trường hợp ngoại lệ — đây là một trong những hàm được biết đến nhiều nhất trong thế giới bảng tính. Tuy nhiên, những người dùng Excel có kinh nghiệm hầu như đều chuyển sang dùng INDEX MATCH, một tổ hợp hai hàm linh hoạt hơn, đáng tin cậy hơn, và có thể giải quyết những bài toán mà VLOOKUP đơn giản là không thể. Bài viết này giải thích cụ thể tại sao, kèm theo cú pháp thực tế, ví dụ minh họa và hướng dẫn thực hành mà bạn có thể áp dụng ngay.

INDEX và MATCH Là Gì?

Trước khi kết hợp chúng lại, hãy tìm hiểu từng hàm riêng lẻ.

Hàm INDEX

INDEX trả về giá trị của một ô tại vị trí xác định trong một vùng hoặc mảng dữ liệu.

=INDEX(array, row_num, [col_num])

• array — vùng ô cần tra cứu bên trong
• row_num — vị trí hàng trong vùng đó
• col_num — (tùy chọn) vị trí cột trong vùng đó

Ví dụ, =INDEX(A1:A10, 3) trả về giá trị ở hàng thứ ba của cột A, từ hàng 1 đến hàng 10.

Hàm MATCH

MATCH tìm kiếm một giá trị trong một vùng và trả về số thứ tự vị trí của nó — không phải bản thân giá trị đó, mà là con số cho biết nó nằm ở đâu.

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value — giá trị bạn đang tìm kiếm
• lookup_array — một hàng hoặc cột đơn để tìm kiếm trong đó
• match_type — dùng 0 để khớp chính xác (phổ biến nhất), 1 cho nhỏ hơn, -1 cho lớn hơn

Ví dụ, nếu A1:A5 chứa {Apple, Banana, Cherry, Date, Fig}, thì =MATCH("Cherry", A1:A5, 0) trả về 3 vì Cherry là phần tử thứ ba.

Kết Hợp INDEX và MATCH

Sức mạnh thực sự xuất hiện khi bạn lồng MATCH vào bên trong INDEX. Thay vì nhập cứng số hàng, bạn để MATCH tính toán động:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Công thức này yêu cầu Excel: "Tìm vị trí của giá trị tra cứu trong vùng tra cứu, rồi trả về giá trị tương ứng từ vùng kết quả." Hai vùng phải có cùng kích thước và được căn chỉnh cùng chiều.

Hướng Dẫn Thực Hành

Hãy hình dung một bảng kiểm kê sản phẩm với cấu trúc như sau:

Mã Sản Phẩm	Tên Sản Phẩm	Danh Mục	Đơn Giá	Tồn Kho
P-101	Wireless Mouse	Electronics	$29.99	142
P-102	USB-C Hub	Electronics	$49.99	87
P-103	Desk Lamp	Office	$34.99	55
P-104	Notebook A5	Stationery	$8.99	310
P-105	Ergonomic Chair	Furniture	$299.00	12

Dữ liệu nằm trong vùng A2:E6, với tiêu đề ở hàng 1. Bạn muốn tra cứu Đơn Giá của sản phẩm có mã được nhập vào ô H2.

Với INDEX MATCH, công thức trong H3 sẽ là:

=INDEX($D$2:$D$6, MATCH(H2, $A$2:$A$6, 0))

Từng bước thực hiện:

1. MATCH tìm kiếm trong cột A (Mã Sản Phẩm) giá trị đang có trong H2.
2. Hàm trả về vị trí hàng — ví dụ, 3 nếu H2 chứa "P-103".
3. INDEX sau đó trả về giá trị ở hàng 3 của cột D (Đơn Giá), tức là $34.99.

Lưu ý cách sử dụng tham chiếu ô tuyệt đối với dấu đô la. Khóa các vùng đảm bảo công thức hoạt động đúng khi bạn sao chép sang các ô khác.

Tại Sao INDEX MATCH Vượt Trội Hơn VLOOKUP

Nếu bạn đã biết VLOOKUP qua hướng dẫn đầy đủ về VLOOKUP của chúng tôi, bạn hiểu điểm mạnh của nó. Nhưng VLOOKUP có những hạn chế nổi tiếng mà INDEX MATCH giải quyết một cách gọn gàng.

1. Tra Cứu Sang Trái — VLOOKUP Không Thể, INDEX MATCH Có Thể

VLOOKUP chỉ tìm kiếm trong cột ngoài cùng bên trái của bảng và trả về giá trị nằm bên phải. Nếu cột tra cứu của bạn nằm bên phải cột kết quả, VLOOKUP sẽ thất bại. INDEX MATCH không có hạn chế như vậy — vùng kết quả và vùng tra cứu hoàn toàn độc lập, vì thế bạn có thể trả về giá trị từ bất kỳ cột nào, kể cả những cột nằm bên trái cột tìm kiếm.

2. Thay Đổi Số Thứ Tự Cột Không Làm Hỏng Công Thức

VLOOKUP dùng số thứ tự cột được nhập cứng (ví dụ: cột thứ ba). Chèn hoặc xóa một cột sẽ khiến con số đó sai, âm thầm trả về dữ liệu không chính xác. Vì INDEX MATCH tham chiếu trực tiếp đến các vùng thực, việc chèn cột không bao giờ làm hỏng công thức.

3. Nhanh Hơn Trên Tập Dữ Liệu Lớn

VLOOKUP quét toàn bộ mảng bảng trong mỗi lần tính toán. INDEX MATCH chỉ đánh giá cột tra cứu cụ thể và cột kết quả cụ thể, điều này nhanh hơn đáng kể trong các tệp có hàng chục nghìn hàng.

4. Tra Cứu Hai Chiều (2D)

Bạn có thể lồng hai hàm MATCH — một cho hàng, một cho cột — để tạo tra cứu hai chiều mà VLOOKUP không thể thực hiện nếu không có công thức hỗ trợ:

=INDEX(B2:E6, MATCH(H2, A2:A6, 0), MATCH(H3, B1:E1, 0))

Ở đây, MATCH(H2, A2:A6, 0) tìm hàng đúng và MATCH(H3, B1:E1, 0) tìm cột đúng. Thay đổi bất kỳ ô đầu vào nào và công thức lập tức cập nhật theo. Điều này đặc biệt hữu ích cho bảng điều khiển doanh số khi bạn cần lấy số liệu theo nhiều chiều khác nhau.

Xử Lý Lỗi Một Cách Khéo Léo

Khi không tìm thấy kết quả khớp, MATCH trả về lỗi #N/A. Hãy bọc toàn bộ công thức INDEX MATCH trong IFERROR để hiển thị thông báo thân thiện với người dùng thay thế:

=IFERROR(INDEX($D$2:$D$6, MATCH(H2, $A$2:$A$6, 0)), "Product not found")

Điều này đặc biệt quan trọng trong các tệp làm việc chung hoặc biểu mẫu mà người dùng cuối nhập giá trị tìm kiếm — xử lý lỗi gọn gàng giúp tránh nhầm lẫn và bực bội. Kết hợp với xác thực dữ liệu trên ô nhập liệu để giới hạn mục nhập vào danh sách hợp lệ, và bạn sẽ có một công cụ tra cứu mạnh mẽ, chống sai sót từ người dùng.

Tra Cứu Nhiều Điều Kiện Với INDEX MATCH

Một trong những tình huống tra cứu được yêu cầu nhiều nhất là khớp theo nhiều điều kiện. Giả sử bạn muốn tìm Đơn Giá mà cả Danh Mục là "Electronics" VÀ Tồn Kho nhỏ hơn 100. Bạn có thể thực hiện điều này bằng phiên bản mảng của INDEX MATCH.

=INDEX($D$2:$D$6, MATCH(1, ($C$2:$C$6="Electronics")*($E$2:$E$6<100), 0))

Trong các phiên bản Excel cũ (trước 365), nhấn Ctrl + Shift + Enter để nhập công thức này dưới dạng công thức mảng — Excel sẽ bọc nó trong dấu ngoặc nhọn {}. Trong Excel 365 và Excel 2021, mảng động xử lý điều này tự động, vì vậy chỉ cần nhấn Enter thông thường là đủ.

Cách hoạt động: mỗi điều kiện tạo ra một mảng giá trị TRUE/FALSE (1 và 0). Nhân chúng lại với nhau tạo ra một mảng mới chỉ bằng 1 khi cả hai điều kiện đều là TRUE. MATCH sau đó tìm số 1 đầu tiên, và INDEX trả về giá cả tương ứng.

INDEX MATCH So Với XLOOKUP

Excel 365 đã giới thiệu XLOOKUP, đơn giản hóa nhiều tác vụ tra cứu chỉ với một hàm duy nhất. XLOOKUP rất xuất sắc cho các tra cứu đơn giản và nó cũng hỗ trợ tra cứu sang trái một cách tự nhiên. Tuy nhiên, INDEX MATCH vẫn còn phù hợp vì một số lý do:

• Hoạt động trên mọi phiên bản Excel, bao gồm Excel 2010, 2013, 2016 và 2019 — bất cứ nơi nào bạn có thể chia sẻ tệp.
• Mẫu tra cứu hai chiều với các hàm MATCH lồng nhau vẫn ngắn gọn hơn các phương án XLOOKUP thay thế.
• Nhiều mô hình tài chính chuyên nghiệp và biểu mẫu kiểm toán được xây dựng trên INDEX MATCH, vì vậy việc đọc và bảo trì chúng đòi hỏi phải quen với tổ hợp này.

Hiểu rõ INDEX MATCH cũng là nền tảng khi làm các tác vụ nâng cao hơn như tạo bảng điều khiển động trong Excel, nơi các công thức tra cứu cung cấp dữ liệu cho biểu đồ và bảng tóm tắt tự động cập nhật.

Mẹo Nhanh Để Thành Công Với INDEX MATCH

• Luôn dùng 0 làm match_type để khớp chính xác, trừ khi bạn có lý do cụ thể để dùng khớp gần đúng.
• Khóa các vùng bằng tham chiếu tuyệt đối ($) để công thức sao chép đúng qua các hàng hoặc cột.
• Giữ vùng tra cứu và vùng kết quả cùng độ dài — kích thước không khớp sẽ gây ra kết quả sai.
• Đặt tên cho vùng bằng cách dùng Hộp Tên (bên trái thanh công thức) để dễ đọc: =INDEX(UnitPrices, MATCH(H2, ProductIDs, 0)) dễ kiểm tra hơn nhiều so với tham chiếu ô thông thường.
• Kết hợp với IF và SUMIFS để tra cứu có điều kiện — xem hướng dẫn về SUMIF và SUMIFS để biết thêm các kỹ thuật công thức điều kiện liên quan.

Nhận Công Thức INDEX MATCH Tức Thì

Nếu bạn đang đứng trước một yêu cầu tra cứu phức tạp — nhiều điều kiện, bố cục bảng không chuẩn, hoặc tham chiếu giữa các trang tính — bạn có thể mô tả những gì bạn cần bằng tiếng Anh thông thường với GPTExcel và nhận ngay công thức INDEX MATCH sẵn dùng trong vài giây, bao gồm các tham chiếu tuyệt đối chính xác và xử lý lỗi. Điều này loại bỏ sự đoán mò và giúp bạn có công thức hoạt động ngay mà không cần thử đi thử lại thủ công.

Để biết thêm các kỹ thuật viết công thức rộng hơn được hỗ trợ bởi AI, bài viết về sử dụng ChatGPT để viết công thức Excel đề cập chi tiết quy trình này.

Câu Hỏi Thường Gặp

INDEX MATCH có luôn tốt hơn VLOOKUP không?

Đối với phần lớn các trường hợp sử dụng chuyên nghiệp, câu trả lời là có. INDEX MATCH xử lý được tra cứu sang trái, không bị hỏng khi chèn cột, và hỗ trợ tra cứu hai chiều cũng như nhiều điều kiện. VLOOKUP đơn giản hơn để viết cho các tra cứu cơ bản sang phải, nhưng những hạn chế của nó sẽ gây khó chịu khi dữ liệu của bạn ngày càng phức tạp.

Tôi có cần nhấn Ctrl+Shift+Enter cho INDEX MATCH không?

Chỉ khi bạn đang dùng phiên bản mảng nhiều điều kiện của công thức trong Excel 2019 trở về trước. Các công thức INDEX MATCH một điều kiện tiêu chuẩn được nhập bằng phím Enter thông thường trong mọi phiên bản Excel. Trong Excel 365 và Excel 2021 với mảng động, ngay cả các phiên bản nhiều điều kiện cũng không cần phím tắt mảng.

Điều gì xảy ra nếu có các giá trị trùng lặp trong cột tra cứu của tôi?

MATCH luôn trả về vị trí của kết quả khớp đầu tiên mà nó tìm thấy. Nếu cột tra cứu của bạn có các giá trị trùng lặp và bạn cần lấy dữ liệu cho từng lần xuất hiện, hãy cân nhắc dùng cột hỗ trợ với các khóa được nối, hoặc dùng Power Query — được đề cập trong hướng dẫn Power Query của chúng tôi — để định hình lại dữ liệu trước khi áp dụng tra cứu.

INDEX MATCH có thể tra cứu giá trị qua các trang tính khác nhau không?

Có. Chỉ cần thêm tên trang tính vào tham chiếu vùng của bạn. Ví dụ: =INDEX(Sheet2!$D$2:$D$100, MATCH(H2, Sheet2!$A$2:$A$100, 0)). Công thức hoạt động giống hệt nhau dù các vùng nằm trên cùng một trang tính hay một trang tính khác trong cùng một tệp.