Cách Dùng Hàm INDEX Trong Excel Để Tìm Kiếm Chính Xác

Hàm INDEX trong Excel là công cụ mạnh mẽ giúp tìm kiếm và trích xuất dữ liệu một cách chính xác. Khi kết hợp với hàm MATCH, nó trở thành phương pháp tìm kiếm đa năng, linh hoạt và hiệu quả trong các bảng dữ liệu lớn. Bài viết này, Kế Toán Lê Ánh sẽ hướng dẫn bạn cách sử dụng hàm INDEX và MATCH một cách chi tiết.

1. Hàm INDEX Là Gì?

Định nghĩa

Hàm INDEX trong Excel là một hàm tìm kiếm và trích xuất dữ liệu. Nó cho phép người dùng lấy một giá trị cụ thể từ một bảng hoặc phạm vi dữ liệu dựa trên chỉ số hàng và cột. Với hàm INDEX, bạn có thể trích xuất chính xác dữ liệu từ các bảng phức tạp mà không cần sắp xếp dữ liệu như khi sử dụng hàm VLOOKUP.

Cú pháp hàm INDEX

Hàm INDEX trong Excel có hai cấu trúc khác nhau vì nó có thể hoạt động theo hai cách riêng biệt, tùy thuộc vào nhu cầu sử dụng:

⭕ Cấu trúc sử dụng array: =INDEX(array; row_num; [column_num])

Trong đó:

• array: Phạm vi dữ liệu mà bạn muốn lấy giá trị.
• row_num: Số hàng của giá trị cần lấy trong phạm vi array.
• column_num: Số cột của giá trị cần lấy (đối với phạm vi nhiều cột). Đối số này là tùy chọn nếu phạm vi chỉ có một cột.

➱ Cấu trúc này thường được sử dụng khi bạn muốn trích xuất giá trị từ một vùng dữ liệu cố định (một dãy ô hoặc một bảng đơn).

⭕ Cấu trúc sử dụng reference: =INDEX(reference; row_num; [column_num]; [area_num])

Trong đó:

• reference: Một hoặc nhiều phạm vi ô khác nhau.
• row_num và column_num: Chỉ số hàng và cột của giá trị cần lấy trong phạm vi.
• area_num: Tùy chọn. Chỉ định phạm vi nào trong reference sẽ được sử dụng nếu reference có nhiều hơn một phạm vi. area_num giúp bạn lựa chọn vùng cụ thể trong các vùng được tham chiếu.

➱ Cấu trúc này hữu ích khi bạn có nhiều phạm vi dữ liệu khác nhau và muốn chọn một trong số đó để lấy giá trị.

Lưu ý: 

• Cấu trúc array phù hợp khi bạn chỉ cần làm việc với một vùng dữ liệu cố định, chẳng hạn như bảng dữ liệu đơn.
• Cấu trúc reference phù hợp khi bạn có nhiều vùng dữ liệu rải rác và muốn xác định vùng cụ thể để lấy dữ liệu.

Sự linh hoạt này của hàm INDEX giúp nó có thể được sử dụng trong nhiều tình huống khác nhau, từ truy xuất dữ liệu trong một vùng đơn giản đến các bảng phức tạp với nhiều vùng dữ liệu.

Tính năng nổi bật của hàm INDEX trong Excel

✔ INDEX có khả năng lấy dữ liệu dựa trên vị trí hàng và cột một cách chính xác mà không phụ thuộcvào việc dữ liệu có được sắp xếp hay không.

✔ INDEX hoạt động nhanh và hiệu quả trong các bảng lớn, giúp bạn dễ dàng truy xuất dữ liệu từ các vị trí cụ thể mà không cần lọc hay sắp xếp lại dữ liệu.

✔ Hàm INDEX có thể được sử dụng cho cả dãy một chiều và hai chiều, cũng như kết hợp với hàm MATCH để tìm kiếm phức tạp hơn.

2. Cách Sử Dụng Hàm INDEX Trong Excel

Hàm INDEX có thể được sử dụng để lấy một giá trị cụ thể tại vị trí xác định trong bảng dữ liệu. Bạn chỉ cần biết vị trí dòng và cột của giá trị đó để hàm INDEX có thể trích xuất thông tin chính xác.

Ví dụ đơn giản

Giả sử bạn có một bảng dữ liệu như sau:

Bạn muốn lấy giá trị ở dòng 3, cột 2, tức là tên "Bình".

Công thức: =INDEX(A1:C5; 3; 2)

• array là A1:C5, phạm vi dữ liệu của bạn.
• row_num là 3, vì giá trị cần lấy nằm ở dòng 3 trong phạm vi đã chọn.
• column_num là 2, vì giá trị cần lấy nằm ở cột 2 trong phạm vi.

Kết quả: Bình

Giải thích: Hàm INDEX tìm đến hàng thứ 3 và cột thứ 2 trong phạm vi A1:C5 và trả về giá trị "Bình".

3. Kết Hợp Hàm INDEX Và MATCH Trong Excel Để Tìm Kiếm Dữ Liệu Chính Xác

Hàm MATCH Là Gì?

Hàm MATCH trong Excel là một hàm dùng để tìm kiếm vị trí của một giá trị trong một phạm vi. Khi kết hợp với hàm INDEX, MATCH giúp tìm kiếm vị trí dựa trên giá trị và sau đó dùng INDEX để trích xuất giá trị từ vị trí đó, tạo thành một công thức linh hoạt và hiệu quả hơn so với VLOOKUP.

Cú pháp hàm MATCH

=MATCH(lookup_value; lookup_array; [match_type])

Trong đó:

• lookup_value: Giá trị cần tìm kiếm.
• lookup_array: Phạm vi tìm kiếm.
• match_type: Kiểu tìm kiếm. Sử dụng 0 để tìm kiếm chính xác.

Cách kết hợp INDEX và MATCH

Sử dụng hàm MATCH để tìm ra vị trí của một giá trị và sau đó dùng INDEX để trích xuất dữ liệu tại vị trí đó.

Công thức kết hợp: =INDEX(array; MATCH(lookup_value; lookup_array; 0); column_num)

Trong đó:

• array: Phạm vi dữ liệu cần trích xuất.
• MATCH(lookup_value; lookup_array; 0): Tìm kiếm giá trị và trả về vị trí hàng trong phạm vi.
• column_num: Số cột chứa giá trị cần lấy.

Lợi ích của việc sử dụng INDEX và MATCH so với VLOOKUP

✔ VLOOKUP chỉ tìm kiếm từ trái sang phải, trong khi INDEX và MATCH có thể tìm kiếm dữ liệu từ bất kỳ cột nào.

✔ Khi làm việc với các bảng lớn, INDEX và MATCH thường hoạt động nhanh hơn.

✔ INDEX và MATCH linh hoạt hơn trong việc xử lý dữ liệu, ngay cả khi vị trí cột trong bảng thay đổi.

Ví dụ minh họa chi tiết

Giả sử bạn có bảng dữ liệu sau:

Bạn muốn tìm tên của nhân viên có ID là 103.

Công thức: =INDEX(B2:B5; MATCH(103; A2:A5; 0))

• B2:B5 là phạm vi chứa dữ liệu Tên cần lấy.
• MATCH(103; A2:A5; 0) tìm kiếm vị trí của giá trị 103 trong cột A (ID).
• MATCH trả về vị trí của 103 là 3, sau đó INDEX sẽ lấy giá trị ở hàng 3 của phạm vi B2:B5.

Kết quả: Cường

Giải thích:

MATCH(103; A2:A5; 0) trả về 3 (ID 103 ở hàng 3 trong phạm vi A2). INDEX(B2:B5; 3) lấy giá trị ở hàng 3 trong phạm vi B2, kết quả là Cường.

