Cách kết hợp hàm Index và hàm Match trong Excel

Việc kết hợp hàm Index và hàm Match sẽ tốt hơn việc sử dụng hàm VLOOKUP trong nhiều trường hợp. Bài viết dưới đây sẽ hướng dẫn bạn cách kết hợp hàm Index và hàm Match trong Excel.

Video hướng dẫn cách dùng hàm Index, hàm Match trong Excel

Cách sử dụng hàm Index và Match trong Excel cơ bản

Hàm INDEX

Hàm INDEX sẽ trả về kết quả dữ liệu của một ô trong một vùng dựa trên chỉ số hàng và chỉ số cột của vùng đó. Về cơ bản, hàm INDEX có công thức như sau:

=INDEX(vùng_dữ_liệu, hàng_thứ_mấy, [cột_thứ_mấy])

  • vùng_dữ_liệu – là địa chỉ vùng dữ liệu chúng ta muốn “nhặt” ra 1 giá trị
  • hàng_thứ_mấy – có ý nghĩa ô cần lấy dữ liệu nằm ở hàng thứ mấy?
  • cột_thứ_mấy – có ý nghĩa ô cần lấy dữ liệu nằm ở cột thứ mấy?

Để thử dùng hàm INDEX bạn hoàn toàn có thể thử trực tiếp câu lệnh sau trên bảng tính phía dưới

=INDEX(A1:D10,6,3)

Hàm INDEX tìm dữ liệu trong vùng dữ liệu A1:D10 và trả lại dữ liệu ở dòng thứ 6, cột thứ 3, tức là ô C6. Kết quả của câu lệnh INDEX trên sẽ là Seoul như trong ô B14

cach-dung-ham-index-ham-match-trong-excel-youtube-01

Hàm MATCH

Hàm MATCH này giúp tìm kiếm 1 giá trị trong 1 vùng của bảng tính và đưa lại vị trí tương đối của ô chứa giá trị đó trong vùng cần tìm kiếm. Cú pháp của hàm MATCH như sau:

=MATCH(giá_trị_cần_tìm, mảng_tìm_kiếm, [kiểu_tìm_kiếm])

  • giá_trị_cần_tìm – giá trị cần được tìm kiếm vị trí trong mảng
  • mảng_tìm_kiếm – mảng chứa giá trị cần tìm kiếm
  • kiểu_tìm_kiếm – có ý nghĩa là tìm kiếm giá trị chính xác hay tìm kiếm giá trị gần nhất.
    • 1 – hoặc bỏ qua: có ý nghĩa là tìm giá trị lớn nhất mà giá trị đó bằng hoặc nhỏ hơn giá_trị_cần_tìm. Giá trị trong mảng_tìm_kiếm cần được sắp xếp tăng dần
    • 0 – có ý nghĩa là tìm giá trị đầu tiên trong mảng bằng với giá_trị_cần_tìm. Đây là giá trị mà chúng ta sẽ rất hay dùng khi sử dụng kết hợp INDEX/MATCH
    • -1 – tìm giá trị nhỏ nhất mà giá trị đó lơn hơn hoặc bằng giá_trị_cần_tìm
Xem thêm :   【Sửa Lỗi】Cắm Tai Nghe Vào Máy Tính Không Nghe Được Win 7,10

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

Bạn hoàn toàn có thể hiểu được sự kết hợp của hàm INDEX và MATCH trong Excel như thế nào trong ví dụ dưới đây. Bạn có bảng Excel sau đây:

cach-dung-ham-index-ham-match-trong-excel-youtube-02

Bạn muốn đi tìm kiếm xem nước nào có thủ đô là Seoul trong bảng này. Công thức để bạn tìm ra điều đó áp dụng công thức như sau:

=INDEX(B1:B10,MATCH(“Seoul”,C1:C10,0)

Trong công thức này:

  • B1:B10 là cột chứa dữ liệu mà chúng ta muốn tra cứu, tìm kiếm hoặc trích lọc
  • MATCH(“Seoul”,C1:C10,0) sẽ cho bạn biết dữ liệu Seoul ở vị trí hàng thứ mấy ở trong bảng tính trên. Kết quả dữ liệu Seoul ở vị trí hàng thứ 6 trong bảng tính trên. Công thức với hàm INDEX sẽ trở thành: INDEX(B1:B10,6)
  • Khi bạn kết hợp 2 công thức này lại và do tính chất của bảng tính trên, Seoul sẽ phải nằm trong cùng dòng với nước có thủ đô là Seoul nên bạn hoàn toàn có thể sử dụng cách này để tìm ra được kết quả là Hàn Quốc

Tổng quát lại thì bạn có công thức áp dụng sau:

=INDEX( cột cần tra cứu giá trị, (MATCH ( giá trị dùng để tra cứu, cột chứa giá trị này, 0 ))

Và một lần nữa, các bạn hoàn toàn có thể thực hành kết hợp 2 hàm INDEX và MATCH trong bảng tính Excel. 

Vì sao kết hợp hàm Index và Match tốt hơn hàm VLOOKUP

VLOOKUP là công thức 1 chiều

Với hàm VLOOKUP, bạn chỉ có thể tra cứu dữ liệu từ trái qua phải. Trong ví dụ tìm nước có thủ đô là Seoul ở trên, bạn sẽ không dùng VLOOKUP để có thể tìm ra Hàn Quốc được. Như các bạn đã thấy, việc kết hợp hàm INDEX và MATCH làm được điều này.

VLOOKUP sẽ bị sai nếu bạn thêm hoặc bớt cột ở trong bảng tính 

Cái này thì rõ ràng rồi, bởi vì khi bạn dùng VLOOKUP các bạn phải chỉ ra cột nào bạn muốn lấy giá trị về. Khi bạn thêm hoặc bớt 1 cột ở giữa cột đầu tiên và cột cần lấy giá trị về thì cột cần lấy giá trị bị chênh lệch đi, dẫn đến kết quả của hàm VLOOKUP không đúng nữa. Với việc kết hợp hàm INDEX và MATCH thì điều này không xảy ra vì khi thêm hoặc bớt cột thì công thức trong hàm INDEX và MATCH sẽ được điều chỉnh theo.

Hàm VLOOKUP sẽ khó khăn khi tra cứu 2 chiều như trong ví dụ sau đây

cach-dung-ham-index-ham-match-trong-excel-youtube-03

Bạn có thể mở Sheet2 trong bảng tính phía trên ra để thực hiện. Nếu bạn muốn tra cứu dân số của Seoul vào năm 2001 thì trong trường hợp này bạn hoàn toàn có thể dùng hàm VLOOKUP – một cách khó khăn, nhưng dùng kết hợp hàm INDEX và MATCH sẽ nhanh gọn hơn như sau:

=INDEX(A1:F10,MATCH(“Seoul”,C1:C10,0),MATCH(“Năm 2001”,A1:F1,0))

  • Hàm Match đầu tiên sẽ cho chúng ta biết thủ đô Seoul của Hàn Quốc nằm ở dòng thứ mấy
  • Hàm Match thứ hai sẽ cho chúng ta biết dữ liệu dân số của Năm 2001 nằm ở cột nào
  • Hàm Index sẽ sử dụng 2 thông tin trên và tìm ra dữ liệu chính xác cho bạn.

Ứng dụng kết hợp hàm index và match nâng cao

Dò tìm dữ liệu dựa trên nhiều cột điều kiện

cach-dung-ham-index-ham-match-trong-excel-youtube-04

Yêu cầu của bài này là bạn phải điền vào D5:D16 dựa trên dữ liệu ở bảng tra cứu. Công thức áp dụng như sau:

{=INDEX($F$5:$H$16,MATCH(1,(A5=$F$5:$F$16)*(B5=$G$5:$G$16),0),3)}

  • Dấu {} – thể hiện rằng đây là một công thức mảng, các bạn khi nhập công thức mảng bạn cần phải sử dụng phím đặc biệt của Excel là CTRL + SHIFT + ENTER. Khi nhập công thức vào Excel, các bạn nhập bình thường, không cần dấu {} và sử dụng phím CTRL + SHIFT + ENTER.
  • Bạn hoàn toàn có thể copy bảng tính này về Excel của bạn và thử công thức đó.
  • Trong công thức trên, phần khó nhất là phần có hàm Match – MATCH(1,(A5=$F$5:$F$16)*(B5=$G$5:$G$16),0):
    • (A5=$F$5:$F$16) : Chúng ta đi so sánh giá trị của A5 với vùng $F$5:$F$16. Kết quả nhận được là {FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
    • (B5=$G$5:$G$16) : Chúng ta đi so sánh giá trị của B5 với vùng $G$5:$G$16. Kết quả nhận được là{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}
    • (A5=$F$5:$F$16)*(B5=$G$5:$G$16): Kết quả của phép tính này là{0;1;0;0;0;0;0;0;0;0;0;0}
    • Do đó Hàm Match của chúng ta từ MATCH(1,(A5=$F$5:$F$16)*(B5=$G$5:$G$16),0) trở thành:MATCH(1,{0;1;0;0;0;0;0;0;0;0;0;0},0) kết quả của hàm match này là 2, dòng thứ 2 trong bảng tra cứu sẽ thỏa mãn điều kiện Dan BrownTáo, Tổng phải tìm là 271
Xem thêm :   Cách dùng hàm Weekday trong Excel

We will be happy to hear your thoughts

Leave a reply

Wiki19.com
Logo
Enable registration in settings - general