INDEX và MATCH tích hợp với nhau sẽ tốt hơn sử dụng VLOOKUP trong nhiều trường hợp ! Trong bài viết này, những ví dụ về sử dụng tích hợp INDEX và MATCH sẽ giúp những bạn giải quyết và xử lý những trường hợp mà VLOOKUP không giải quyết và xử lý được .

“Tại sao lại học thêm 2 hàm nữa?”. Bởi vì hàm VLOOKUP không phải là công cụ duy nhất của bạn, INDEX và MATCH sẽ giúp bạn làm việc hiệu quả hơn, nhanh hơn, ít lỗi hơn. Ngoài ra, hàm INDEX MATCH rất linh hoạt và có thể tuỳ biến cho nhiều trường hợp khác.

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

Vì bài viết này tập trung vào những trường hợp dùng INDEX và MATCH sẽ thuận lợi hơn việc dùng VLOOKUP nên để tìm hiểu cách sử dụng cơ bản của INDEX và MATCH mời các bạn xem video sau đây. File Excel đi kèm video các bạn download ở cuối bài.

Hàm INDEX

Hàm INDEX sẽ trả về dữ liệu của một ô trong 1 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ú pháp 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ần lấy dữ liệu nằm ở hàng thứ mấy?
• cột_thứ_mấy – ô cần lấy dữ liệu nằm ở cột thứ mấy?

Để thử dùng hàm INDEX chúng ta 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 A1:D10 và trả lại dữ liệu trong 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

Hàm MATCH

Hàm MATCH 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 tìm kiếm. Cú pháp của hàmMATCH 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 – 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: 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 – 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ợpINDEX/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

Khi đọc đến đây, chắc các bạn sẽ tự hỏi là Cái hàm MATCH này hình như không tác dụng lắm thì phải. Đúng thế, nếu chỉ như vậy thì hàm MATCH không có nhiều tác dụng lắm. Nhưng ở phần tiếp theo, khi kết hợp cùng hàm INDEX thì chúng ta hãy xem xem cặp đôi này làm gì được cho các bạn.

Sử dụng kết hợp INDEX và MATCH trong Excel

Chúng ta sẽ đi làm ngay một ví dụ trước để các bạn có thể hiểu được sự kết hợp của hàm INDEX và MATCH trong Excel như thế nào. Chúng ta có bảng Excel sau đây:

Chúng ta 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 để tìm ra điều đó 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 chúng ta biết Seoul ở vị trí hàng thứ mấy ở trong bảng tính trên. Kết quả Seoul ở vị trí hàng thứ 6 trong bảng tính. Công thức với INDEX trở thành: INDEX(B1:B10,6)
• Khi 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, Seoul sẽ phải nằm cùng dòng với nước có thủ đô là Seoul nên ta 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ì tất cả chúng ta có công thức 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 có thể thực hành INDEX và MATCH trong bảng tính Excel online phía trên. Khi các bạn đã chắc tay với INDEX và MATCH* chúng ta sẽ xem phần tiếp theo tại sao **INDEX và MATCH lại tốt hơn VLOOKUP ở nhiều trường hợp.

Vì sao INDEX và MATCH tốt hơn VLOOKUP

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

Với VLOOKUP, chúng ta 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, chúng ta sẽ không dùngVLOOKUP để có thể tìm ra Hàn Quốc được. Như các bạn đã thấy, INDEX và MATCH làm được điều này.

VLOOKUP sẽ bị sai nếu chúng ta 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 dùng VLOOKUP các bạn phải chỉ ra cột nào chúng ta muốn lấy giá trị về. Khi 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ị xê dịch đi, dẫn đến kết quả của hàm VLOOKUP không đúng nữa. Với INDEX và MATCH thì điều này không xảy ra vì khi thêm/bớt cột thì công thức trong hàm INDEX và MATCH sẽ được điều chỉnh theo.

VLOOKUP sẽ gặp khó khăn khi tra cứu 2 chiều như trong VD sau đây

