7 biến thể của Index-Match và mở rộng

vào

 



Index-Match là bộ hàm dò tìm phổ biến với vô vàn tính năng. So sánh vơi hàm quốc dân như Vlookup, Index-Match có ưu điểm là không cần quét nguyên bảng, không cần đếm số cột và vị trí cột dò có thể nằm ở bất kỳ đâu.

Ứng dụng:Dò tìm, xử lý data, so sánh, kiểm tra, tạo báo cáo -Chart-Dashboard động, tìm Min/Max, tạo biểu mẫu -Form tự động, tự động hóa báo cáo,...

Dưới đây là một số biến thể với tính năng nổi bật, từ cơ bản đến nâng cao:

1. Index+Match 1 điều kiện dọc :90% công việc của mình dùng hàm này

=INDEX($H$2:$H$8,MATCH(N2,$G$2:$G$8,0))
Trong đó:
  • $H$2:$H$8 :cột chứa giá trị cần lấy
  • N2 :Gía trị dùng để dò tìm
  • $G$2:$G$8 :cột chứa giá trị dò tìm.

2. Index+Match Ngang 1: Data nằm dọc nhưng bảng kết quả nằm ngang

=INDEX($H$2:$H$8,MATCH(S$1,$G$2:$G$8,0))

Trong đó:

  • $H$2:$H$8 :cột chứa giá trị cần lấy
  • S$1 :Gía trị dùng để dò tìm (cố định dòng)
  • $G$2:$G$8 :cột chứa giá trị dò tìm.

3. Index+Match Ngang 2:Data nằm ngang nhưng bảng kết quả nằm dọc

=INDEX($H$13:$N$13,MATCH($G18,$H$12:$N$12,0))

Trong đó:

  • $H$13:$N$13 :dòng chứa giá trị cần lấy
  • $G18 :Gía trị dùng để dò tìm (cố định cột)
  • $H$12:$N$12 :dòng chứa giá trị dò tìm.

4. Index+Match 2 điều kiện dòng và cột:Dùng 1 công thức dò cho cả bảng.

Bảng dưới yêu cầu dò 3 cột theo WO+các Title ở dòng 1

=INDEX($H$2:$J$8,MATCH($N2,$G$2:$G$8,0),MATCH(O$1,$H$1:$J$1,0))

Trong đó:

  • $H$2:$J$8 :bảng chứa giá trị cần lấy
  • $N2 :Gía trị dùng để dò tìm theo cột (cố định cột)
  • $G$2:$G$8:cột chứa giá trị dò tìm.
  • O$1 :Gía trị dùng để dò tìm theo dòng (cố định dòng)
  • $H$1:$J$1:dòng chứa giá trị dò tìm
**Lưu ý:Match cột trước, Match dòng sau

5.Index+Match nhiều cột điều kiện : Tạo thêm cột hỗ trợ

Bảng dưới yêu cầu tìm Payment theo WO+Criteria A+Service nên ta tạo thêm 1 cột hỗ trợ ở bảng Data gốc với công thức là nội dung của 3 cột  WO+Criteria A+Service

Sau đó dùng Index+Match như bình thường:

=INDEX($J$2:$J$8,MATCH(M2&N2&O2,$K$2:$K$8,0))

Trong đó:

  • $J$2:$J$8 :bảng chứa giá trị cần lấy
  • M2&N2&O2 :các gía trị dùng để dò tìm theo cột (nên cố định cột)
  • $K$2:$K$8:cột chứa giá trị dò tìm.

Index+Match nhiều điều kiện cho bảng ngang hoặc Index+Match nhiều điều kiện dòng và cột cũng tạo cột hỗ trợ tương tự.

6. Index+Match nhiều điều kiện không dùng cột hỗ trợ :Công thức mảng


={INDEX($J$2:$J$8,MATCH(1,($M2=$G$2:$G$8)*($N2=$H$2:$H$8)*($O2=$I$2:$I$8),0))}

**Ctrl+Shift+Enter

Trong đó:

  • $J$2:$J$8 :cột chứa giá trị cần lấy
  • $M2:Gía trị dò 1
  • $G$2:$G$8:Cột chứa giá trị dò 1
  • $N2:Gía trị dò 2
  • $H$2:$H$8:Cột chứa giá trị dò 2
  • $O2:Gía trị dò 3
  • $I$2:$I$8:Cột chứa giá trị dò 3
Mở rộng 1: Tìm Payment theo 2 dòngWO+Criteria A và cột Service
=INDEX($J$2:$J$8,MATCH(1,(H$12=$G$2:$G$8)*(H$13=$H$2:$H$8)*($G14=$I$2:$I$8),0))

Mở rộng 2:Index+Match nhiều điều kiện theo cột+dòng không dùng cột hỗ trợ.

=INDEX($H$14:$O$16,MATCH(I21,$G$14:$G$16,0),MATCH(1,($G21=$H$12:$O$12)*($H21=$H$13:$O$13),0))

**Lưu ý:Match cột trước, Match dòng sau


7. Index+Match nhiều điều kiện không dùng cột hỗ trợ và không dùng công thức mảng


=INDEX($J$2:$J$8,MATCH(1,INDEX((H$12=$G$2:$G$8)*(H$13=$H$2:$H$8)*($G14=$I$2:$I$8),0),0))

Trong đó:

  • $J$2:$J$8 :cột chứa giá trị cần lấy
  • H$12:Gía trị dò 1
  • $G$2:$G$8:Cột chứa giá trị dò 1
  • H$13:Gía trị dò 2
  • $H$2:$H$8:Cột chứa giá trị dò 2
  • $G14:Gía trị dò 3
  • $I$2:$I$8:Cột chứa giá trị dò 3


Nhận xét

Đăng nhận xét