Hàm INDEX Và Hàm MATCH Trong Excel - Tốt Hơn Cả Hàm Vlookup

Bài viết này diễn giải ưu điểm chính của hàm INDEX và hàm MATCH trong Excel – các ưu điểm khiến nó phù hợp hơn so với hàm VLOOKUP. Bạn sẽ tìm thấy nhiều ví dụ công thức – những ví dụ giúp bạn dễ dàng xử lý các nhiệm vụ phức tạp khi hàm VLOOKUP không làm được.

Trong các bài viết gần đây tôi đã cố gắng hết mình để giải thích những điểm cơ bản của hàm VLOOKUP trong Excel cho người mới bắt đầu và cung cấp các ví dụ công thức hàm VLOOKUP nâng cao. Và bây giờ, tôi sẽ cố gắng nếu đã không nói về việc sử dụng hàm VLOOKUP, thì ít nhất sẽ cung cấp cách thay thế để thực hiện phép tìm kiếm theo cột trong Excel.

“Tôi cần nó để làm gì?” bạn có thể hỏi tôi như thế. Bởi vì hàm VLOOKUP không phải là hàm tìm kiếm duy nhất có sẵn trong Excel, và việc có nhiều hạn chế có thể ngăn cản bạn đạt được kết quả mong muốn trong nhiều tình huống. Mặt khác, hàm INDEX và hàm MATCH trong Excel thì linh hoạt hơn và có những tính năng nhất định khiến chúng vượt trội hơn hàm VLOOKUP trên nhiều phương diện.

HÀM INDEX VÀ HÀM MATCH TRONG EXCEL – NHỮNG ĐIỀU CƠ BẢN

Vì mục đích của bài viết này là để nói về cách tìm kiếm thay thế trong Excel bằng cách kết hợp hàm INDEX với hàm MATCH, nên chúng ta sẽ không nói nhiều về cú pháp và cách sử dụng. Chúng ta sẽ chỉ nói đủ để hiểu ý tưởng chung rồi nói kỹ hơn về các ví dụ công thức – các ví dụ cho thấy ưu điểm của việc sử dụng hàm INDEX và hàm MATCH thay cho hàm VLOOKUP.

CÚ PHÁP VÀ CÁCH DÙNG CỦA HÀM INDEX:

Hàm INDEX trong Excel trả về giá trị từ mảng dựa trên số thứ tự hàng và cột bạn xác định. Cú pháp hàm INDEX rất đơn giản:

INDEX(array, row_num, [column_num])

Dưới đây là cách giải thích đơn giản cho mỗi thông số:

array – đây là dải ô nơi bạn muốn giá trị được trả về.

row_num – số thứ tự của hàng trong mảng mà bạn muốn giá trị được trả về. Nếu bỏ trống, thì colum_num là bắt buộc.

colum_num – số thứ tự của cột trong mảng mà bạn muốn giá trị được trả về. Nếu bỏ trống, thì row_num là bắt buộc.

Nếu cả thông số row_num và colum_num đều được sử dụng, thì hàm INDEX sẽ trả về giá trị ở ô là giao điểm của hàng và cột xác định.

Và đây là ví dụ đơn giản nhất của hàm INDEX:

=INDEX(A1:C10,2,3)

Công thức tìm kiếm dải ô A1:C10 rồi trả về giá trị của ô ở hàng thứ hai và cột thứ ba, cụ thể là ô C2.

Rất dễ, đúng không? Tuy nhiên, khi xử lý dữ liệu thực, bạn hầu như không biết được mình muốn hàng nào cột nào, đây là lý do bạn cần sự giúp đỡ của hàm MATCH.

CÚ PHÁP VÀ CÁCH DÙNG CỦA HÀM MATCH:

Hàm MATCH trong Excel tìm kiếm giá trị cần tìm trong dải ô, rồi trả về vị trí tương đối của ô đó trong dải ô.

Ví dụ, nếu dải ô B1:B3 chứa các giá trị “New-York”, “Paris”, “London”, rồi công thức =MATCH(“London”,B1:B3,0) trả về số 3 bởi vì “London” là mục thứ ba trong dải ô.

Cú pháp hàm MATCH có dạng dưới đây:

MATCH(lookup_value, lookup_array, [match_type])

lookup_value – đây là chữ sô hay chuỗi ký tự mà bạn tìm kiếm. Đây có thể là một giá trị, một tham chiếu ô hay một giá trị lô gic.

lookup_array – dải ô được tìm kiếm.

match_type – thông số này nói cho hàm MATCH biết bạn muốn trả về sự phù hợp tuyệt đối hay sự phù hợp tương đối.

1 hay bỏ trống – tìm giá trị lớn nhất – giá trị nhỏ hơn hay bằng giá trị cần tìm. Các giá trị trong mảng cần tìm phải được lọc theo thứ tự tăng dần, cụ thể là từ nhỏ nhất đến lớn nhất.

0 – tìm giá trị đầu tiên bằng giá trị cần tìm. Trong sự kết hợp của hàm INDEX và hàm MATCH, bạn hầu như luôn luôn cần sự phù hợp tuyệt đối, vì thế câu lệnh thứ ba của hàm MATCH là “0”.

-1 – tìm giá trị nhỏ nhất lớn hơn hay bằng giá trị cần tìm. Các giá trị trong mảng cần tìm phải được sắp xếp theo thứ tự giảm dần, cụ thể là từ lớn nhất đến nhỏ nhất.

Lúc đầu, tính hữu ích của hàm MATCH có thể không rõ ràng. Ai quan tâm đến vị trí của giá trị trong dải ô chứ? Điều ta thật sự muốn biết đó chính là giá trị.

Hãy để tôi nhắc bạn nhớ rằng vị trí tương đối của giá trị cần tìm (cụ thể là số thứ tự của cột hay/và hàng) chính là tất cả những gì bạn cần để nhập vào câu lệnh row_num hay/và column_num của hàm INDEX. Có thể bạn vẫn còn nhớ, hàm INDEX có thể trả về giá trị ở giao điểm của hàng và cột xác định, nhưng nó không thể xác định một cách chính xác bạn muốn cột nào hàng nào.

Xem thêm: Hướng dẫn học Excel cơ bản

CÁCH SỬ DỤNG HÀM INDEX MATCH TRONG EXCEL:

Bây giờ bạn đã biết những điều cơ bản của hai hàm trên, tôi tin rằng bạn có thể mường tượng hai hàm này sẽ kết hợp như thế nào.

Hàm MATCH xác định vị trí tương đối của giá trị cần tìm trong dải ô xác định. Từ đó, hàm INDEX sử dụng con số đó, hay các con số đó, rồi trả về giá trị ở ô tương ứng.

Vẫn gặp khó khăn trong việc mường tượng ra đúng không? Hãy nghĩ về hàm INDEX/MATCH theo cách này:

=INDEX (column to return a value from, MATCH (lookup value, column to lookup against, 0))

Tôi tin rằng việc xem ví dụ sẽ dễ hiểu hơn. Giả sử bạn có một danh sách các thủ đô như thế này:

Hãy tìm dân số của một thủ đô nào đó, giả sử thủ đô của Nhật Bản đi, hãy dùng công thức INDEX/MATCH dưới đây:

=INDEX($D$2:$D$10,MATCH(“Japan”,$B$2:$B$10,0))

Bây giờ, hãy phân tích xem mỗi thành phần của công thức có tác dụng gì:

Hàm MATCH tìm kiếm giá trị cần tìm “Nhật Bản” ở cột B, chính xác hơn là dải ô B2:B10,  rồi trả về số 3, bởi vì “Nhật Bản” nằm thứ ba trong danh sách.

Hàm INDEX lấy số 3 từ thông số thứ hai (row_num), thông số này chỉ rõ bạn muốn trả về giá trị từ hàng nào, rồi trở thành công thức đơn giản như sau =INDEX($D$2:$D$10,3). Nói một cách đơn giản, công thức được đọc như sau: tìm trong dải ô D2:D10 rồi trả về giá trị của ô ở hàng thứ ba, cụ thể là ô D4, bởi vì chúng ta bắt đầu đếm từ hàng thứ hai.

Và đây là kết quả bạn có được trong Excel:

Quan trọng! Số hàng và số cột trong mảng INDEX nên lần lượt khớp với số hàng và số cột trong thông số row_num hay/và column_num trong hàm MATCH. Nếu không thì, công thức sẽ trả về kết quả không chính xác.

Đợi đã…tại sao chúng ta không dùng công thức VLOOKUP sau? Mục đích của việc lãng phí thời gian cố gắng tìm kiếm sự thay thế bí ẩn của hàm INDEX/MATCH trong Excel là gì?

=VLOOKUP(“Japan”,$B$2:$D$2,3)

Trong trường hợp này, chẳng có mục đích nào cả 🙂 Ví dụ đơn giản này chỉ dành cho việc diễn giải mà thôi, để bạn thấy được cách hàm INDEX và hàm MATCH kết hợp với nhau. Các ví dụ khác bên dưới sẽ cho bạn thấy khả năng thật sự của sự kết hợp hàm MATCH/INDEX – điều này giúp bạn dễ dàng xử lý nhiều viễn cảnh phức tạp khi hàm VLOOKUP không xử lý được.

TẠI SAO SỰ KẾT HỢP HÀM INDEX VÀ HÀM MATCH TRONG EXCEL LẠI TỐT HƠN HÀM VLOOKUP

Khi quyết định công thức dùng tìm kiếm theo cột, phần lớn các chuyên gia Excel cho rằng hàm INDEX kết hợp với hàm MATCH thì hữu ích hơn hàm VLOOKUP rất nhiều. Tuy nhiên, rất nhiều người dùng Excel vẫn viện đến hàm VLOOKUP vì nó đơn giản. Điều này diễn ra vì rất ít người hiểu trọn vẹn tất cả các lợi ích của việc đổi từ hàm VLOOKUP thành hàm INDEX kết hợp với hàm MATCH, và vì lý do này nên chẳng ai chịu dành thời gian học một công thức phức tạp hơn.

Dưới đây, tôi sẽ cố gắng chỉ ra các lợi ích chính của việc kết hợp hàm INDEX với hàm MATCH trong Excel, rồi để bạn quyết định liệu bạn vẫn dùng hàm VLOOKUP hay dùng hàm INDEX kết hợp với hàm MATCH.

4 LỢI ÍCH CHÍNH CỦA VIỆC KẾT HỢP HÀM INDEX VỚI HÀM MATCH TRONG EXCEL:

• Tìm kiếm từ phải sang trái. Bất cứ người dùng thông thạo nào cũng biết, hàm VLOOKUP trong Excel không thể tìm kiếm phía bên trái, có nghĩa là giá trị cần tìm phải luôn nằm ở cột cận trái của dải ô cần tìm. Với hàm INDEX MATCH, cột cần tìm của bạn có thể nằm ở phía bên phải của bảng.
• Chèn hay xóa cột một cách an toàn. Công thức VLOOKUP bị lỗi hay trả về kết quả không chính xác khi xóa hay thêm cột mới vào bảng cần tìm. Với hàm VLOOKUP, bất cứ cột nào được chèn hay xóa cũng có thể thay đổi kết quả trả về bởi công thức bởi vì cú pháp hàm VLOOKUP yêu cầu định rõ toàn bộ mảng bảng và một số nhất định để chỉ bạn muốn lấy dữ liệu từ cột nào.

Ví dụ: nếu bạn có bảng A1:C10 và muốn trả về giá trị từ cột B, bạn sẽ đặt số “2” ở thông số thứ ba (col_index_num) của công thức VLOOKUP, ví dụ =VLOOKUP(“lookup value”, A1:C10, 2). Nếu sau đó, bạn chèn một cột mới giữa cột A và cột B, thì bạn phải thay đổi “2” thành “3” trong công thức, nếu không thì nó sẽ trả về giá trị từ cột mới được chèn vào.

• Với hàm INDEX MATCH, bạn có thể xóa hay chèn cột mới vào bảng cần tìm mà không thay đổi kết quả bởi vì bạn định rõ cột chứa giá trị cần tìm một cách trực tiếp. Và đây là một lợi ích rất lớn, nhất là khi xử lý chuỗi dữ liệu lớn, vì bạn có khả năng chèn hay xóa mà không cần lo lắng về việc cập nhật mỗi công thức tìm kiếm liên quan.
• Không đặt giới hạn cho kích thước dữ liệu cần tìm. Khi sử dụng hàm VLOOKUP, hãy nhớ rằng tổng chiều dài của tiêu chuẩn tìm kiếm không nên vượt quá 255 ký tự, nếu không thì bạn sẽ nhận được lỗi #VALUE!. Vì thế, nếu chuỗi dữ liệu của bạn chứa một chuỗi dài, thì hàm INDEX MATCH là giải pháp khả thi duy nhất.

Giả sử, bạn sử dụng công thức VLOOKUP dưới đây để tìm kiếm  từ ô B5 đến ô D10 cho giá trị ở ô A2:

=VLOOKUP(A2,B5:D10,3,FALSE)

Công thức sẽ không chạy nếu giá trị cần tìm ở ô A2 vượt quá 255 ký tự. Thay vào đó, bạn nên sử dụng hàm INDEX/MATCH tương tự:

=INDEX(D5:D10, MATCH(TRUE, INDEX(B5:B10=A2, 0), 0))

• Tốc độ xử lý nhanh hơn. Nếu bạn đang xử lý một bảng tương đối nhỏ, thì điểm khác biệt trong việc xử lý của Excel sẽ hầu như không thể nhận thấy, nhất là ở các phiên bản gần đây. Nhưng nếu bạn sử dụng bảng tính lớn có hàng ngàn hàng và hàng ngàn công thức cần tìm, Excel sẽ làm việc nhanh hơn nhiều nếu bạn sử dụng hàm INDEX/MATCH thay vì hàm VLOOKUP. Nói chung, việc sử dụng công thức INDEX/MATCH sẽ tăng tốc độ xử lý của Excel lên 13 % so với công thức VLOOKUP tương tự.

Ảnh hưởng của hàm VLOOKUP lên việc xử lý của Excel có thể sẽ rõ ràng hơn nếu sổ làm việc của bạn chứa hàng trăm công thức mảng phức tạp như VLOOKUP và SUM. Điểm mấu chốt đó là việc kiểm tra mỗi giá trị trong mảng yêu cầu một hàm VLOOKUP khác. Vì thế, mảng càng có nhiều giá trị và sổ làm việc của bạn càng có nhiều công thức mảng, thì Excel xử lý càng chậm.

Mặt khác, với hàm INDEX/MATCH, Excel chỉ phải cân nhắc cột tìm kiếm và cột trả về, nên nó xử lý công thức nhanh hơn nhiều.

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.

TÍNH TOÁN VỚI HÀM INDEX MATCH TRONG EXCEL (AVERAGE, MAX, MIN)

Bạn có thể lồng ghép các hàm Excel khác vào hàm INDEX MATCH, giả sử, để tìm giá trị nhỏ nhất hay lớn nhất, hay giá trị gần với giá trị trung bình nhất trong dải. Sau đây là vài ví dụ công thức cho bảng được dùng trong ví dụ trước:

Chức năng	Ví dụ công thức	Miêu tả	Kết quả được trả về
Min	=INDEX($C$2:$C$10, MATCH(MIN($D$2:I$10), $D$2:D$10, 0))	Tìm kiếm giá trị nhỏ nhất trong cột D rồi lấy một giá trị từ cột C trên cùng một hàng.	Bắc Kinh
Max	=INDEX($C$2:$C$10, MATCH(MAX($D$2:I$10), $D$2:D$10, 0))	Tìm kiếm giá trị lớn nhất ở cột D rồi lấy một giá trị từ cột C trên cùng một hàng.	Lima
Average	=INDEX($C$2:$C$10, MATCH(AVERAGE($D$2:D$10), $D$2:D$10, 1))	Tính giá trị trung bình trong dải ô D2:D10, tìm giá trị gần với giá trị trung bình nhất, rồi lấy giá trị tương ứng từ cột C.	Matx-cơ-va

NHỮNG ĐIỀU CẦN NHỚ KHI SỬ DỤNG HÀM AVERAGE VỚI HÀM INDEX MATCH TRONG EXCEL

