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. Bài viết sau đây sẽ hướng dẫn các bạn cách dùng Hàm INDEX trong Excel.
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?
- Hàm INDEX dạng mảng – Công thức và đối số
- Ví dụ minh họa của hàm INDEX dạng mảng trong Excel
- Cách khởi động hộp thoại hàm INDEX dạng mảng
- Cách nhập các đối số của hàm INDEX dạng mảng
- Các lỗi liên quan đến hàm INDEX dạng mảng
- Hàm INDEX dạng tham chiếu – Công thức và đối số
- Ví dụ của hàm INDEX dạng tham chiếu trong Excel
- Cách mở hộp thoại INDEX dạng tham chiếu
- Cách nhập dữ liệu của hàm INDEX dạng tham chiếu
- Các lỗi liên quan đến hàm INDEX dạng tham chiếu
- Cách khởi động hộp thoại hàm INDEX dạng mảng
- Cách nhập các đối số của hàm INDEX dạng mảng
- Các lỗi liên quan đến hàm INDEX dạng mảng
- Cách mở hộp thoại INDEX dạng tham chiếu
- Cách nhập dữ liệu của hàm INDEX dạng tham chiếu
- Các lỗi liên quan đến hàm INDEX dạng tham chiếu
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à:
- 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à:
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:
- 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.
Có thể bạn quan tâm:
- Hướng dẫn cách sử dụng hàm COUNT để đếm dữ liệu trong Excel
- Hướng dẫn cách sử dụng hàm SUMIF để tính tổng có điều kiện trong Excel
- Hướng dẫn sử dụng hàm PRODUCT để thực hiện phép nhân trong Excel
Bài viết liên quan:
