Hướng dẫn kết hợp hàm Index và Match trong Excel - QUẢN TRỊ EXCEL

QUẢN TRỊ EXCEL

Chào mừng các bạn đến với QuanTriExcel.info - Không gì là Không thể... Về thủ thuật Excel, học lập trình VBA, phần mềm chuyên dụng, kế toán, kĩ năng sống ...

Tìm kiếm

Monday, December 2, 2019

Hướng dẫn kết hợp hàm Index và Match trong Excel

Hàm tìm kiếm trong Excel thuộc nhóm hàm cơ bản Excel và có rất nhiều lựa chọn khác nhau để bạn tìm được giá trị mình cần trong bảng, như dùng hàm Vlookup Excel hay hàm Hlookup. Tuy nhiên 2 hàm tìm kiếm trên sẽ chỉ tìm giá trị theo hàng hoặc cột, theo 1 chiều mà không tìm kiếm giá trị trên cả hàng và cột.
Với trường hợp này chúng ta nên kết hợp giữa hàm Index và Match để lấy các giá trị ở các hàng và các cột, dễ dàng thực hiện hơn để thay thế cho hàm tìm kiếm Vlookup hay Hlookup. Bài viết dưới đây sẽ hướng dẫn bạn đọc cách kết hợp 2 hàm Index và Match trong Excel.

Hướng dẫn kết hợp hàm Index và Match trong Excel

1. Hàm Index trong Excel
Hàm Index có 2 dạng là hàm Index dạng mảng và Index dạng tham chiếu.
Hàm Index dạng mảng trả về giá trị của 1 ô dữ liệu có chỉ số hàng và chỉ số cột đang tìm kiếm. Cú pháp hàm dạng mạng là Index (Array, Row_num,[column_num]). Trong đó:
  • Array: Mảng dữ liệu tham chiếu.
  • Row_num: Hàng chứa giá trị cần lấy.
  • Column_num: Cột chứa giá trị cần lấy.
