Hàm INDEX trong Excel là gì? Cú pháp và cách sử dụng hàm INDEX như thế nào? Phương pháp kết hợp hàm INDEX và MATCH trong Excel để giảm bớt việc tính toán trong công việc và học tập. Tất cả những vấn đề trên sẽ được giải quyết thông qua bài viết dưới đây. Hãy cùng Daotaotinhoc theo dõi nhé!
Xem thêm:
4 Cách sử dụng hàm round trong excel đơn giản, dễ làm kèm ví dụ minh họa
Các khóa học cơ bản excel từ cơ bản đến nâng cao
MỤC LỤC
Tổng quan về hàm INDEX trong Excel
Trong quá trình học tập hay công việc hàng ngày, chắc hẳn chúng ta đã từng nghe đến các hàm trong Excel. Trong số đó, có một hàm rất linh hoạt và nó có thể kết hợp với các hàm khác nữa để giải quyết được nhiều vấn đề phức tạp đặt ra nhưng sức mạnh của những hàm này thường bị đánh giá thấp và không được sử dụng đúng cách. Hàm đó có lẽ là hàm INDEX.
Hàm INDEX là gì?
Hàm INDEX là hàm trả về mảng, hàm này giúp lấy các giá trị tại một ô nào đó giao giữa cột và dòng. Có thể hiểu đơn giản như sau, trong một mảng cho trước, nếu biết được vị trí (dòng và cột) của một phần tử thì hàm INDEX sẽ lấy giá trị tại ô đó.
Hàm INDEX trong Excel là gì?
Trong Excel hàm INDEX là một Function (chức năng) được sử dụng nhằm mục đích trả về một ô tham chiếu trong một mảng hoặc một dải nhất định. Hay nói cách khác, bạn sẽ sử dụng hàm INDEX khi bạn biết (hoặc có thể tính toán) vị trí của một phần tử nào trong bảng tính Excel và bạn muốn nhận giá trị thực của phần tử đó.
Hàm INDEX nâng cao trong Excel
Trong Excel, hàm INDEX nâng cao giúp cho chúng ta có thể tạo một vùng tham chiếu rộng hơn, bao gồm nhiều vùng tham chiếu khác nhau. Vậy điểm khác nhau giữa hàm INDEX cơ bản và nâng cao như thế nào? Cùng theo dõi phần dưới đây nhé!
Cú pháp và cách dùng hàm INDEX trong Excel
Cú pháp hàm INDEX cơ bản
Công thức của hàm INDEX cơ bản như sau:
=INDEX(array;row_num;[column_num])
Trong đó:
- array: là vùng ô hoặc một hàng số mảng nào đó;
- row_num: là chọn hàng trong mảng từ đó trả về một giá trị;
- column: là chọn một cột trong mảng từ đó trả về một giá trị;
Ví dụ tham khảo: Bạn hãy tìm giá trị của phần tử dòng thứ 3, cột thứ 2 trong mảng (mảng bao gồm 11 dòng và cột) như hình dưới đây:
Bước 1: Tại vị trí ô G8 bạn nhập công thức sau:
=INDEX(B3:F13;3;2)
Bước 2: Nhấn phím Enter, ta sẽ có kết quả như sau: Phần tử ở dòng thứ 3, cột thứ 2 trong mảng sẽ là: “Lê Thị C”
Tương tự, bạn có thể tìm phần tử bất kỳ thuộc mảng này thông qua cú pháp đã cung cấp ở trên nhé.
Lưu ý: Lỗi #REF sẽ xảy ra khi các bạn chọn địa chỉ của phần tử không nằm trong mảng ban đầu.
Cú pháp hàm INDEX nâng cao
Khi vượt ra phạm vi của một mảng, chúng ta có thể tìm phần tử của một vùng tham chiếu (có thể có 1 hay nhiều mảng khác nhau) bằng công thứ tổng quát như sau:
=INDEX(reference;row_num;[column_num],[area_num])
Trong đó:
- reference: đây là vùng tham chiếu (có thể kết hợp nhiều mảng lại với nhau);
- row_num: là chỉ số hàng từ đó trả về một tham chiếu;
- area_num: là số của vùng sẽ trả về giá trị trong reference (có nghĩa là số thứ tự của mảng trong vùng tham chiếu). Nếu như area_num được bỏ qua thì hàm INDEX sẽ mặc định dùng vùng 1.
Về mặt cú pháp, hàm INDEX nâng cao không có sự khác nhau nhiều so với hàm cơ bản. Điểm khác nhau đó là vùng giá trị có thể có nhiều hơn một mảng và kết quả trả về sẽ phụ thuộc vào mảng mà bạn chọn.
Để dễ hiểu hơn, chúng ta cùng quan sát ví dụ sau:
Ví dụ tham khảo: Giả sử chúng ta có 2 mảng bao gồm mảng [A3:F8] (màu trắng) và mảng [A9:F13] (màu hồng). Ta muốn tìm phần tử ở hàng thứ 4, cột thứ 2 của mảng thứ 2. Như hình minh họa dưới đây:
Lúc này, chúng ta sẽ sử dụng công thức của hàm INDEX nâng cao, để tìm kết quả trả về theo điều kiện đề ra như sau:
=INDEX((A3:F8;A9:F13);3;2;2)
Ở trong công thức này, chúng ta có 2 mảng [B3:F8] và [B9:F13] (tức là vùng tham chiếu); phần tử ở dòng thứ 3, cột thứ 2 và nằm ở mảng thứ 2 do đó ta sẽ điền lần lượt là 3,2,2 trong công thức.
Sau đó, nhấn phím Enter, kết quả trả về sẽ là:
Vậy phần tử của dòng thứ 3, cột thứ 2 của mảng thứ 2 là “Nguyễn Công I”
Kết hợp hàm INDEX và MATCH
Trong thực tế, việc dùng hàm INDEX đơn thuần thường không được tận dụng hết tất cả sức mạnh của hàm này. Người ta thường kết hợp giữa hàm INDEX và hàm MATCH để thực hiện những chuỗi tính toán phức tạp hơn rất nhiều.
Như những tìm hiểu trên, trong cú pháp hàm INDEX sẽ có 2 giá trị là vị trí dòng (row_num) và vị trí cột (column_num). Ta hoàn toàn có thể thay thế 2 giá trị này bằng cách sử dụng phép toán của hàm MATCH.
Ví dụ tham khảo: Trong bảng lương dưới đây, bạn hãy tìm chức vụ của nhân viên có mã NV là NV04 trong bảng dưới đây:
Như vậy, để tìm được chức vụ của nhân viên trong bảng này, chúng ta phải biết số dòng và số cột của phần tử đó để áp dụng công thức hàm INDEX như hướng dẫn trên.
Khi đó, muốn tìm được số dòng và số cột ta áp dụng thêm công thức của hàm MATCH.
Bước 1: Để tìm được số dòng, chúng ta phải tìm địa chỉ của mã “NV04” tong dãy mã nhân viên, công thức như sau: =MATCH(“NV04”:B3:B13;0).
Và kết quả trả về là số 4. Vậy mã NV04 nằm ở vị trí dòng thứ 4 trong dãy mã NV:
Bước 2: Để tìm được số cột, ta sẽ phải tìm địa chỉ của “Chức vụ” trong dãy từ C6 đến F6, công thức như sau: =MATCH(“Chức vụ”;C2:F2;0).
Vậy kết quả trả về là số 2. Vậy cụm từ “chức vụ” này nằm ở vị trí thứ 2 trong dãy:
Bước 3: Ta kết hợp hàm INDEX và MATCH bằng công thức sau:
=INDEX(C3:F13;MATCH(“NV04”;B3:B13;0);MATCH(“Chức vụ”;C2:F2;0))
Bước 4: Trong kết quả trả về, nhân viên có mã số NV04 làm chức vụ “Trưởng Phòng”
Những lỗi thường gặp khi dùng hàm INDEX và cách khắc phục
Lỗi #N/A hoặc lỗi #VALUE!
Nguyên nhân: Khi sử dụng công thức mảng mà không nhấn Ctrl + Shift + Enter. Như những đề cập trên, bạn có thể khai báo row_num hoặc column_num là 0 để 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 là bạn nhập hàm INDEX làm công thức mảng. Trong trường hợp này, ngoại 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 như không làm đúng quy trình khi dùng công thức mảng.
Cách khắc phục:
Với những phiên bản 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 bạn chọn vùng dữ liệu đầu ra, nhập công thức vào ô phía trên cùng, bên trái của phạm vi đầu ra rồi sau đó nhấn Ctrl + Shift + Enter.
Ví dụ: Khi bạn muốn hàm INDEX trả về mảng giá trị cho toàn bộ cột 1 trong mảng B3:F13, bạn hãy làm theo các bước sau:
Bước 1: Bạn chọn phạm vi đầu ra, ở đây bạn chọn 10 hàng trong cột G, có địa chỉ là I3:I13.
Bước 2: Bạn nhập công thức ở ô phía trên cùng bên trái của phạm vi đầu ra (ở đây là ô I3). Tại vị trí ô I3 bạn nhập công thức: =INDEX(B3:F13,0,1)
Bước 3: Bạn nhấn Ctrl + Shift + Enter để xác nhận. Phía 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ả trả về như sau:
Lỗi #REF!
Nguyên nhân: Khi các đối số row_num và column_num không trỏ tới bất kỳ một ô nào trong mảng đã khai báo, ví dụ khi mảng khai báo chỉ có 4 cột nhưng bạn lại vô tình khập column_num là 6.
Cách khắc phục: Bạn kiểm tra lại công thức xem đã đúng chưa và chỉnh lại cho phù hợp, đặc biệt là các đối số column_num, row_num.
Như vậy, thông qua bài viết trên, Daotaotinhoc vừa chia sẻ cho bạn cú pháp, cách dùng hàm INDEX và sự kết hợp giữa hàm INDEX và MATCH trong Excel. Nếu bạn có câu hỏi hay thắc mắc nào hay để lại bình luận phía dưới ngay nhé! Cảm ơn các bạn đã theo dõi và chúc các bạn thành công!