Hàm INDEX và MATCH trong Excel

Trong Excel, việc tìm kiếm và trích xuất dữ liệu là một trong những thao tác quan trọng giúp người dùng xử lý thông tin nhanh chóng và chính xác. Hàm INDEX và MATCH trong Excel là một bộ đôi mạnh mẽ, giúp thay thế và nâng cấp khả năng tìm kiếm của hàm VLOOKUP hoặc HLOOKUP truyền thống. 

Bài viết này Kế toán Lê Ánh sẽ giúp bạn hiểu rõ cách sử dụng hàm INDEX và MATCH trong Excel, từ cú pháp cơ bản đến các ứng dụng nâng cao, giúp bạn làm chủ công cụ này trong công việc hàng ngày. 

1. Vì sao nên sử dụng INDEX và MATCH thay vì VLOOKUP/HLOOKUP?

VLOOKUP và HLOOKUP bị giới hạn trong việc tìm kiếm theo hàng hoặc cột cố định, trong khi INDEX và MATCH có thể tìm kiếm cả theo hàng và cột.

INDEX và MATCH hoạt động hiệu quả ngay cả khi bảng dữ liệu thay đổi cấu trúc, trong khi VLOOKUP sẽ bị lỗi nếu có sự thay đổi về vị trí cột.

Tốc độ xử lý của INDEX và MATCH tốt hơn với dữ liệu lớn, đặc biệt khi cần tìm kiếm nhiều lần trong bảng tính có dung lượng lớn.

INDEX và MATCH có thể thực hiện tìm kiếm động, giúp dễ dàng trích xuất dữ liệu theo nhiều điều kiện khác nhau.

>>> Xem thêm: So sánh hàm VLOOKUP và HLOOKUP - Khi nào nên sử dụng?

2. Ứng dụng của hàm INDEX và MATCH trong xử lý dữ liệu

Tìm kiếm dữ liệu theo hàng và cột linh hoạt, không bị giới hạn bởi vị trí cột/hàng.

Dễ dàng kết hợp với các hàm khác như IF, IFERROR, SUM để tạo ra các công thức tìm kiếm thông minh.

Ứng dụng trong báo cáo tài chính, quản lý kho, phân tích dữ liệu giúp tìm kiếm thông tin nhanh và chính xác hơn.

Thích hợp cho dữ liệu động, dễ dàng điều chỉnh khi có thay đổi về bảng dữ liệu mà không bị lỗi công thức.

3. Cách sử dụng hàm INDEX trong Excel

Hàm INDEX trong Excel là hàm trả về mảng giúp lấy các giá trị tại một ô nào đó giao giữa cột và dòng. Cụ thể hơn là trong một bảng dữ liệu cho trước, nếu biết được vị trí của một phần tử nào đó thì hàm INDEX sẽ giúp ta lấy giá trị của ô đó.

a. Hàm INDEX cơ bản

Công thức: =INDEX(array;row_num;[column_num])

Trong đó: 

array: vùng ô hoặc một hàng số mảng nào đó;

row_num: số thứ tự hàng trong mảng chứa giá trị được trả về;

column_num: số thứ tự cột trong mảng chứa giá trị được trả về.

Ví dụ: Sử dụng hàm INDEX để tìm giá trị của phần tử dòng thứ 4, cột thứ 2 trong mảng

Bước 1: Nhập công thức: =INDEX(B3:F10,4,2) vào ô tham chiếu

Trong đó: 

• B3:B10 là vùng chứa giá trị cần lấy

• 4 là số thứ tự của hàng chứa giá trị trả về có trong mảng

• 2 là số thứ tự của cột chứa giá trị trả về có trong mảng

Bước 2: Enter để nhận kết quả hàm.

b. Hàm INDEX nâng cao

Công thức: =INDEX(reference;row_num;[column_num],[area_num])

Trong đó:

reference: vùng tham chiếu (có thể kết hợp nhiều mảng với nhau);

row_num: số thứ tự hàng trong mảng chứa giá trị được trả về;

column_num: số thứ tự cột trong mảng chứa giá trị được trả về;

area_num: số thứ tự của mảng trong vùng tham chiếu reference sẽ trả về giá trị. Nếu

area_num được bỏ qua thì hàm INDEX mặc định dùng vùng 1.

>>> Xem thêm: Tổng hợp các hàm excel cho kế toán

4. Cách sử dụng hàm Match trong Excel

Công thức: =MATCH(lookup_value, lookup_array, [match_type])

Trong đó:

Lookup_value (đối số bắt buộc): Là giá trị bạn muốn so khớp trong lookup_array. Nó có thể là một số, văn bản hoặc giá trị logic được nhập theo cách thủ công hoặc được tham chiếu qua tham chiếu ô. 

Lookup_array (đối số bắt buộc): Là phạm vi ô cần xem qua. Nó có thể là một hàng hoặc một cột.

match_type có thể là -1, 0 hoặc 1. Nó chỉ định cách lookup_value được so khớp với các giá trị trong lookup_array. 1 là giá trị mặc định nếu đối số này bị bỏ qua.

Sử dụng 1 hoặc -1 cho những thời điểm bạn cần chạy tra cứu gần đúng theo thang điểm, như khi xử lý các con số và khi tính gần đúng là ổn. Nhưng hãy nhớ rằng nếu bạn không chỉ định match_type, 1 sẽ là mặc định, điều này có thể làm sai lệch kết quả nếu bạn thực sự muốn một kết quả khớp chính xác.

5. Kết hợp INDEX và MATCH để tìm kiếm dữ liệu

Ví dụ 1: Tìm giá trị “Kết quả” của bạn Hoàng Văn D trong bảng dưới

Nhập công thức: =INDEX(A13:D18;MATCH("Hoàng Văn D";A13:A18;0);4)

Mô tả:

Hàm MATCH sẽ dò tìm “Hoàng Văn D” trong phạm vi A13:A18 và trả về kết quả là 5 vì nó nằm ở vị trí 5 trong mảng A13:A18.

Hàm INDEX lúc này có thể viết gọn là =INDEX(A13:D18;5;4) sẽ trả về giá trị giao nhau tại hàng 5 (chứa tên học sinh) và cột 4 (chứa kết quả) trong mảng A13:D18 là “Đậu”

Ví dụ 2: Giả sử ta có đơn giá theo Sản phẩm và Hãng sản xuất trong Bảng 2 (B15:E18). Dựa theo bảng 2, ta cần điền đơn giá vào Bảng 1 (B3:D12).

Cách ta sử dụng hàm INDEX kết hợp hàm MATCH để dò tìm giá trị cho ô D4 như sau:

Bước 1: Sử dụng hàm MATCH để xác định vị trí hàng tương ứng của sản phẩm CDRom trong phạm vi B15:B18 của Bảng 2: 

=MATCH(B4,$B$15:$B$18,0)

Kết quả 4 tương ứng với hàng số 4 trong Bảng 2.

Bước 2: Sử dụng hàm MATCH để xác định vị trí cột tương ứng của hãng sản xuất Samsung trong phạm vi B15:E15:

=MATCH(C4,$B$15:$E$15,0)

Kết quả 2 tương ứng với cột số 2 trong Bảng 2.

Bước 3: Sử dụng hàm INDEX kết hợp với 2 hàm MATCH ở trên để trả về giá trị dựa theo hàng và cột trong Bảng 2:

=INDEX($B$15:$E$18,MATCH(B4,$B$15:$B$18,0),MATCH(C4,$B$15:$E$15,0))

Kết quả trả về giá trị tương ứng của hàng 4 (Hàng Mouse), cột 2 (Cột Samsung) trong Bảng 2 là 5.

Sau đó, ta copy công thức cho các ô khác để hoàn thành.

6. Lưu ý khi sử dụng hàm INDEX và MATCH

Dù hàm INDEX và MATCH mang lại nhiều lợi ích vượt trội, nhưng để sử dụng hiệu quả và tránh các lỗi phổ biến, bạn cần lưu ý một số điểm quan trọng sau:

- Nên sử dụng MATCH với kiểu tìm kiếm chính xác (MATCH type = 0)

Hàm MATCH có 3 kiểu tìm kiếm, được xác định bằng đối số match_type:

• 1 (tìm kiếm gần đúng – giá trị nhỏ nhất lớn hơn hoặc bằng giá trị tìm kiếm, dữ liệu cần sắp xếp tăng dần).

• 0 (tìm kiếm chính xác, không yêu cầu sắp xếp dữ liệu).

• -1 (tìm kiếm gần đúng – giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tìm kiếm, dữ liệu cần sắp xếp giảm dần).

Khi sử dụng 1 hoặc -1, nếu dữ liệu không được sắp xếp đúng cách, kết quả trả về có thể sai.Nếu bảng dữ liệu thay đổi theo thời gian và bạn không chắc chắn về thứ tự sắp xếp, việc sử dụng 0 giúp đảm bảo kết quả chính xác nhất.

Ví dụ: =MATCH(500, A2:A10, 0)

Công thức trên sẽ tìm chính xác giá trị 500 trong phạm vi A2:A10.

- INDEX và MATCH phân biệt chữ hoa chữ thường

Một lưu ý quan trọng là INDEX và MATCH mặc định không phân biệt chữ hoa và chữ thường, điều này có thể gây sai sót trong một số trường hợp cần tìm kiếm chính xác theo định dạng chữ.

Nếu cần phân biệt chữ hoa/thường, bạn phải kết hợp với hàm EXACT.

Cách sử dụng MATCH phân biệt chữ hoa/thường: =MATCH(TRUE, EXACT(A2:A10, "Excel"), 0)

Công thức này sẽ chỉ tìm giá trị "Excel" trùng khớp hoàn toàn với chữ hoa/chữ thường trong phạm vi A2:A10.

- Sử dụng định dạng dữ liệu phù hợp để tránh lỗi khi tìm kiếm

Khi sử dụng MATCH để tìm kiếm số hoặc ngày tháng, hãy đảm bảo định dạng của dữ liệu trong bảng giống với định dạng của giá trị tìm kiếm.