Hàm Index dạng tham chiếu trả về giá trị của 1 ô có chỉ số hàng và cột đang tìm kiếm. Cú pháp hàm dạng tham chiếu là INDEX (Reference, Row_num, [Column_num], [Area_num]. Trong đó:
  • Reference: Vùng tham chiếu chứa giá trị cần tìm.
  • Row_num: Chỉ số hàng chứa giá trị cần tìm.
  • Column_num: Chỉ số cột chứa giá trị cần tìm.
  • Area_num: Số vùng trả về, để trống mặc định là 1.
2. Hàm Match trong Excel
Hàm Match trả về số thứ tự của giá trị cần tìm trong bảng. Cấu trúc hàm Match là =MATCH (Lookup_Value, Lookup_array, [Match_type]). Trong đó:
  • Lookup_Value: Giá trị cần tìm kiếm.
  • Lookup_array: Mảng chứa giá trị cần tìm.
  • Match_type: Kiểu tìm kiếm. Có 3 kiểu tìm kiếm:
    • Tìm kiếm giá trị nhỏ hơn giá trị cần tìm kiếm khi match_type =1.
    • Tìm kiếm giá trị bằng giá trị cần tìm kiếm khi match_type =0.
    • Tìm kiếm giá trị lớn hơn giá trị cần tìm kiếm khi match_type = -1.
3. Kết hợp hàm Match với hàm Index
Chúng ta có bảng dữ liệu như hình dưới đây.
Bảng dữ liệu

Bài tập 1: Tìm kiếm chất liệu của mặt hàng Tất cổ chân trong bảng (Tìm từ phải qua trái)
Theo bảng chúng ta thấy vị trí của Tất cổ chân nằm ở STT 4 và dóng sang sẽ thấy chất liệu của Tất cổ chân là Len dệt kim. Như vậy chúng ta sẽ nhìn vào cột Chất liệu và tìm tới dòng số 5 sẽ ra được kết quả.
Bước 1:
Trước hết chúng ta sẽ tìm mặt hàng Tất cổ chân nằm ở vị trí nào trong bảng. Nhập công thức =MATCH("Tất cổ chân",B1:B7,0) rồi nhấn Enter. Trong đó:
  • Tất cổ chân: Là giá trị cần tìm đúng vị trí.
  • B1:B7: Vùng tìm kiếm cho giá trị, ở đây là cột Mặt hàng.
  • 0: Tìm giá trị chính xác.
Tìm vị trí Tất cổ chân
Kết quả sẽ ra 5, nghĩa là Tất cổ chân ở vị trí dòng thứ 5 trong bảng.
Vị trí Tất cổ chân
Bước 2:
Bây giờ chúng ta sẽ tìm giá trị tại cột Chất liệu ứng với giá trị tại dòng số 5, sẽ ra được chất liệu cho mặt hàng Tất cổ chân.
Công thức kết hợp hàm Index với hàm Match là =INDEX( cột cần tra cứu giá trị, (MATCH (giá trị dùng để tra cứu, cột chứa giá trị, 0)).
Áp dụng vào bài tập 1 thì có thể thay cả cụm MATCH (giá trị dùng để tra cứu, cột chứa giá trị, 0)) = 5, thứ tự của giá trị dùng để tra cứu.
Nhập công thức là =INDEX(D1:D7,5) và nhấn Enter. Trong đó D1:D7 là cột chứa giá trị cần tra cứu.
Tìm giá trị ứng với Tất cổ chân
Kết quả sẽ ra chất liệu Len dệt Kim.
Chất liệu cho Tất cổ chân
Công thức tổng quát kết hợp hàm Index với hàm Match khi áp dụng vào bài tập 1 là =INDEX(D1:D7,MATCH("Tất cổ chân",B1:B7,0)) rồi nhấn Enter.
Công thức cho hàm INDEX với hàm MATCH
Kết quả cũng cho ra Len dệt kim ứng với Tất cổ chân.
Kết quả cho hàm INDEX với hàm MATCH
Bài tập 2: Tìm mặt hàng ứng với chất liệu Vải lanh trong bảng (Tìm từ trái sang phải)
Bước 1:
Trước hết chúng ta cần tìm vị trí của Vải lanh trong cột Chất liệu. Bạn nhập công thức =MATCH(“Vải lanh”,D1:D7,0) rồi nhấn Enter. Trong đó:
  • Vải lanh: Cần tìm vị trí cho giá trị này.
  • D1:D7: Vùng dữ liệu chứa giá trị cần tìm vị trí.
  • 0: Tìm chính xác giá trị này.
Tìm vị trí Vải lanh
Kết quả ra 7, Vải lanh nằm ở dòng số 7 tại cột Chất liệu.
Thứ tự cho Vải lanh
Bước 2:
Kết hợp với hàm Index chúng ta có công thức là =INDEX(B1:B7,7) rồi nhấn Enter.
Mặt hàng ứng với Vải lanh
Kết quả sẽ hiển thị Quần nữ ngắn đúng với bảng.
Mặt hàng cho vải lanh
Chúng ta sẽ kết hợp hàm Index với hàm Match trong cùng 1 công thức nhập là =INDEX(B1:B7,MATCH(“Vải lanh”,D1:D7,0)) rồi nhấn Enter.
Kết hợp hàm INDEX và MATCH
Kết quả cũng cho ra ứng với Quần nữ ngắn.
Tìm giá trí
Như vậy khi kết hợp hàm Index với hàm Excel thì chúng ta sẽ tìm kiếm được giá trị theo cả 2 chiều từ trái sang phải hay từ phải sang trái. Với hàm VLOOKUP, chúng ta chỉ có thể tra cứu dữ liệu từ trái qua phải mà thôi.
👍Các bạn tải App Quản Trị Excel cài trực tiếp trên điện thoại Iphone và Android mới nhất tại đây : https://www.quantriexcel.info/p/than-gui-cac-ban-tai-quantriexcel.html

No comments:

Post a Comment

Bạn hãy chia sẻ nhận xét ở đây nhé: