Categories: Tin học văn phòng

Cách dùng Hàm INDEX trong Excel – Công thức và ví dụ minh họa

Hàm INDEX trong Excel trả về một giá trị trong bảng dựa trên dựa trên số hàng và số cột được cung cấp trong bảng dữ liệu.

Bạn có thể hình dung như nó giống như việc sử dụng tọa độ để xác định vị trí trên bản đồ. Dựa vào số hàng và số cột được cung cấp, hàm INDEX sẽ trả về giá trị khớp với vị trí trong bảng.

Trước khi bắt đầu bài hướng dẫn, tôi cần phải cho bạn biết rằng hàm INDEX riêng lẻ không thực sự hữu ích hay thú vị gì cả. Nó chỉ trở nên hữu dụng khi bạn sử dụng kết hợp nó với các hàm chức năng khác ví dụ như hàm MATCH. Lấy ví dụ, bằng cách kết hợp 2 hàm INDEX và MATCH, bạn sẽ có thể khắc phục những điểm yếu của hàm VLOOKUP.

Để bạn có thể dễ dàng tìm hiểu về chức năng của hàm INDEX, chúng tôi sẽ để cập đến chức năng của hàm MATCH trong hướng dẫn sau.

Ghi chú: Các hướng dẫn trong bài viết này có thể áp dụng cho Microsoft Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel của Microsoft 365.

Nội dung chính:

Có mấy dạng hàm INDEX trong Excel?

Có hai dạng biểu mẫu có sẵn của hàm INDEX trong Excel.

Hàm INDEX dạng mảng.
Hàm INDEX dạng tham chiếu.

Sự khác biệt chính của hai dạng hàm INDEX này là:

Hàm INDEX dạng mảng trả về giá trị nằm ở điểm giao nhau của một hàng và cột dữ liệu cụ thể.
Hàm INDEX dạng tham chiếu trả về tham chiếu ô của điểm giao nhau của một hàng và cột dữ liệu cụ thể.

Mảng trong Excel là một nhóm các ô liền kề trong một bảng tính. Trong hình ảnh minh họa bên dưới, mảng là một tập hợp các ô từ A2 đến C4.

Hàm INDEX dạng mảng – Công thức và đối số

Cú pháp hàm sẽ đề cập đến cách bố trí của hàm và bao gồm tên hàm, dấu ngoặc, dấu phẩy phân tách và đối số).

Công thức của hàm INDEX dạng mảng là:

=INDEX(Array,Row_num,Column_num)

Array: Phạm vi ô được tìm kiếm theo hàm với thông tin bạn mong muốn.
Row_num (tùy chọn): Số hàng trong mảng để trả về giá trị. Nếu đối số này bị bỏ qua, thì Column_num là đối số bắt buộc.
Column_num (tùy chọn): Số cột trong mảng để trả về giá trị. Nếu đối số này bị bỏ qua, Row_num là sẽ là đối số bắt buộc.

Đối với cả đối số Row_num và Column_num, bạn có thể nhập số hàng và số cột thực tế hoặc sử dụng tham chiếu ô đến vị trí của thông tin này trên bảng tính.

Ví dụ minh họa của hàm INDEX dạng mảng trong Excel

Vì có hai dạng khác nhau của hàm INDEX – mỗi dạng có một bộ đối số riêng nên dạng biểu thức của nó cũng yêu cầu một hộp thoại riêng.

Dẫn tới kết quả là, hàm INDEX sẽ có thêm một bước lựa chọn mà bạn không thể nhìn thấy trong các hàm Excel khác. Bước này liên quan đến việc bạn lựa chọn dạng biểu mẫu Mảng (Array) hay Tham chiếu (Reference) của hàm INDEX.

Cách khởi động hộp thoại hàm INDEX dạng mảng

Bước 1: Bạn tạo một bảng tính Excel mới và nhập các dữ liệu như trong hình bên dưới.

Bước 2: Chọn ô B8 trong bảng tính. Đây là nơi kết quả của hàm sẽ được trả về.

Bước 3: Trên thanh Menu chính, bạn chọn tab Formulas.

Bước 4: Sau đó chọn Lookup and Reference > INDEX.

Bước 5: Trong hộp thoại Select Arguments, bạn lựa chọn array, row_num, column_num và nhấn OK.

Cách nhập các đối số của hàm INDEX dạng mảng

Bước 1: Trong hộp thoại Function Arguments, bạn lựa chọn hộp văn bản Array.

Bước 2: Trong bảng tính, bạn hãy tô đen phạm vi các ô từ A2 đến C4 để nhập phạm vi này vào hộp thoại.

Bước 3: Tiếp đến trong hộp thoại Function Arguments, bạn chọn hộp văn bản Row_num để định hình vị trí dòng.

Bước 4: Nhập số 3.

Bước 5: Chọn hộp văn bản Column_num để định hình vị trí cột.

Bước 6: Trong Function Arguments, bạn nhập số 2 để nhập tham chiếu ô Column_num.

Bước 7: Chọn OK để hoàn thành công thức và đóng hộp thoại.

Kết quả trả về “Máy rửa chén” sẽ xuất hiện trong ô B8 vì đây là giá trị của ô giao với dòng thứ ba và cột thứ hai của phạm vi dữ liệu mà bạn đã lựa chọn.

Khi bạn nhấp chuột vào ô B8, hàm INDEX hoàn chỉnh =INDEX(A2:C4,3,2) sẽ được hiển thị trong thanh công thức phía trên của trang tính.

Các lỗi liên quan đến hàm INDEX dạng mảng

Các giá trị lỗi phổ biến liên quan đến hàm INDEX dạng mảng là:

#VALUE!: Xảy ra nếu đối số Row_num hoặc Column_num không phải là số.
#REF!: Xảy ra nếu đối số Row_num lớn hơn số lượng hàng trong phạm vi đã chọn hoặc đối số Col_num lớn hơn số lượng cột trong phạm vi đã chọn.

Hàm INDEX dạng tham chiếu – Công thức và đối số

Dạng tham chiếu của hàm trả về giá trị dữ liệu của ô nằm ở điểm giao nhau giữa một hàng và cột dữ liệu cụ thể. Mảng tham chiếu có thể bao gồm nhiều phạm vi dữ liệu không liền kề với nhau.

Cú pháp và đối số của hàm INDEX dạng tham chiếu là:

=INDEX(Reference,Row_num,Column_num,Area_num)

Reference (bắt buộc): là tham chiếu ô cho phạm vi ô được tìm kiếm theo hàm cho thông minh bạn mong muốn. Nếu như bạn có nhiều phạm vi dữ liệu không liền kề và muốn nhập thông tin vào đây, các phạm vi phải được bao quanh bởi một bộ dấu ngoặc tròn riêng biệt như được hiển thị trong hàm INDEX bên dưới:

=INDEX((A1:A5,C1:E1,C4:D5),B7,B8)

Row_num: là số hàng trong mảng dữ liệu để trả về giá trị.
Column_num: là số cột trong mảng dữ liệu để trả về giá trị.

Ghi chú: Đối với các đối số Row_num và Column_num, bạn có thể nhập số hàng và số cột thực tế hoặc tham chiếu ô đến vị trí của thông tin này trong bảng tính.

Area_num (tùy chọn): được sử dụng khi đối số Reference chứa nhiều phạm vi không liền kề với nhau. Đối số này chọn phạm vi ô nào để trả về dữ liệu. Nếu được bỏ qua, hàm INDEX sẽ sử dụng phạm vi đầu tiên được liệt kê trong đối số Reference. Phạm vi được nhập trong đối số Reference sẽ được đánh số là 1, thứ hai là 2, thứ ba là 3…

Ví dụ của hàm INDEX dạng tham chiếu trong Excel

Bởi vì hàm INDEX có hai dạng khác nhau, nên bạn phải sử dụng hộp thoại với các đối số riêng biệt.

Cách mở hộp thoại INDEX dạng tham chiếu

Bước 1: Nhập những dữ liệu được hiển thị trong bảng tính Excel này.

Bước 2: Chọn ô B10 trong bảng tính, là nơi kết quả của hàm được hiển thị.

Bước 3: Chuyển đến Formulas.

Bước 4: Lựa chọn Lookup and Reference > INDEX.

Bước 5: Trong hộp thoại Select Arguments, lựa chọn reference, row_num, column_num, area_num.

Bước 6: Chọn OK để mở hộp thoại Function Arguments.

Cách nhập dữ liệu của hàm INDEX dạng tham chiếu

Bước 1: Trong hộp thoại Function Arguments, lựa chọn hộp văn bản Reference.

Bước 2: Trong bảng tính, bạn hãy tô đen các giá trị từ ô A1 cho đến ô D5 để nhập tham chiếu ô đó vào trong hộp thoại.

Bước 3: Trong hộp thoại Function Arguments, lựa chọn hộp văn bản Row_num.

Bước 4: Trong bảng tính, bạn chọn ô B7 để nhập tham chiếu ô đó vào trong hộp thoại.

Bước 5: Trong hộp thoại Function Arguments, bạn chọn hộp văn bản Column_num.

Bước 6: Trong bảng tính, chọn ô B8 để nhập tham chiếu ô đó vào trong hộp thoại.

Bước 7: Trong hộp thoại Function Arguments, bạn chọn hộp văn bản Area_num.

Bước 8: Trong bảng tính, lựa chọn ô B9 để nhập tham chiếu ô đó vào hộp thoại.

Bước 9: Nhấn OK.

Kết quả Tháng 6 sẽ xuất hiện trong ô B10 vì đó là tháng trong ô giao với hàng đầu tiên và cột thứ hai của phạm vi từ ô A1 đến D5.

Các lỗi liên quan đến hàm INDEX dạng tham chiếu

Những lỗi thường gặp xuất hiện trên hàm INDEX dạng tham chiếu là:

#VALUE!: Xảy ra nếu các đối số Row_num, Column_num, hoặc Area_num không phải là số.
#REF!: Xảy ra khi đối số Row_num lớn hơn số lượng hàng trong phạm vi đã chọn hoặc đối số Col_num lớn hơn số lượng cột trong phạm vi đã chọn hoặc đối số Area_num lớn hơn số vùng trong phạm vi đã chọn.

Vậy là vừa rồi bạn đã tìm hiểu qua hàm INDEX với hai dạng khác nhau đi kèm với công thức, cách sử dụng và ví dụ minh họa của từng dạng hàm. Trong bài viết sau, chúng ta sẽ cùng tìm hiểu chức năng của hàm MATCH trong Excel để dùng kết hợp với hàm INDEX để mang lại hiệu quả tối ưu nhất.