Hàm INDEX trong Excel, cách sử dụng có bài tập cụ thể

Hàm INDEX trong Excel trả về một giá trị hoặc tham chiếu tới một giá trị trong bảng hoặc phạm vi. Mời bạn cùng bài viết sau của Điện máy XANH tìm hiểu về công thức hàm INDEX và cách sử dụng cụ thể nhé!

1Công thức hàm INDEX

Hàm INDEX trong Excel là hàm cho kết quả trả về là một giá trị hoặc tham chiếu tới một giá trị trong bảng hoặc phạm vi nhất định.

Cú pháp hàm INDEX:

Lưu ý: 

• Nếu dùng đến cả 2 đối số row_num và column_num, hàm sẽ trả về giá trị trong ô tại giao điểm row_num và column_num.
• Hàm trả về lỗi #REF! nếu row_num và column_num không trỏ tới bất kỳ một ô trong mảng đã khai báo.
• Nếu khai báo row_num hoặc column_num là 0 (không), hàm trả về 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 hàm INDEX làm công thức mảng.

Bên cạnh đó, hàm INDEX cũng cho phép mở rộng phạm vi tham chiếu đến nhiều mảng. Chúng ta có thể tìm phần tử của một vùng tham chiếu thuộc nhiều mảng khác nhau thông qua công thức sau:

Lưu ý:

• Nếu khai báo row_num hoặc column_num là 0 (không), hàm trả về 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 hàm INDEX làm công thức mảng.
• Hàm trả về lỗi #REF! nếu row_num và column_num không trỏ tới bất kỳ một ô nào trong mảng đã khai báo. Nếu row_num và column_num được bỏ qua, hàm sẽ trả về vùng trong tham chiếu được xác định bởi area_num.
• Kết quả của hàm INDEX là một tham chiếu. Giá trị mà hàm INDEX trả về có thể được dùng như một tham chiếu hoặc một giá trị tùy theo công thức bạn áp dụng cho kết quả đó.

2Một số bài tập hàm INDEX

Dưới đây là một số ví dụ sử dụng hàm INDEX để xử lý dữ liệu trên 1 hoặc nhiều mảng tham chiếu:

Ví dụ 1: Lấy giá trị từ mảng dữ liệu được khai báo

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

Bây giờ, giả sử bạn cần tìm giá trị của phần tử ở dòng thứ 4, cột thứ 1 trong mảng. Mảng ở đây gồm 10 dòng và 4 cột (có địa chỉ là B4:E13).

Bạn nhập công thức: =INDEX(B4:E13,4,1)

Giải thích công thức (theo thứ tự các đối số trong công thức từ trái sang):

• B4:E13 là mảng chứa giá trị cần trả về.
• 4 là số thứ tự của hàng trong mảng chứa giá trị cần trả về.
• 1 là số thứ tự của cột trong mảng chứa giá trị cần trả về.

Sau khi nhập xong, bạn nhấn Enter. Kết quả trả về sẽ như hình dưới.

Ví dụ 2: Lấy giá trị từ nhiều mảng dữ liệu khác nhau

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

Bây giờ bạn muốn tìm phần tử hàng thứ 2, cột thứ 1 của mảng thứ 2. Bạn nhập công thức:

=INDEX((B4:E8,B9:E13),2,1,2)

Giải thích công thức (theo thứ tự các đối số trong công thức từ trái sang):

• (B4:E8,B9:E13) là 2 mảng chứa giá trị cần trả về. 2 mảng này được phân cách bởi dấu phẩy (,)
• 2 là số thứ tự của hàng trong mảng chứa giá trị cần trả về.
• 1 là số thứ tự của cột trong mảng chứa giá trị cần trả về.
• 2 là thứ tự của mảng mà bạn muốn lấy dữ liệu trong vùng tham chiếu.

Sau khi nhập xong, bạn nhấn Enter. Kết quả trả về sẽ như hình dưới.

3Một số lỗi khi sử dụng hàm INDEX

Dưới đây là một số lỗi bạn có thể gặp khi thao tác với hàm INDEX:

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.

Như đã đề cập ở trên, bạn có thể khai báo row_num hoặc column_num là 0 (không) để hàm trả về 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 hàm INDEX làm công thức mảng. Tuy nhiên, trong trường hợp này, trừ phiên bản mới nhất của Microsoft 365, đối với các phiên bản khác, Excel sẽ báo lỗi nếu không làm đúng quy trình dùng công thức mảng.

- Cách khắc phục:

Với các phiên bản Excel không phải phiên bản mới của Microsoft 365, bạn sẽ phải nhập công thức mảng dưới dạng công thức mảng kế thừa bằng cách chọn vùng dữ liệu đầu ra, nhập công thức vào ô trên cùng, bên trái của phạm vi đầu ra sau đó nhấn Ctrl + Shift + Enter.

Ví dụ: Bạn muốn hàm INDEX trả về mảng giá trị cho toàn bộ cột 1 trong mảng B4:E13, bạn làm theo các bước sau:

Bước 1: Chọn phạm vi đầu ra, ở đây chọn 10 hàng trong cột G, có địa chỉ là G4:G13.

Bước 2: Nhập công thức ở ô trên cùng bên trái của phạm vi đầu ra (Ở đây là ô G4).

Tại ô G4 bạn nhập: =INDEX(B4:E13,0,1)

Bước 3: Nhấn Ctrl + Shift + Enter để xác nhận. Excel sẽ chèn dấu ngoặc nhọn ở đầu và cuối công thức cho bạn. Cuối cùng, bạn xem kết quả hoàn thành như sau:

Lỗi #REF!

- Nguyên nhân: Các đối số row_num và column_num không trỏ tới bất kỳ một ô trong mảng đã khai báo, ví dụ mảng khai báo chỉ có 4 cột nhưng bạn vô tình nhập column_num là 6.

- Cách khắc phục: Kiểm tra lại công thức xem đã đúng chưa và chỉnh lại cho phù hợp, nhất là các đối số column_num, row_num.

Trên đây là hướng dẫn cách dùng hàm INDEX trong Excel kèm bài tập minh họa. Hy vọng bài viết sẽ giúp ích cho bạn trong việc thao tác với dữ liệu Excel và tối ưu hiệu quả làm việc nhé!