INDEX và MATCH trong Excel 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, Office Whyvn sẽ hướng dẫn bạn cách 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.

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

1.1. 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 đó.

Để 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 ô B13.

2.2. 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.

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.

2. 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ì chúng ta có công thức sau:

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.

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

3.1. 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.

3.2. 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

Ở Sheet2 trong bảng tính, 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.

3.3. Hàm INDEX và hàm 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à 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 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: 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.

4. Ứ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ở Sheet 3 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

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 rằng những thông tin này đã giúp ích cho bạn 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 trong công việc của mình.