Khi kết hợp hàm AVERAGE với hàm INDEX/MATCH, thông thường bạn sẽ nhập “1” hay “-1” trong câu lệnh thứ ba (match_type) của hàm MATCH, nếu bạn không chắc mảng cần tìm có chứa giá trị bằng giá trị trung bình hay không. Nếu bạn không chắc, thì bạn có thể nhập “0” cho sự phù hợp tuyệt đối.

Nếu bạn cài đặt 1, thì các giá trị trong cột cần tìm phải được sắp xếp theo thứ tự tăng dần, và công thức sẽ trả về giá trị lớn nhất – giá trị này có thể nhỏ hơn hay bằng giá trị trung bình.

Nếu bạn cài đặt -1, thì các giá trị trong cột cần tìm phải được sắp xếp theo thứ tự giảm dần, và công thức sẽ trả về giá trị nhỏ nhất – giá trị này có thể lớn hơn hay bằng giá trị trung bình.

Trong ví dụ của chúng ta, các giá trị ở cột D được sắp xếp theo thứ tự tăng dần, nên ta dùng “1” làm match type, và công thức Match+Index Match sẽ trả về “Matxcơva” vì dân số của nó (11,541,000) là giá trị nhỏ hơn nhưng gần với giá trị trung bình nhất (12,269,006).

CÁCH SỬ DỤNG HÀM INDEX MATCH ĐỂ KIẾM GIÁ TRỊ THEO HÀNG VÀ THEO CỘT

Công thức này tương đương công thức VLOOKUP hai chiều – các công thức này cho phép bạn tìm giá trị ở giao điểm của cột và hàng.

Trong ví dụ này, hàm INDEX MATCH trong Excel của bạn sẽ rất giống với các công thức khác mà chúng ta đã bàn trong bài hướng dẫn này, ngoại trừ một điểm khác biệt. Đoán xem nào?

Có lẽ bạn vẫn còn nhớ, cú pháp hàm INDEX cho phép bạn thực hiện cả hai điều đó: INDEX(array, row_num, [column_num]). Và tôi xin chúc mừng những bạn đã đoán đúng 🙂

Để bắt đầu, hãy lập công thức tổng quát để xứ lý việc tìm kiếm ma trận này. Chúng ta chỉ cần dùng công thức INDEX/MATCH mà bạn đã biết rồi bổ sung thêm một hàm MATCH vào nó, việc này sẽ trả về số thứ tự của cột.

=INDEX (lookup table, MATCH (vertical lookup value, column to lookup against, 0), MATCH (horizontal lookup value, row to lookup against, 0))

Xin hãy chú ý rằng bạn phải định rõ toàn bộ bảng trong câu lệnh mảng của hàm INDEX trong trường hợp tìm kiếm theo hai cách.

Và bây giờ, hãy áp dụng mẫu công thức này. Dưới đây, bạn thấy danh sách các thành phố đông dân nhất trên thế giới. Giả sử, bạn muốn biết số dân của Mỹ năm 2015:

Được rồi, hãy bắt đầu từ công thức nào. Bất cứ khi nào tôi cần tạo ra một công thức Excel phức tạp có gộp một hai vài hàm khác, thì tôi luôn viết từng hàm ra trước.

Vì vậy, bạn bắt đầu viết hai hàm MATCH – các hàm này sẽ trả về số thứ tự hàng và cột cho hàm INDEX của bạn.

Vertical match – bạn dò cột B, chính xác là từ ô B2 đến ô B11, để kiếm giá trị ở ô H2 (“Mỹ”), và hàm MATCH tương ứng có dạng như sau: =MATCH($H$2,$B$1:$B$11,0). Công thức MATCH này trả về giá trị 4 vì “Mỹ” là giá trị thứ tư ở cột B (bao gồm cột tiêu đề).

Horizontal match – bạn tìm giá trị ở cột H3 (“2015”) từ hàng 1, cụ thể là từ ô A1 đến ô E1: =MATCH($H$3,$A$1:$E$1,0). Công thức MATCH này trả về số “5” bởi vì “2015” nằm ở cột thứ năm.

Xem thêm: Khóa học excel nâng cao Hà Nội

Bây giờ, hãy đặt các công thức trên vào hàm INDEX, và chúng ta có:

=INDEX($A$1:$E$11, MATCH($H$2,$B$1:$B$11,0), MATCH($H$3,$A$1:$E$1,0))

Nếu bạn thay thế các hàm MATCH bằng các số được trả về, thì công thức sẽ dễ hiểu hơn rất nhiều: =INDEX($A$1:$E$11, 4, 5, 0))

Có nghĩa là, nó trả về giá trị ở giao điểm của hàng thứ tư và cột thứ năm trong dải ô A1:E11, giá trị đó nằm ở ô E4. Dễ, đúng không nào? Đúng rồi! 🙂

SỬ DỤNG HÀM INDEX MATCH TÌM KIẾM KÈM THEO NHIỀU ĐIỀU KIỆN

Trong bài hướng dẫn sử dụng hàm VLOOKUP trong Excel, tôi đã diễn giải một ví dụ công thức tìm kiếm kèm theo nhiều điều kiện. Tuy nhiên, hạn chế đáng kể của cách đó chính là cần phải thêm một cột trợ giúp. Tin tốt là hàm INDEX MATCH trong Excel cũng có thể tìm kiếm giá trị ở hai cột, mà không cần cột trợ giúp nào cả!

Giả sử, bạn có một danh sách đơn hàng và bạn muốn tính tổng dựa trên hai tiêu chí “Tên khách hàng” và “Sản phẩm”. Một tác nhân phức tạp đó là một khách hàng có thể mua nhiều sản phẩm và tên khách hàng được liệt kê một cách ngẫu nhiên trong bảng cần tìm:

Công thức mảng INDEX MATCH sau đây sẽ giúp ích rất nhiều:

=INDEX(‘Lookup table’!$A$2:$C$13, MATCH(1, (A2=’Lookup table’!$A$2:$A$13) * (B2=’Lookup table’!$B$2:$B$13) ,0), 3)

Công thức này thì phức tạp hơn nhiều so với bất kỳ công thức nào – các công thức chúng ta đã thảo luận ngày hôm nay, nhưng hãy tận dụng kiến thức về hàm INDEX MATCH trong Excel mà bạn có, tôi tin rằng bạn có thể xử lý nó.

Phần khó nhất đó là hàm MATCH, vì thế hãy phân tích nó trước:

MATCH(1, (A2=’Lookup table’!$A$2:$A$13),0) * (B2=’Lookup table’!$B$2:$B$13), 0)

Những gì chúng ta có đó là 3 câu lệnh dưới đây:

Lookup_value: 1

Lookup_array: (A2=’Lookup table’!$A$2:$A$13),0) * (B2=’Lookup table’!$B$2:$B$13)

Match_type: 0

Thông số thứ nhất và thứ ba thì rõ ràng rồi – hàm tìm kiếm “1”, rồi trả về giá trị tìm được đầu tiên.

Bây giờ, câu hỏi chính là – tại sao chúng ta lại tìm “1”? Để có được câu trả lời, hãy xem xét kỹ hơn mảng cần tìm của chúng ta.

Việc chúng ta làm ở đây đó là tìm kiếm giá trị đầu tiên (A2) trong cột “Tên khách hàng” trong bảng chính và tìm tên đó trong tất cả tên khách hàng ở bảng cần tìm (A2:A13). Nếu tìm thấy sự trùng khớp, thì phương trình trả về TRUE, nếu không thì sẽ trả về FALSE. Rồi chúng ta làm tương tự với các giá trị ở cột B (“Sản phẩm”).

Để hiểu rõ hơn những gì tôi đang nói, bạn có thể chọn mảng trong thanh công thức, rồi nhấn phím F9 để xem mỗi phần được chọn được đánh giá như thế nào:

Có lẽ bạn đã biết, trong công thức Excel, giá trị lô gic TRUE tương đương với 1, và FALSE tương đương với 0. Và vì dấu hoa thị (*) đóng vai trò làm toán tử AND trong công thức mảng, nên chúng ta nhận được “1” chỉ khi sự phù hợp được tìm thấy ở cả hai cột, nếu không thì chúng ta nhận được “0”. Vì thế, kết quả của thao tác này là một mảng gồm 1 và 0, trong đó “1” là giá trị đáp ứng cả hai điều kiện xác định. Nếu bảng cần tìm của bạn không có bất kỳ hàng nào trùng lặp nhau, thì sẽ chỉ có một giá trị “1” trong mảng. Và vì “1” là giá trị cần tìm của chúng ta, nên hàm MATCH trả về vị trí tương đối của hàng đó – việc này được diễn giải trong ảnh chụp màn hình dưới đây:

Đồng thời, xin hãy chú ý rằng bạn phải sử dụng thông số tùy chọn thứ ba (colum_num) của hàm INDEX. Đây là vì bạn đã định rõ toàn bộ bảng ở thông số thứ nhất (array), và bạn cần phải để hàm biết bạn muốn lấy giá trị của cột nào. trong trường hợp của chúng ta, đó là cột C (“Tổng”), nên chúng ta nhập 3 trong câu lệnh thứ ba của hàm INDEX.

Và cuối cùng, vì chúng ta cần kiểm tra mỗi ô của mảng, nên công thức INDEX MATCH nên là công thức mảng. Dấu hiệu nhận biết rõ ràng nhất của công thức mảng đó là {dấu ngoặc nhọn} trong đó nó được đóng khung bởi thanh công thức. Hãy nhớ nhấn Ctrl+Shift+Enter để hoàn thiện công thức sau khi bạn đã nhập xong.

Nếu mọi thứ đều được thực hiện chính xác, thì bạn sẽ có kết quả tương tự như những gì bạn nhìn thấy dưới đây:

Xem thêm: Học lập trình VBA trong Excel ở đâu

SỬ DỤNG HÀM INDEX/MACTH VỚI HÀM IFERROR TRONG EXCEL

Có lẽ bạn đã nhận ra (hơn một lần 🙂 nếu bạn nhập một giá trị vô hiệu, cụ thể là giá trị không tồn tại trong mảng cần tìm, thì hàm INDEX/MATCH sẽ trả về lỗi #N/A hay lỗi#VALUE. Nếu bạn thay thế nó bằng một cái gì đó có nghĩa, thì bạn có thể lồng hàm INDEX/MATCH trong hàm IFERROR.

Cú pháp của hàm IFERROR rất đơn giản:

IFERROR(value, value_if_error)

Trong đó câu lệnh value chính là giá trị được kiểm tra lỗi (kết quả của công thức INDEX MATCH trong trường hợp của chúng ta); và value_if_error chính là giá trị cần trả về nếu công thức có lỗi.

Ví dụ, bạn có thể lồng công thức trong ví dụ trước vào hàm IFERROR theo cách này:

=IFERROR(INDEX($A$1:$E$11, MATCH($G$2,$B$1:$B$11,0), MATCH($G$3,$A$1:$E$1,0)),

“Không tìm thấy. Xin hãy thử lại!”)

Và bây giờ, nếu ai đó nhập một mục vô hiệu, thì công thức sẽ cho kết quả như trong ảnh chụp màn hình dưới đây:

Nếu bạn muốn có một ô trống khi lỗi được trả về, thì bạn có thể sử dụng dấu ngoặc kép (“”) trong thông số thứ hai của hàm IFERROR, giống như thế này:

Tôi hy vọng ít nhất có một công thức trong bài hướng dẫn này sẽ hữu ích đối với bạn. Nếu bạn gặp phải nhiệm vụ tìm kiếm khác mà bạn không tìm thấy giải pháp cho nhiệm vụ đó ở đây, thì hãy cứ bình luận ở đây rồi chúng ta sẽ cùng nhau tìm hiểu nó.

– – – – –

Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel:

Một số hàm cơ bản thường gặp như:

• SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện
• COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
• Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…
• Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ hay sử dụng như:

• Định dạng theo điều kiện với Conditional formatting
• Thiết lập điều kiện nhập dữ liệu với Data Validation
• Cách đặt Name và sử dụng Name trong công thức
• Lập báo cáo với Pivot Table…

Rất nhiều kiến thức phải không nào? Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: Học Excel Online