Với cách kết hợp này, bạn có thể tìm kiếm giá trị một cách linh hoạt và chính xác trong các bảng dữ liệu phức tạp.

4. Cách Dùng Hàm INDEX MATCH Với Nhiều Điều Kiện

Trong một số trường hợp, bạn cần tìm kiếm dữ liệu theo nhiều điều kiện, chẳng hạn như tìm một nhân viên theo ID và Phòng ban.

Khi đó, hàm INDEX và MATCH có thể được kết hợp dưới dạng mảng để đáp ứng yêu cầu này. Bằng cách sử dụng công thức mảng hoặc kết hợp với hàm IF, bạn có thể tìm kiếm linh hoạt và chính xác hơn so với các hàm tìm kiếm thông thường như hàm VLOOKUP.

Công thức kết hợp INDEX và MATCH với nhiều điều kiện

Để sử dụng hàm INDEX MATCH với nhiều điều kiện, bạn có thể xây dựng công thức mảng như sau:

=INDEX(array; MATCH(1; (condition1_range=condition1_value) * (condition2_range=condition2_value), 0))

• array: Phạm vi dữ liệu mà bạn muốn lấy giá trị.
• condition1_range: Phạm vi chứa điều kiện 1.
• condition1_value: Giá trị mà bạn muốn so sánh trong condition1_range.
• condition2_range: Phạm vi chứa điều kiện 2.
• condition2_value: Giá trị mà bạn muốn so sánh trong condition2_range.
• MATCH(1; …): Hàm MATCH sẽ tìm kiếm vị trí đầu tiên mà các điều kiện đều đúng (kết quả nhân của các điều kiện là 1).

Ví dụ minh họa

Giả sử bạn có bảng dữ liệu về nhân viên như sau:

Yêu cầu: Tìm Tên của nhân viên có ID là 103 và Phòng ban là Kế Toán.

Công thức: =INDEX(B2:B5; MATCH(1; (A2:A5=103) * (C2:C5="Kế Toán"); 0))

• B2:B5 là phạm vi chứa Tên nhân viên mà bạn muốn trả về.
• (A2:A5=103): Kiểm tra điều kiện ID là 103.
• (C2:C5="Kế Toán"): Kiểm tra điều kiện Phòng ban là Kế Toán.
• Hàm MATCH tìm kiếm vị trí đầu tiên mà cả hai điều kiện đều đúng và trả về vị trí tương ứng.

Nhấn Ctrl + Shift + Enter:

Vì đây là công thức mảng, bạn cần nhấn Ctrl + Shift + Enter để kích hoạt công thức. Nếu thực hiện đúng, công thức sẽ được bao quanh bởi {}.

Kết quả: Cường

Giải thích: MATCH kiểm tra từng hàng để xác định xem cả hai điều kiện đều đúng không, và tìm thấy kết quả đầu tiên khớp là ở vị trí của Cường.

5. Kết Hợp Hàm INDEX Và MATCH Với Các Hàm Khác

Kết hợp INDEX, MATCH và SUMIF

Dưới đây là một ví dụ về cách kết hợp INDEX, MATCH, và SUMIF để giải quyết một bài toán phức tạp hơn, khi bạn cần tính tổng doanh thu của một nhân viên dựa trên tên nhân viên và phòng ban mà nhân viên đó làm việc.

Tình huống:

Giả sử bạn có một bảng dữ liệu doanh thu theo nhân viên và phòng ban như sau:

Yêu cầu: Tính tổng doanh thu của nhân viên Cường trong phòng ban Kế Toán.

Cách làm:

Xác định vị trí của dữ liệu: Chúng ta sẽ sử dụng hàm INDEX và MATCH để tìm phạm vi cột Doanh thu cần tính tổng, sau đó kết hợp với hàm SUMIF để tính tổng giá trị của nhân viên Cường trong phòng ban Kế Toán.

Công thức tổng quát: =SUMIF(A2:A7; INDEX(A2:D7; MATCH("Cường"; B2:B7; 0); MATCH("Kế Toán"; C2:C7; 0));D2:D7)

 

Kết quả: 135000

Ngoài ra: Với yêu cầu của ví dụ này bạn cũng có thể chỉ cần sử dụng Hàm SUMIF (Tính tổng có điều kiện) để giải quyết như sau:

Công thức: =SUMIFS(D2:D7 B2:B7, "Cường", C2:C7, "Kế Toán")

Xem thêm: Cách sử dụng hàm SUMIF trong Excel

Sử dụng INDEX, MATCH Trong Google Sheets

Trong Google Sheets, INDEX và MATCH hoạt động tương tự như trong Excel. Tuy nhiên, Google Sheets tự động nhận diện công thức mảng mà không cần nhấn Ctrl + Shift + Enter.

Ví dụ minh họa trong Google Sheets

Dùng lại ví dụ doanh thu trên, để tìm doanh thu đầu tiên của Cường:

=INDEX(C2:C6; MATCH("Cường"; B2:B6; 0))

Kết quả trả về sẽ là 60000, doanh thu đầu tiên của Cường.

Kết hợp INDEX và MATCH với SUMIF, COUNTIF

Ví dụ: Tính tổng doanh thu của phòng Kế Toán.

Công thức:

=SUMIF(C2:C10, "Kế Toán", D2:D10)

Tính tổng doanh thu cho tất cả nhân viên thuộc phòng Kế Toán trong bảng.

6. Những Lưu Ý Khi Sử Dụng Hàm INDEX Và MATCH

⭕ Sắp xếp dữ liệu: Dữ liệu cần sắp xếp đúng nếu bạn sử dụng kiểu tìm kiếm gần đúng với MATCH (match_type là 1 hoặc -1). Nếu sử dụng match_type là 0 (tìm kiếm chính xác), không yêu cầu sắp xếp.

⭕ Không dùng MATCH loại tìm kiếm gần đúng cho dữ liệu không sắp xếp: Sử dụng match_type khác 0 khi dữ liệu không sắp xếp có thể cho kết quả sai.

⭕ Hiệu suất: INDEX và MATCH có xu hướng hoạt động nhanh và linh hoạt hơn VLOOKUP, đặc biệt khi làm việc với bảng dữ liệu lớn hoặc các bảng có nhiều cột.

⭕ Phiên bản Excel: Một số tính năng nâng cao của INDEX và MATCH (như công thức mảng) có thể không tương thích trên các phiên bản Excel cũ.

7. Ứng Dụng Của Hàm INDEX MATCH Trong Kế Toán Và Quản Lý Dữ Liệu

Hàm INDEX và MATCH là công cụ mạnh mẽ trong Excel, đặc biệt hữu ích trong các lĩnh vực như kế toán và quản lý dữ liệu. Những hàm này không chỉ hỗ trợ tìm kiếm và trích xuất thông tin mà còn giúp xử lý và phân tích dữ liệu một cách linh hoạt và chính xác. Dưới đây là các ứng dụng cụ thể của INDEX MATCH trong công việc kế toán và quản lý dữ liệu.

⭕ Tìm kiếm và trích xuất dữ liệu

INDEX và MATCH giúp bạn dễ dàng tìm kiếm và trích xuất các thông tin quan trọng trong bảng dữ liệu lớn, đặc biệt là trong các bảng doanh thu, chi phí, hay danh sách khách hàng.

Ví dụ

Giả sử bạn có bảng dữ liệu chứa thông tin khách hàng, bao gồm mã khách hàng, tên, và doanh thu mua hàng. Khi muốn tìm doanh thu của một khách hàng dựa trên mã khách hàng:

Sử dụng MATCH để xác định vị trí của mã khách hàng.

