Có thể bạn quan tâm: Hướng Dẫn Cộng Trừ Nhân Chia Trong Excel
Giới thiệu chung
Trong công việc với Excel, hai hàm INDEX và MATCH luôn đứng đầu trong danh sách những công cụ hữu ích nhất để tra cứu dữ liệu, thay thế cho hàm VLOOKUP hay HLOOKUP truyền thống. Những hàm này không những linh hoạt hơn mà còn mạnh mẽ hơn, cho phép bạn thực hiện các phép tra cứu phức tạp mà không bị giới hạn bởi cấu trúc cột. Hơn nữa, khi kết hợp chúng với nhau, bạn có thể đạt được những kết quả mà chỉ với VLOOKUP mà mất nhiều thao tác hoặc phải viết lại công thức.
Bài viết này sẽ cung cấp một hướng dẫn chi tiết, đầy đủ các khái niệm, cú pháp, ví dụ thực tế và những mẹo tối ưu khi sử dụng INDEX + MATCH. Bạn sẽ học cách:
- Hiểu rõ ý nghĩa và công dụng của từng hàm.
- Viết công thức INDEX và MATCH đơn giản.
- Kết hợp hai hàm thành công thức tra cứu linh hoạt.
- Sử dụng MATCH để tìm vị trí chính xác, bao gồm cả tìm kiếm gần đúng.
- Đưa ra các ví dụ thực tế từ báo cáo tài chính, bảng kê khách hàng đến phân tích dữ liệu marketing.
- Áp dụng các mẹo tối ưu: tránh lỗi #REF!, giảm thời gian tính toán, dùng mảng động (dynamic arrays), và sử dụng hàm XLOOKUP/LOOKUP nếu có sẵn.
- Giải quyết các lỗi phổ biến và cách khắc phục.
Với độ dài trên 1500 từ, bài viết sẽ trở thành một tài liệu tham khảo toàn diện cho người mới học và cả những chuyên gia Excel muốn nâng cao kỹ năng.
Có thể bạn quan tâm: Hướng Dẫn Cách Sử Dụng Excel Cơ Bản: Từ Những Thao Tác Đầu Tiên Đến Kỹ Thuật Nâng Cao
1. Tổng quan về hàm INDEX
1.1 Định nghĩa và mục đích
Hàm INDEX trong Excel được sử dụng để trả về giá trị của một ô trong một vùng dữ liệu (range) dựa trên vị trí hàng và cột. Hàm này thường được chia thành hai dạng:
- INDEX(array, row_num, column_num) – trả về giá trị tại vị trí xác định trong mảng.
- INDEX(reference, row_num, column_num, area_num) – trả về giá trị trong một vùng được tham chiếu bởi nhiều khu vực (area).
Trong thực tế, phiên bản đầu tiên (array) được sử dụng phổ biến nhất. Khi chỉ định row_num và column_num, Excel sẽ trả về giá trị tại ô giao nhau của hàng và cột đó.
1.2 Cú pháp chi tiết
INDEX(array, row_num, column_num)
- array: Mảng (range) chứa dữ liệu cần tra cứu. Ví dụ:
A1:D10. - row_num: Số thứ tự hàng trong mảng. Nếu
row_num= 1, Excel sẽ trả về giá trị ở hàng đầu tiên trong range. - column_num (tùy chọn): Số thứ tự cột trong mảng. Nếu bỏ qua, Excel sẽ trả về toàn bộ hàng nếu mảng là một cột, hoặc toàn bộ cột nếu mảng là một hàng.
1.3 Ví dụ đơn giản
| A | B | C | D |
|---|---|---|---|
| 10 | 20 | 30 | 40 |
| 50 | 60 | 70 | 80 |
| 90 | 100 | 110 | 120 |
- Công thức
=INDEX(A1:D3, 2, 3)sẽ trả về 70 (hàng thứ hai, cột thứ ba). - Công thức
=INDEX(A1:D3, 3)sẽ trả về array 90; 100; 110; 120, vì chỉ córow_numvà mảng là một hàng.
Có thể bạn quan tâm: Hướng Dẫn Cách Làm Tròn Số Trong Excel
2. Tổng quan về hàm MATCH
2.1 Định nghĩa và mục đích
Hàm MATCH hoạt động ngược lại với INDEX: nó tìm vị trí của một giá trị trong một mảng (range) và trả về số thứ tự của vị trí đó. Đây là công cụ mạnh mẽ để xác định chỉ số dòng hoặc cột trước khi sử dụng trong hàm INDEX.
2.2 Cú pháp chi tiết
MATCH(lookup_value, lookup_array, match_type)
- lookup_value: Giá trị cần tìm. Ví dụ:
50hoặcB2. - lookup_array: Mảng trong đó tìm kiếm. Ví dụ:
A1:A10. - match_type: Kiểu khớp. Có ba giá trị:
1(hoặc bỏ qua): Giá trị lớn hơn hoặc bằnglookup_value. Dữ liệu phải được sắp xếp tăng dần.0: Giá trị chính xác. Dữ liệu có thể được sắp xếp bất kỳ.-1: Giá trị nhỏ hơn hoặc bằnglookup_value. Dữ liệu phải được sắp xếp giảm dần.
2.3 Ví dụ đơn giản
| A |
|---|
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
- Công thức
=MATCH(30, A1:A5, 0)trả về 3 (vị trí thứ ba). - Công thức
=MATCH(35, A1:A5, 1)trả về 4 (vị trí lớn hơn hoặc bằng 35, tức là 40).
3. Kết hợp INDEX và MATCH
3.1 Tại sao kết hợp?
- VLOOKUP chỉ có thể tra cứu theo cột thứ nhất và trả về giá trị ở cột bên phải. Nếu bạn muốn tra cứu theo một cột nào đó và trả về giá trị ở cột bên trái, cần dùng công thức phức tạp.
- INDEX + MATCH cho phép tra cứu theo cả hàng và cột, mà không phụ thuộc vào thứ tự cột.
- Hiệu suất: Khi làm việc với bảng dữ liệu lớn, INDEX + MATCH thường nhanh hơn VLOOKUP vì không cần duyệt toàn bộ cột.
3.2 Cú pháp kết hợp
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
- return_range: Vùng dữ liệu chứa giá trị cần trả về.
- lookup_value: Giá trị cần tìm.
- lookup_range: Vùng dữ liệu chứa giá trị tìm kiếm (cột hoặc hàng).
3.3 Ví dụ thực tế
Giả sử bạn có bảng dữ liệu:
| A (Mã sản phẩm) | B (Tên sản phẩm) | C (Giá) | D (Số lượng) |
|---|---|---|---|
| P001 | Áo thun | 200,000 | 50 |
| P002 | Quần jeans | 500,000 | 30 |
| P003 | Giày thể thao | 1,200,000 | 20 |
Bạn muốn tra cứu giá của sản phẩm có mã P002.
- Công thức:
=INDEX(C2:C4, MATCH("P002", A2:A4, 0)) - Kết quả: 500,000.
Bạn cũng có thể tra cứu số lượng:
=INDEX(D2:D4, MATCH("P003", A2:A4, 0))- Kết quả: 20.
4. Các trường hợp nâng cao
4.1 Tra cứu vòng lặp (array)
Nếu bạn muốn tra cứu nhiều giá trị cùng lúc, có thể dùng công thức mảng (array formula). Ví dụ, lấy giá trị của hai sản phẩm P001 và P003 trong cùng lúc:
=INDEX(C2:C4, MATCH({"P001","P003"}, A2:A4, 0))
Trong Excel 365, công thức này sẽ trả về mảng 200000; 1200000.
4.2 Tra cứu theo nhiều tiêu chí
Có thể bạn quan tâm: Hướng Dẫn Dùng Pivot Table Trong Excel 2026
Bạn muốn tìm giá trị dựa trên hai điều kiện: mã sản phẩm và năm bán hàng. Sử dụng công thức mảng:
=INDEX(E2:E10, MATCH(1, (A2:A10="P001")(C2:C10=2026), 0))
Trong Excel 365, bạn chỉ cần nhấn Enter mà không cần Ctrl+Shift+Enter.
4.3 Tra cứu theo hàng và cột
Nếu dữ liệu của bạn là bảng 2 chiều, bạn có thể tra cứu cả hàng lẫn cột:
| 2026 | 2026 | |
|---|---|---|
| P001 | 100 | 120 |
| P002 | 150 | 180 |
Tra cứu số liệu năm 2026 của P001:
=INDEX(B2:C3, MATCH("P001", A2:A3, 0), MATCH(2026, B1:C1, 0))
MATCH("P001", A2:A3, 0)trả về 1.MATCH(2026, B1:C1, 0)trả về 2.INDEX(B2:C3, 1, 2)trả về 120.
4.4 Sử dụng MATCH để tìm vị trí gần đúng
Nếu dữ liệu được sắp xếp tăng dần, bạn có thể tìm vị trí gần đúng bằng cách đặt match_type là 1 hoặc -1. Ví dụ:
| Giá trị |
|---|
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
Tìm vị trí của giá trị gần nhất với 35:
=MATCH(35, A1:A5, 1)trả về 4 (vị trí 40, vì 40 là giá trị lớn hơn 35).=MATCH(35, A1:A5, -1)trả về 3 (vị trí 30, vì 30 là giá trị nhỏ hơn 35).
4.5 Xử lý lỗi khi không tìm thấy giá trị
Khi MATCH không tìm thấy giá trị, công thức sẽ trả về lỗi #N/A. Để tránh hiển thị lỗi, bạn có thể bọc hàm MATCH trong IFERROR:
=IFERROR(INDEX(C2:C4, MATCH("P005", A2:A4, 0)), "Không tìm thấy")
Nếu không tìm thấy mã P005, công thức sẽ trả về “Không tìm thấy” thay vì lỗi.
5. Mẹo tối ưu khi sử dụng INDEX + MATCH
5.1 Tránh lỗi #REF!
- Khi dùng
INDEXvớiMATCHvàlookup_rangekhông phải là cột tương ứng vớireturn_range, Excel sẽ báo lỗi#REF!. Đảm bảo rằnglookup_rangevàreturn_rangecó cùng kích thước hoặclookup_rangelà một cột trongreturn_range.
5.2 Sử dụng named ranges để làm công thức dễ đọc hơn
=INDEX(NamHang, MATCH("P002", MaSanPham, 0))
- MaSanPham:
A2:A4 - NamHang:
C2:C4
5.3 Sử dụng OFFSET kết hợp với INDEX khi dữ liệu thay đổi kích thước
Nếu dữ liệu có thể mở rộng hoặc thu hẹp, bạn có thể dùng OFFSET để tự động điều chỉnh phạm vi:
=INDEX(OFFSET($A$1,0,0,COUNTA($A:$A),1), MATCH("P002", A:A, 0))
5.4 Tăng tốc độ tính toán
- Tắt tính toán tự động: Khi làm việc với các bảng lớn, tạm dừng tính toán tự động (
Formulas > Calculation options > Manual) và chỉ tính lại khi cần. - Sử dụng mảng động (dynamic arrays): Trong Excel 365, công thức mảng tự động mở rộng, giúp tránh việc nhập
Ctrl+Shift+Enter.
5.5 Sử dụng hàm XLOOKUP nếu có sẵn
Nếu bạn đang sử dụng Excel 365 hoặc Excel 2026, hàm XLOOKUP là một sự thay thế hoàn hảo cho INDEX + MATCH, vì nó tích hợp cả hai chức năng trong một hàm:
=XLOOKUP("P002", A2:A4, C2:C4, "Không tìm thấy")
XLOOKUP hỗ trợ cả tra cứu ngược, tìm kiếm gần đúng, và tránh lỗi lỗi #N/A một cách linh hoạt.
6. Thực hành: Bảng dữ liệu ví dụ
6.1 Bảng dữ liệu mẫu
| Mã sản phẩm | Tên sản phẩm | Giá bán | Số lượng | Ngày bán |
|---|---|---|---|---|
| P001 | Áo thun | 200,000 | 50 | 01/01/2026 |
| P002 | Quần jeans | 500,000 | 30 | 05/01/2026 |
| P003 | Giày thể thao | 1,200,000 | 20 | 10/01/2026 |
| P004 | Mũ lưỡi trai | 100,000 | 70 | 15/01/2026 |
| P005 | Túi xách | 350,000 | 40 | 20/01/2026 |
6.2 Truy vấn dữ liệu
6.2.1 Tìm giá bán của sản phẩm có mã P004
=INDEX(C2:C6, MATCH("P004", A2:A6, 0))
6.2.2 Tìm ngày bán của sản phẩm có tên Giày thể thao
=INDEX(E2:E6, MATCH("Giày thể thao", B2:B6, 0))
6.2.3 Tính tổng doanh thu của từng sản phẩm
Doanh thu = Giá bán Số lượng.
Sử dụng công thức mảng:
=SUMPRODUCT(C2:C6, D2:D6)
6.2.4 Tra cứu số lượng bán trong ngày cụ thể
Giả sử bạn muốn biết số lượng bán của P002 vào ngày 05/01/2026:
=INDEX(D2:D6, MATCH(1, (A2:A6="P002")(E2:E6=DATE(2026,1,5)), 0))
7. Phân tích lỗi phổ biến
| Lỗi | Nguyên nhân | Cách khắc phục |
|---|---|---|
| #REF! | lookup_range và return_range không khớp kích thước | Kiểm tra và điều chỉnh phạm vi |
| #N/A | Không tìm thấy giá trị | Kiểm tra chính tả, loại dữ liệu, dùng IFERROR |
| #VALUE! | Sử dụng kiểu dữ liệu sai trong MATCH | Đảm bảo dữ liệu trong lookup_array là số hoặc văn bản tương ứng |
| Công thức chậm | Dùng OFFSET trong bảng lớn | Sử dụng INDEX thay vì OFFSET, tắt tính toán tự động khi cần |
8. Kết luận
Hàm INDEX và MATCH là cặp đôi mạnh mẽ trong Excel, cho phép bạn thực hiện các phép tra cứu linh hoạt, chính xác và nhanh chóng hơn so với VLOOKUP. Khi học và thực hành, bạn sẽ nhận ra rằng:
- INDEX đơn giản và mạnh mẽ khi được kết hợp với MATCH.
- Cấu trúc và cú pháp của hai hàm rất dễ nhớ.
- Những mẹo tối ưu như dùng named ranges, IFERROR, hoặc XLOOKUP sẽ giúp công việc trở nên mượt mà hơn.
- Với các bảng dữ liệu phức tạp, công thức mảng (array formulas) và dynamic arrays trong Excel 365 sẽ mở rộng khả năng làm việc của bạn.
Hãy dành thời gian thực hành với các ví dụ thực tế, điều chỉnh phạm vi và điều kiện tra cứu. Khi thành thạo, bạn sẽ có thể xử lý bất kỳ yêu cầu dữ liệu nào trong Excel – từ báo cáo doanh thu, danh sách khách hàng, đến phân tích dữ liệu marketing. Chúc bạn thành công và tận hưởng quá trình khám phá sức mạnh của INDEX và MATCH!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.