Hàm INDEX trong Excel: Công thức và cách sử dụng

Hàm INDEX trong Excel cho phép bạn lấy dữ liệu dựa trên vị trí hàng – cột trong bảng tính một cách linh hoạt và rõ ràng. Bài viết sẽ hướng dẫn chi tiết cú pháp, cách sử dụng và các ví dụ minh họa, giúp bạn áp dụng hàm INDEX vào công việc hằng ngày một cách hiệu quả.

Công thức và cách sử dụng hàm INDEX trong Excel

Hàm INDEX trong Excel được dùng để lấy một giá trị hoặc một tham chiếu nằm trong một bảng hoặc một phạm vi xác định.

Cú pháp:

=INDEX(array, row_num, column_num)

Giải thích các tham số

• array: Phạm vi dữ liệu hoặc một hằng số mảng.
• Nếu mảng chỉ có một hàng hoặc một cột, bạn có thể bỏ qua lần lượt row_num hoặc column_num.
• Khi mảng có nhiều hàng và nhiều cột nhưng bạn chỉ cung cấp một trong hai tham số row_num hoặc column_num, hàm sẽ trả về toàn bộ hàng hoặc cột tương ứng dưới dạng mảng.
• row_num: Chỉ số hàng chứa giá trị cần lấy. Đây là tham số bắt buộc nếu column_num bị bỏ trống. Nếu bạn không nhập row_num, bạn phải nhập column_num.
• column_num: Chỉ số cột chứa giá trị cần lấy. Tương tự, nếu không dùng column_num thì phải khai báo row_num.

Lưu ý khi sử dụng:

• Khi cả row_num và column_num đều được cung cấp, hàm sẽ trả về giá trị tại vị trí giao nhau của hàng và cột đó.
• Hàm sẽ báo lỗi #REF! nếu hai tham số này không trỏ đến ô hợp lệ trong phạm vi đã chọn.
• Nếu row_num hoặc column_num bằng 0, hàm sẽ trả về toàn bộ hàng hoặc cột tương ứng (khi được nhập dưới dạng công thức mảng).

Công thức của hàm INDEX trong Excel

Ví dụ 1: Sử dụng hàm INDEX để lấy giá trị tại hàng và cột trong mảng

Bước 1: Xác định các thành phần của hàm

• array: phạm vi dữ liệu cần tra cứu là B2:F9
• row_num: hàng cần lấy dữ liệu là 4
• column_num: cột cần lấy dữ liệu là 2

Công thức nhập vào ô như sau:

=INDEX(B2:F9,4,2)

Công thức INDEX tra cứu giá trị tại hàng 4 và cột 2 trong mảng

Bước 2: Nhấn Enter để xem kết quả

Hàm sẽ trả về giá trị ở vị trí giao giữa hàng 4 và cột 2 của mảng, đó là: Nguyễn Thu Hà

Kết quả hàm INDEX trả về giá trị tương ứng vị trí hàng 4 cột 2

Ví dụ 2: Dùng hàm INDEX nâng cao để truy cập dữ liệu từ nhiều vùng mảng

Hàm INDEX mở rộng có dạng:

=INDEX(reference, row_num, [column_num], [area_num])

Giải thích các tham số:

• reference: tập hợp các vùng dữ liệu dùng làm nguồn truy xuất (có thể gồm một hoặc nhiều mảng ghép lại).
• row_num: số thứ tự của hàng trong vùng được chọn mà hàm sẽ lấy giá trị.
• column_num: vị trí cột trong vùng mà bạn muốn trả về kết quả.
• area_num: xác định mảng nào trong danh sách reference sẽ được sử dụng. Đây là số thứ tự của từng vùng trong reference; nếu không nhập, Excel tự hiểu là vùng số 1.

Giả sử bảng nhân sự được chia thành 2 vùng:

• Vùng 1 (mảng 1): B2:F5
• Vùng 2 (mảng 2): B6:F9
• Mục tiêu: Lấy giá trị tại hàng thứ 2 – cột thứ 2 của mảng số 2.

