Hàm INDEX là một hàm dò tìm được sử dụng phổ biến trong Excel. Hàm INDEX trong Excel sẽ trả về một giá trị hoặc tham chiếu đến một giá trị có trong bảng dữ liệu hoặc phạm vi nhất định. Hàm INDEX được sử dụng khá nhiều trong việc xử lý các bảng dữ liệu trong Excel.
Một điểm tối ưu hơn so với các hàm dò tìm khác như HLOOKUP, VLOOKUP đó là hàm INDEX có thể kết hợp với hàm MATCH hay một số hàm khác để dò tìm dữ liệu trong một bảng dữ liệu lớn với nhiều hàng và nhiều cột. Vậy thì hàm INDEX là gì trong Excel? Sử dụng hàm INDEX như thế nào? Hãy cùng tìm hiểu trong bài viết dưới đây của NTP nhé.
Mục lục
Hàm INDEX là gì trong Excel
Khái niệm về hàm dò tìm INDEX trong Excel
Hàm INDEX là một hàm dò tìm trong Excel. Hàm INDEX trong Excel sẽ trả về một giá trị hoặc tham chiếu đến một giá trị có trong bảng dữ liệu hoặc phạm vi nhất định. Còn được gọi là Hàm VLOOKUP, HLOOKUP ngược vì có chức năng dò tìm tương tự như hàm VLOOKUP, HLOOKUP nhưng hàm INDEX có thể dò tìm được theo nhiều chiều khác nhau.
Cú pháp hàm INDEX cơ bản trong Excel
Cú pháp hàm INDEX:
=INDEX(array, row_num, column_num).
Chú thích:
- array: Một vùng ô hoặc dải ô có chứa dữ liệu mà bạn cần dò tìm.
- row_num: Số thứ tự của hàng mà bạn muốn giá trị được trả về trong mảng. Nếu bỏ trống row_num thì column_num là bắt buộc.
- column_num: Số thứ tự của cột mà bạn muốn giá trị được trả về trong mảng. Nếu bỏ trống column_num thì row_num là bắt buộc.
Ví dụ về cách dùng Hàm INDEX
Cách dùng hàm INDEX cơ bản trong Excel
Ví dụ 1: Hãy tìm giá trị có ở dòng thứ 5, cột 1 trong mảng dữ liệu (B3:D8) của bảng DANH SÁCH SINH VIÊN NĂM 2021-2022 được cho trong hình bên dưới.
VD về Hàm INDEX cơ bản
Để có thể biết được giá trị có trong ô nằm ở vị trí dòng thứ 5, cột 1 trong mảng là giá trị nào ta thực hiện như sau:
Bước 1: Tại vị trí ô F4 (một ô trong cột ghi chú) bạn tiến hành nhập công thức =INDEX(B3:D8,5,1) như hình minh họa dưới đây.
Chú thích công thức:
- B3:D8 : Mảng chứa giá trị ta mà bạn cần dò tìm.
- 5 : số thứ tự của dòng tương ứng với vị trí giá trị cần tìm.
- 1 : số thứ tự của cột tương ứng với vị trí giá trị cần tìm.
VD về Hàm INDEX cơ bản
Bước 2: Sau khi nhập xong công thức bạn chỉ cần nhấn ENTER sau đó Hàm INDEX trong Excel sẽ trả về giá trị của ô mà bạn dò tìm, được hiển thị tại ô bạn nhập công thức. Kết quả như sau:
VD về Hàm INDEX cơ bản
Như vậy: Trần Quang Hải là giá trị có ở vị trí dòng thứ 5, cột 1 trong mảng B3:D8 mà ta cần dò tìm.
Sử dụng hàm INDEX nâng cao trong Excel
Cú pháp hàm INDEX nâng cao:
=INDEX(reference, row_num, [column_num], [area_num])
Chú thích:
- reference: Phạm vi tham chiếu có chứa 2 mảng (Vd: (B3:D5,B6:D8) – minh họa VD2 bên dưới)
- row_num: Số thứ tự của hàng mà bạn muốn giá trị được trả về trong reference.
- [column_num]: Số thứ tự của cột mà bạn muốn giá trị được trả về trong reference.
- area_num: Vị trí của mảng giá trị mà bạn chọn để dò tìm trong reference. Nếu trong reference có nhiều mảng thì giá trị area_num sẽ quy định row_num và [column_num] thuộc mảng nào trong reference.
Ví dụ 2: Giả sử chúng ta cần tìm phần tử ở vị trí hàng thứ 3, cột thứ 1 của mảng 2 trong một phạm vi tham chiếu có chứa 2 mảng như hình bên dưới.
VD về Hàm INDEX nâng cao
Như vậy trong trường hợp này 2 mảng được xét chính là: B3:D5 (Màu xanh) và B6:D8 (Màu đỏ).
Để có thể dò tìm phần tử ở vị trí hàng thứ 3, cột thứ 1 của mảng 2 trong một phạm vi tham chiếu trên ta thực hiện như sau:
Bước 1: Tại vị trí ô F4 (một ô trong cột ghi chú) bạn tiến hành nhập công thức =INDEX((B3:D5,B6:D8),3,1,2) trong đó:
- (B3:D5,B6:D8) : Phạm vi tham chiếu gồm 2 mảng có chứa giá trị ta mà bạn cần dò tìm.
- 3 : số thứ tự của dòng tương ứng với vị trí giá trị cần tìm.
- 1 : số thứ tự của cột tương ứng với vị trí giá trị cần tìm.
- 2: Vị trí của mảng chứa giá trị mà bạn chọn để dò tìm (Mảng 2).
VD về Hàm INDEX nâng cao
Bước 2: Sau khi nhập xong công thức bạn chỉ cần nhấn ENTER sau đó hàm INDEX trong Excel sẽ trả về giá trị của ô mà bạn dò tìm trong mảng 2, được hiển thị tại ô bạn nhập công thức. Kết quả như sau:
VD về Hàm INDEX nâng cao
Như vậy: Hà Huy Tuấn là giá trị có ở vị trí dòng thứ 3, cột 1 trong mảng 2 (B6:D8) mà ta cần dò tìm.
Kết hợp giữa hàm INDEX MATCH trong Excel
Sự khác nhau giữa hàm INDEX MATCH và VLOOKUP, HLOOKUP
Hàm INDEX hay VLOOKUP, HLOOKUP đều là những hàm dò tìm dữ liệu phổ biến và quen thuộc với nhiều người dùng Excel hiện nay. Tuy nhiên hàm VLOOKUP và HLOOKUP có điều hạn chế trong quá trình dò tìm đó chính là giá trị trả về phải nằm ở cột bên phải của giá trị dò tìm đối với hàm VLOOKUP và nằm ở cột bên dưới của giá trị dò tìm đối với hàm HLOOKUP.
Vì vậy để khắc phục điều đó thay vì dùng hàm VLOOKUP và HLOOKUP để dò tìm dữ liệu bạn có thể sử dụng kết hợp hàm INDEX MATCH để dò tìm dữ liệu. Vì hàm INDEX và MATCH khi kết hợp có thể dò tìm dữ liệu theo nhiều chiều khác nhau.
Ứng dụng hàm INDEX và MATCH excel
Cú pháp hàm MATCH:
=MATCH(lookup_value, lookup_array, [match_type]).
Chú thích:
- lookup_value: Giá trị mà bạn cần tìm kiếm trong mảng/ vùng dữ liệu.
- lookup_array: Là mảng/ vùng dữ liệu có chứa giá trị mà bạn cần tìm, có thể là số, văn bản hay giá trị logic. Thành phần bắt buộc phải có.
- match_type: Kiểu tìm kiếm và là thành phần không bắt buộc (Mặc định là 1 (Less than)).
Ví dụ 3: Từ thông tin trong BẢNG 1 và BẢNG 2 được cho bên dưới bạn hãy tìm Đơn giá của từng Sản phẩm ứng với Hãng SX tương ứng của Sản phẩm đó.
VD Hàm MATCH và INDEX
Để có thể tìm được Đơn giá của sản phẩm theo Hãng SX tương ứng của nó bạn tiến hành các bước như sau:
Bước 1: Tại vị trí ô C3 bạn hãy nhập công thức:
=INDEX($B$14:$D$16,MATCH(A3,$A$14:$A$16,0),MATCH(B3,$B$13:$D$13,0))
Chú thích công thức:
- $B$14:$D$16: Phạm vi ô chứa Đơn giá mà ta cần dò tìm.
- MATCH(A3,$A$14:$A$16,0): Vị trí của hàng tương ứng với đơn giá cần dò tìm.
- MATCH(B3,$B$13:$D$13,0): Vị trí của cột tương ứng với đơn giá cần dò tìm.
VD Hàm MATCH và INDEX
Bước 2: Nhấn ENTER để hàm INDEX MATCH tiến hành dò tìm và kết quả sẽ được trả về tại ô mình nhập công thức.
VD Hàm INDEX MATCH
Bước 3: Cuối cùng bạn chỉ cần COPY công thức cho những ô còn lại để hoàn thành dò tìm cho toàn bộ Đơn giá của sản phẩm.
VD Hàm INDEX MATCH
Như kết quả trên hình minh họa kết quả được hiển thị chính là Đơn giá của từng Sản phẩm ứng với Hãng SX tương ứng.
Điều cần lưu ý khi dùng hàm INDEX trong Excel
- Nếu bạn khai báo row_Number hay column_Number là 0 thì hàm INDEX trong Excel sẽ trả về mảng giá trị cho toàn bộ số cột hoặc hàng tương ứng với điều kiện bạn đã nhập.
- Khi kết quả của bạn hiện lên lỗi #REF thì bạn nên kiểm tra lại vì khi bạn chọn địa chỉ của phần tử không nằm trong mảng ban đầu thì hàm sẽ trả về lỗi #REF.
Bài viết trên NTP đã hướng dẫn cho bạn chi tiết về cách dùng hàm INDEX trong Excel sao cho hiệu quả và cùng với đó là những lưu ý khi dùng hàm tìm kiếm giá trị này. Việc bạn sử dụng tốt hàm INDEX và kết hợp cùng với hàm MATCH trong Excel sẽ giúp bạn dò tìm dữ liệu và giải quyết công việc của mình một cách nhanh chóng hơn. NTP hy vọng với những kiến thức bổ ích trên sẽ giúp bạn hoàn thành công việc tốt nhất. Hãy theo dõi NTP để có thêm nhiều kiến thức hơn nhé!