Một số lỗi phổ biến:

• Số trong bảng đang ở dạng text, nhưng bạn tìm kiếm bằng một số dạng number → Kết quả không tìm thấy.

• Ngày tháng được lưu ở dạng text, nhưng bạn tìm kiếm bằng một giá trị ngày hợp lệ.

Cách khắc phục:

• Kiểm tra định dạng dữ liệu bằng cách sử dụng ISTEXT() hoặc ISNUMBER().

• Sử dụng VALUE() để chuyển đổi dữ liệu dạng text sang số trước khi tìm kiếm.

• Đối với ngày tháng, có thể dùng DATEVALUE() để đảm bảo dữ liệu ở đúng định dạng.

- INDEX + MATCH hiệu quả hơn với dữ liệu lớn, nhưng cần viết công thức chính xác để tránh lỗi

Để đảm bảo công thức hoạt động đúng, cần lưu ý:

• MATCH phải tìm kiếm trong một cột duy nhất: Nếu tìm kiếm theo hàng hoặc theo mảng lớn, hãy đảm bảo phạm vi của MATCH chính xác.

• INDEX phải tham chiếu đúng phạm vi dữ liệu: Nếu MATCH trả về vị trí nằm ngoài phạm vi INDEX, công thức sẽ báo lỗi.

Lỗi #N/A hoặc #VALUE!

Nguyên nhân: Sử dụng công thức mảng mà không nhấn Ctrl + Shift + Enter.

Bạn có thể sử dụng giá trị 0 cho row_num hoặc column_num trong hàm INDEX để trả về một mảng giá trị cho toàn bộ cột hoặc hàng tương ứng với điều kiện bạn nhập vào hàm INDEX như một công thức mảng. Tuy nhiên, điều này chỉ áp dụng cho phiên bản mới nhất của Microsoft 365. Trong các phiên bản Excel khác, nếu bạn không tuân theo quy trình sử dụng công thức mảng, Excel sẽ thông báo lỗi.

Cách khắc phục: Trong các phiên bản Excel không phải là phiên bản mới nhất của Microsoft 365, để sử dụng công thức mảng, bạn cần nhập nó dưới dạng công thức mảng kế thừa. Điều này có thể được thực hiện bằng cách chọn vùng dữ liệu đầu ra, sau đó nhập công thức vào ô ở trên cùng bên trái của phạm vi đầu ra. Sau đó, bạn nhấn tổ hợp phím Ctrl + Shift + Enter để hoàn thành quá trình.

Lỗi #REF!

Nguyên nhân: Do các đối số row_num và column_num không trỏ tới bất kỳ một ô nào ở trong mảng đã được khai báo trong công thức, ví dụ mảng khai báo bao gồm 5 cột nhưng bạn nhập vào column_num là 8.

Cách khắc phục: Kiểm tra lại các đối số column_num, row_num xem đã đúng chưa và điều chỉnh lại cho chính xác.

Hàm INDEX và MATCH trong Excel không chỉ giúp bạn tìm kiếm dữ liệu chính xác mà còn mở ra nhiều khả năng xử lý thông tin linh hoạt hơn so với các hàm tìm kiếm truyền thống. Nhờ vào khả năng tra cứu theo cả hàng và cột, không bị giới hạn bởi thứ tự cột như VLOOKUP, bộ hàm này trở thành công cụ hữu ích cho những ai thường xuyên làm việc với dữ liệu phức tạp. Việc thành thạo hàm INDEX và MATCH sẽ giúp bạn tối ưu hóa quy trình làm việc, giảm thiểu sai sót và tiết kiệm thời gian đáng kể!

>>> Xem thêm: 10 Hàm Excel Thường Dùng Trong Kế Toán Mà Bạn Nên Biết

                           Học kế toán Excel ở đâu tốt nhất

                          Cách Dùng Hàm Countif Trong Excel - Ứng Dụng Trong Kế Toán

-------------------------------------------

Kế Toán Lê Ánh - Nơi đào tạo kế toán thực hành uy tín nhất hiện nay, đã tổ chức thành công rất nhiều khóa học nguyên lý kế toán, khóa học kế toán tổng hợp, khóa học kế toán thuế cho người mới bắt đầu, khóa học kế toán thuế chuyên sâu, khóa học phân tích tài chính doanh nghiệp, khóa học chứng chỉ kế toán trưởng, khóa học kế toán cho chủ doanh nghiệp... và hỗ trợ kết nối tuyển dụng cho hàng nghìn học viên.

Nếu như bạn muốn tham gia các khóa học kế toán online/offline của trung tâm Lê Ánh thì có thể liên hệ hotline tư vấn: 0904.84.8855/ Mrs Lê Ánh  

Ngoài các khóa học kế toán thì trung tâm Lê Ánh còn cung cấp các khóa học hành chính nhân sự, khóa học xuất nhập khẩu TPHCM, Hà Nội, online chất lượng tốt nhất hiện nay.

Thực hiện bởi: KẾ TOÁN LÊ ÁNH - TRUNG TÂM ĐÀO TẠO KẾ TOÁN SỐ 1 VIỆT NAM