Bước 1: Xác định tham số

• reference = (B2:F5, B6:F9)
• row_num = 2
• column_num = 2
• area_num = 2

Công thức sử dụng:

=INDEX((B2:F5,B6:F9),2,2,2)

Thiết lập 2 vùng dữ liệu để sử dụng INDEX nâng cao

Bước 2: Nhấn Enter để nhận kết quả

Hàm sẽ trả về giá trị: Hà Văn Thới

Công thức INDEX nhiều vùng lấy giá trị hàng 2 cột 2 của mảng thứ 2

Nguyên nhân:

• Khi dùng INDEX để trả về toàn bộ một hàng hoặc một cột, bạn có thể đặt row_num hoặc column_num bằng 0. Trường hợp này yêu cầu công thức phải được nhập dưới dạng công thức mảng.
• Ở những phiên bản Excel cũ (không phải Microsoft 365), nếu bạn chỉ nhấn Enter thông thường thì Excel sẽ báo lỗi #N/A hoặc #VALUE!.

Cách xử lý:

• Với Excel truyền thống (không phải Microsoft 365):
• Chọn toàn bộ vùng sẽ nhận kết quả
• Gõ công thức vào ô đầu tiên của vùng
• Sau đó nhấn Ctrl + Shift + Enter để nhập công thức mảng kế thừa
• Với Microsoft 365 hoặc Excel Online, bạn chỉ cần Enter vì hệ thống hỗ trợ công thức mảng động.

Ví dụ: Giả sử bạn muốn hàm INDEX trả về toàn bộ giá trị của cột thứ nhất trong vùng dữ liệu B4:E13, bạn có thể thao tác như sau:

Bước 1: Xác định phạm vi sẽ chứa kết quả. Ở ví dụ này, ta cần 10 hàng nên chọn vùng G4:G13.

Bước 2: Nhập công thức tại ô đầu tiên của vùng đầu ra (ô G4):

=INDEX(B4:E13,0,1)

Dùng INDEX để trả về toàn bộ giá trị của một cột

Lưu ý: Công thức này yêu cầu nhập dưới dạng công thức mảng đối với các phiên bản Excel cũ.

Bước 3: Giữ nguyên vùng G4:G13 đang được chọn, sau đó nhấn Ctrl + Shift + Enter. Excel sẽ tự động thêm dấu ngoặc nhọn { } bao quanh công thức để xác nhận đây là công thức mảng.

Kết quả trả về sẽ hiển thị toàn bộ giá trị ở cột thứ nhất của mảng B4:E13.

Kết quả công thức mảng INDEX hiển thị toàn bộ dữ liệu của cột đầu tiên

Lỗi #REF! xuất hiện khi cặp tham số row_num và column_num chỉ tới vị trí không tồn tại trong mảng đã khai báo.

Ví dụ: Phạm vi chỉ gồm 5 cột nhưng bạn lại nhập column_num = 6, Excel sẽ báo lỗi #REF!.

Ví dụ lỗi #REF! khi row_num hoặc column_num vượt ngoài phạm vi mảng

Cách khắc phục: Xem lại toàn bộ công thức, đặc biệt là hai tham số row_num và column_num, đảm bảo chúng nằm trong giới hạn số hàng và số cột thực tế của mảng, sau đó sửa lại cho đúng.

Hàm INDEX mang lại sự linh hoạt khi bạn cần truy xuất dữ liệu trong những bảng tính lớn, đặc biệt hữu ích cho các thao tác tìm kiếm theo hàng và cột. Để khám phá thêm nhiều mẹo Excel dễ hiểu và áp dụng nhanh, bạn có thể theo dõi chuyên mục Kinh nghiệm của Siêu Thị Điện Máy - Nội Thất Chợ Lớn.

Tham khảo ngay các dòng máy tính bảng cấu hình mạnh, màn hình đẹp và nhiều mức giá linh hoạt đang có tại Siêu Thị Điện Máy - Nội Thất Chợ Lớn để chọn mẫu phù hợp cho học tập, làm việc hoặc giải trí.