Vì sao dùng hàm INDEX và MATCH tốt hơn dùng VLOOKUP trong Excel?

Hàm INDEX và MATCH kết 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, các ví dụ về sử dụng kết hợp INDEX và MATCH sẽ giúp các bạn xử lý những trường hợp mà VLOOKUP không 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

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.

Xem thêm: Hướng dẫn dùng hàm INDEX dạng mảng để tham chiếu nhiều kết quả cùng lúc trên Excel

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à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 – 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ợ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

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.

Xem thêm: Hướng dẫn cách tìm dòng cuối có chứa dữ liệu của một cột bằng hàm Match trong Excel

Sử dụng kết hợp hàm 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ì 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 hàm INDEX và MATCH lại tốt hơn VLOOKUP ở nhiều trường hợp.

Xem thêm: Hướng dẫn một số hàm excel thông dụng trong kế toán tiền lương, nhân sự: Hàm Index, Match

Vì sao hàm 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ùng VLOOKUP để 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 ví dụ 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.

Xem thêm: Hướng dẫn ứng dụng Index+match trong quản lý hợp đồng lao động

Hàm Index và Match trong Excel - Ví dụ công thức 

Bây giờ, bạn đã biết lý do nên học hàm MATCH/INDEX, nên hãy đi vào phần thú vị nhất và xem xem bạn có thể áp dụng nguyên lý hay không.

Cách tìm kiếm giá trị sang trái với hàm Index và 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 thể 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 bao giờ công thức VLOOKUP trả về kết quả mà bạn mong muốn.

Hàm INDEX MATCH trong Excel thì linh hoạt hơn và thật sự không quan tâm việc cột trả về nằm ở đâu. Ví dụ, chúng ta sẽ dùng một bảng – bảng này liệt kê thủ đô và dân số. Lần này, hãy lập một công thức INDEX MATCH tìm xem thủ đô của Nga, Matxcơva, xếp thứ mấy dựa theo dân số.

Như bạn có thể thấy từ ảnh chụp màn hình bên dưới, công thức sau đây hoạt động 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ẽ bạn chẳng gặp chút khó khăn nào trong việc hiểu công thức hoạt động như thế nào:

Đầu tiên, bạn lập công thức MATCH đơn giản tìm vị trí của Nga:

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

Rồi, bạn xác định thông số 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 kết hợp hai phần lại và có công thức:

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

Mẹo hay: 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.

Xem thêm: Hướng dẫn cách sử dụng hàm vlookup kết hợp hàm Match khi tìm theo nhiều cột chứa kết quả

Ứng dụng 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

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.

Xem thêm: Hướng dẫn giải bài tập dùng hàm INDEX kết hợp hàm MATCH có file mẫu

Kết luận

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.

Để học thêm các kiến thức chuyên sâu về Excel, hãy tham gia khóa học trực tuyến Tuyệt đỉnh Excel của Gitiho:

Tuyệt đỉnh Excel: Trở thành bậc thầy Excel sau 16 giờ

Khóa học giúp các bạn trang bị kiến thức Excel từ cơ bản đến nâng cao. Các bạn không cần lo lắng nếu như bạn chưa biết gì về Excel. Các bài học đã được sắp xếp theo hệ thống nâng dần độ khó để bạn dễ dàng nắm bắt kiến thức. Khóa học cũng không giới hạn thời gian và số lần học nên các bạn có thể học lại bài trước nếu lỡ quên kiến thức. Các giảng viên Excel của Gitiho cũng sẵn sàng hỗ trợ và trả lời câu hỏi giúp bạn hiểu rõ bài học hơn trong vòng 24h. Chúc các bạn học tập hiệu quả!