Dùng INDEX để trích xuất doanh thu tại vị trí đó.

Công thức: =INDEX(C2:C1000; MATCH("KH001"; A2:A1000; 0))

Giải thích:

• C2:C1000 là cột chứa doanh thu.
• MATCH("KH001"; A2:A1000; 0) tìm vị trí của mã khách hàng "KH001" trong cột A.

⭕ Phân tích dữ liệu chi tiết

INDEX và MATCH có thể hỗ trợ phân tích dữ liệu theo nhiều điều kiện, giúp bạn dễ dàng kiểm soát và đưa ra các nhận định chính xác.

Ví dụ: Giả sử bạn có bảng chi phí cho từng phòng ban trong công ty và muốn tính tổng chi phí cho phòng kế toán trong một tháng cụ thể. Bạn có thể dùng công thức mảng kết hợp INDEX và MATCH với nhiều điều kiện.

Công thức: =INDEX(D2:D1000; MATCH(1; (B2:B1000="Kế Toán") * (C2:C1000="Tháng 1"); 0))

Giải thích:

• D2:D1000 là cột chứa chi phí.
• B2:B1000="Kế Toán" và C2:C1000="Tháng 1" là các điều kiện để tìm ra chi phí cho phòng kế toán trong tháng 1.
• MATCH(1; …) tìm vị trí đầu tiên mà cả hai điều kiện đều đúng.

⭕ Lập báo cáo động

INDEX và MATCH cho phép bạn tạo các báo cáo động, giúp báo cáo tự động cập nhật khi dữ liệu thay đổi. Khi bạn cập nhật dữ liệu đầu vào, công thức sẽ tự động điều chỉnh mà không cần thao tác thủ công.

Ví dụ: Bạn có thể dùng INDEX MATCH để tạo báo cáo theo thời gian thực, chẳng hạn như danh sách khách hàng tiềm năng dựa trên doanh thu hàng tháng. Khi dữ liệu doanh thu thay đổi, báo cáo sẽ tự động cập nhật.

Công thức mẫu để trích xuất tên khách hàng có doanh thu cao nhất trong tháng 1:

=INDEX(B2:B1000; MATCH(MAX(D2:D1000); D2:D1000; 0))

Giải thích:

• MAX(D2:D1000) tìm doanh thu cao nhất trong tháng.
• MATCH xác định vị trí của doanh thu cao nhất đó trong cột D.
• INDEX trả về tên khách hàng tại vị trí tìm được.

Xem thêm:

• 10+ Hàm Excel Thường Dùng Trong Kế Toán Mà Bạn Nên Biết
• Cách Dùng Hàm COUNTIF Trong Excel
• Cách sử dụng hàm IF trong excel
• Cách sử dụng hàm SUMIF trong excel
• Hàm Vlookup trong Excel
• Cách sử dụng hàm AVERAGE trong Excel
• Hàm ROUND Trong Excel
• Hàm FREQUENCY Trong Excel
• Cách Dùng Hàm PRODUCT Trong Excel

Hàm INDEX và MATCH là công cụ cực kỳ hữu ích trong tìm kiếm và phân tích dữ liệu. Đặc biệt trong các lĩnh vực như kế toán và quản lý dữ liệu, việc sử dụng thành thạo INDEX MATCH giúp bạn tiết kiệm thời gian và tăng độ chính xác khi xử lý dữ liệu lớn, lập báo cáo, hoặc phân tích chi tiết.

Để nắm vững và ứng dụng hàm INDEX và MATCH linh hoạt trong thực tế, bạn nên thực hành thường xuyên với các bảng dữ liệu đa dạng. Sử dụng công thức này trong các báo cáo hàng ngày sẽ giúp bạn tối ưu hóa quy trình làm việc và đảm bảo dữ liệu luôn chính xác và cập nhật.

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

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 quản trị, 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ự online/offline, 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