Các bạn có thể mở Sheet2 trong bảng tính phía trên ra. Nếu chúng ta muốn tra cứu dân số của Seoul vào năm 2001 thì trong trường hợp này chúng ta có thể dùng VLOOKUP – một cách khó khăn, nhưng dùng 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 chúng ta.

HÀM INDEX VÀ HÀM MATCH TRONG EXCEL – VÍ DỤ CÔNG THỨC

Bây giờ, bạn đã biết nguyên do nên học hàm MATCH / INDEX, nên hãy đi vào phần mê hoặc nhất và xem xem bạn hoàn toàn có thể vận dụng nguyên tắc hay không .

CÁCH TÌM KIẾM GIÁ TRỊ SANG TRÁI VỚI HÀM INDEX VÀ HÀM MATCH

Như đã được nhắc đến trong bất kể bài hướng dẫn VLOOKUP nào, hàm Excel này không hề tìm kiếm bên trái của nó. Vì vậy, nếu cột cần tìm của bạn không phải là cột cận trái trong dải ô cần tìm, thì không khi nào công thức VLOOKUP trả về hiệu quả mà bạn mong ước .
Hàm INDEX MATCH trong Excel thì linh động hơn và thật sự không chăm sóc việc cột trả về nằm ở đâu. Ví dụ, tất cả chúng ta sẽ dùng một bảng – bảng này liệt kê TP. hà Nội và dân số. Lần này, hãy lập một công thức INDEX MATCH tìm xem thủ đô hà nội của Nga, Matxcơva, xếp thứ mấy dựa theo dân số .
Như bạn hoàn toàn có thể thấy từ ảnh chụp màn hình hiển thị bên dưới, công thức sau đây hoạt động giải trí một cách trôi chảy :

=INDEX($A$2:$A$10,MATCH(“Russia”,$B$2:$B$10,0))

Bây giờ, có lẽ rằng bạn chẳng gặp chút khó khăn vất vả nào trong việc hiểu công thức hoạt động giải trí như thế nào :
Đầu tiên, bạn lập công thức MATCH đơn thuần tìm vị trí của Nga :
= MATCH ( “ Russia ”, USD B USD 2 : USD B $ 10,0 ) )
Rồi, bạn xác lập thông số kỹ thuật mảng cho hàm INDEX, trong trường hợp này là cột A ( A2 : A10 ) .
Cuối cùng, bạn tích hợp hai phần lại và có công thức :
= INDEX ( $ A $ 2 : USD A $ 10, MATCH ( “ Russia ”, USD B USD 2 : USD B $ 10,0 ) )

Mẹo. Việc sử dụng tham chiếu ô tuyệt đối luôn là một ý tưởng hay trong công thức INDEX và MATCH để dải ô cần tìm của bạn không bị phá hỏng khi bạn sao chép công thức sang ô khác.

Ứng dụng INDEX và MATCH nâng cao

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

Các bạn hãy mở Sheet3 trong bảng tính sau đây:

Yêu cầu bài này là chúng ta phải điền vào D5:D16 dựa trên dữ liệu ở bảng tra cứu. Công thức 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 cần 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.
• Công thức trên chưa được hỗ trợ trực tiếp trong phiên bản hiện tại của Excel Online, nếu bạn muốn thử, 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ẽ thoả mãn điều kiện Dan Brown và Táo, Tổng phải tìm là 271

Còn gì nữa sau bài viết này

Qua bài viết này, chắc các bạn cũng đã biết là ngoài VLOOKUP ra thì còn một công thức khó dùng khác nữa là INDEX và MATCH cũng khá là có ích khi mà gặp phải trường hợp VLOOKUP không xử lý được. Mong đây là bước khởi đầu tốt cho các bạn. Nếu bạn đã đọc đến đây, cảm ơn bạn rất nhiều.

Tài liệu Excel đi kèm:

• Hàm Index và Match trong Excel – 1

• Hàm Index và Match trong Excel – 2

Các kiến thức liên quan:

• Hàm Index và Match cơ bản
• Hàm Index và Match nâng cao

Những kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của Học Excel Online. Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống: về các hàm, các công cụ trong excel, ứng dụng excel trong công việc… Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này.

Source: http://wp.ftn61.com
Category: Tin Tức