Cách dùng hàm INDEX trong Excel – Hàm kết hợp INDEX MATCH

Cách dùng Hàm INDEX trong Excel - Hàm kết hợp INDEX MATCH

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é.

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.

Cách dùng Hàm INDEX trong Excel - Hàm kết hợp INDEX MATCH

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.

Cách dùng Hàm INDEX trong Excel - Hàm kết hợp INDEX MATCH

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:

Cách dùng Hàm INDEX trong Excel - Hàm kết hợp INDEX MATCH

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 [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.

Cách dùng Hàm INDEX trong Excel - Hàm kết hợp INDEX MATCH

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).

Cách dùng Hàm INDEX trong Excel - Hàm kết hợp INDEX MATCH

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:

Cách dùng Hàm INDEX trong Excel - Hàm kết hợp INDEX MATCH

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 1BẢ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 đó.

Hàm MATCH trong Excel - Hàm kết hợp INDEX MATCH

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.

Hàm MATCH trong Excel - Hàm kết hợp INDEX MATCH

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.

Hàm MATCH trong Excel - Hàm kết hợp INDEX MATCH

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.

Hàm MATCH trong Excel - Hàm kết hợp INDEX MATCH

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é!

Trả lời

Email của bạn sẽ không được hiển thị công khai.

0969313020