Top 9 # Xem Nhiều Nhất Match Trong Excel Là Gì Mới Nhất 6/2023 # Top Like

Chúng ta thường nghe nói tới rất nhiều các hàm trong Excel, trong đó có hàm Match. Vậy loại hàm này có tác dụng  và chúng được sử dụng như thế nào trong bảng tính Excel. Bài viết ngày hôm nay chúng ta sẽ cùng nhau đi tìm hiểu về hàm Match là gì, cách sử dụng chúng sao cho hiệu quả và chính xác nhất.

Hàm Match là gì?

Công nghệ thông tin là một trong những thứ hỗ trợ cho con người rất tốt để làm việc, học tập tốt hơn, nhanh hơn. Đặc biệt là sự có mặt của các hàm trong Excel laị càng đạt được tính hữu dụng cao hơn. Khi muốn tính tổng chúng ta đã có hàm SUM, SUMIF muốn đếm chúng ta đã có hàm Count,…Và bây giờ, khi bạn muốn tìm kiếm giá trị đã xác định nào sẵn trong trang tính xem chúng ở vị trí nào thì chỉ cần dùng hàm Match. Vậy bạn đã biết hàm Match là gì và nó được sử dụng với công thức nào chưa?

Khi bạn muốn tìm kiếm một giá trị nào đó đã được xác định trước trong một phạm vi và bạn muốn trả về đúng vị trí trong phạm vi đó ở vị trí tương đối thì bạn sẽ sử dụng hàm Match. Hàm Match cũng giống như một số hàm cơ bản khác trong Excel như là hàm tính, hàm tính giá trị trung bình, hàm đếm,…nó được sử dụng rất phổ biến trong quá trình bạn làm việc hay học tập.

Hiểu một cách đơn giản, ví dụ như trong một bảng tính của Excel, bạn lưu trữ tên của rất nhiều học sinh, bạn muốn tìm dữ liệu của học sinh tên Hoàng nhưng lại không muốn mất thời gian ngồi và dùng mắt để tìm một cách thủ công, nó vừa khiến bạn mỏi mắt, tốn thời gian lại không đảm bảo sự chính xác cao. Thay vào đó, bạn dùng hàm Match để tìm kiếm sẽ tốt hơn, khi sử dụng đúng hàm thì kết quả trả về sẽ cho bạn biết học sinh tên Hoàng đó được lưu trữ ở vị trí nào, thứ tự nào trên bảng tính.

Rất nhiều người nhầm rằng hàm Match sẽ chỉ ra giá trị của chính nó, nhưng thực chất hàm Match chỉ chỉ ra được vị trí tương đối của giá trị mà bạn đang đi tìm kiếm. Cũng giống như hàm Sum thì hàm Match không phân biệt khi bạn viết chữ in hoa hay in thường. Nếu như trong công thức của bạn có một giá trị tìm kiếm nhưng trong khu vực mà bạn tìm kiếm có nhiều kết quả cùng trả về thì hệ thống sẽ nhận kết quả đầu tiên. Nếu như trong khu vực tìm kiếm đó không có kết quả của giá trị bạn tìm thì hệ thống sẽ trả lại cho bạn kết quả là #N/A.

Cách sử sụng hàm Match

Chúng ta biết được chức năng của hàm Match là giúp ban tìm được vị trí chính xác của một giá trị nào đó trong bảng tính. Ứng dụng đó rất hữu ích đối với những người sử dụng Excel, thế nhưng bạn cần nắm được chuẩn xác công thức thì mới có thể sử dụng đúng được.

Cú pháp của hàm như sau: MATCH(Lookup_value,Lookup_array,[Match_type])

Với cú pháp này bạn cần hiểu:

– Lookup_value: Đây là giá trị mà bạn đang tìm kiếm, ví dụ như bạn muốn tìm kiếm tên của ai đó, con số nào đó, văn bản, một giá trị logic,…Bắt buộc bạn phải có giá trị tìm kiếm này.

– Lookup_array: đây là khu vực mà bạn sẽ tìm kiếm.

– Match_type: Là kiểu tìm kiếm, phần này thì không bắt buộc tùy từng yêu cầu tìm kiếm của bạn.

Ở Excel sẽ có 3 kiểu tìm kiếm phổ biến đó là: Nhỏ hơn giá trị tìm kiếm ( 1:Less than), chính xác với giá trị bạn tìm kiếm ( 0:Exach Match), kiểu còn lại là lớn hơn giá trị tìm kiếm (-1:Greater than). Và đây cũng chính là phần có hoặc không có ở trong công thức tính của hàm Match, nếu như bạn không nhập phần này thì mặc định hàm Match sẽ tìm kết quả nhỏ hơn.

Với những người đã sử dụng Excel thành thục, có sự hiểu về bản chất của các hàm thì người ta còn kết hợp hàm Match với hàm Index để thay thế cho việc sử dụng hàm Vlookup hay Hlookup. Đây cũng là những hàm tìm kiếm được sử dụng rất phổ biến, nó cho phép lấy cả giá trị ở những hàng hay cột trong các bảnh khác nhau và trả về kết quả với độ chính xác khá cao.

Việc chúng ta dò tìm, tra cứu vị trí dữ liệu thường xuyên diễn ra với hầu hết tất cả mọi người. Đặc biệt là với những ai làm trong lĩnh vực công nghệ thông tin, những người học tin học hay những ai đang làm công việc quản lý thông tin, hành chính, kế toán ở một công ty, doanh nghiệp nào đó. Chính vì thế mà sử dụng các hàm tìm kiếm như Match, Vlookup, Hlookup là điều vô cùng cần thiết, mang đến sự tiện lơi, chính xác cao. Thay vì ngồi dò kiếm từng hàng, từng cột, từng ô trong bảng tính, với các hàm có sẵn bạn chỉ cần vài thao tác đơn giản thì phạm vi tìm kiếm được thu nhỏ rất nhiều.

Chúng ta không thể nào quản lý một hệ thống dữ liệu rộng lớn chỉ với cách làm thủ công đó là dùng ghi chép bằng tay, lưu nhớ bằng đầu, tìm kiếm bằng mắt, tính toán bởi chính con người nữa. Chính vì thế sự xuất hiện của Excel, phát triển của các hàm tính toán giúp đỡ con người rất nhiều, mọi thứ trở nên thật đơn giản, dễ dàng.

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:

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

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.

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:

=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:

Đợ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.

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.

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.

=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))

Đầ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)

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

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ô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ì 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.

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! 🙂

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:

=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)

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)

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:

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.

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!”)

– – – – –

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…

Hôm nay ad sẽ giới thiệu cho các bạn về hàm match và một số ứng dụng của hàm match trong thực tế công việc và học tập để thi qua môn tin học :). Nếu thấy hay thì đừng tiếc một like hoặc nhấn share để lưu về wall và chia sẻ với bạn bè cùng biết.

Các nội dung chính:

1. Định nghĩa về hàm

2. Cú pháp và cách sử dụng

3. Bài tập ví dụ và bài giải: Vlookup + Match

1. Hàm match trong excel là gì?

Hàm match là hàm excel dùng để tìm số thứ tự của 1 giá trị cho trước trong một danh sách các giá trị.

Lấy ví dụ ta có 1 danh sách học sinh như sau:

Nhưng nếu không có cột số thứ tự thì làm thế nào để biết bây giờ?

– Phải đếm từ học sinh đầu tiên đến bạn VĨNH. Nhưng nếu bạn vĩnh nằm ở phía cuối của danh sách hàng nghìn học sinh thì phải làm sao?

– Khi đó bạn phải dùng hàm Match để xử lý vấn đề rồi.

Vậy cách dùng hàm match như thế nào để tìm được số thứ tự của bạn VĨNH trong nháy mắt, mời bạn theo dõi phần 2 của bài viết.

2. Cú pháp và cách sử dụng hàm match trong excel

Cú pháp hàm match trong excel:

Cú pháp bằng tiếng anh = Match (Lookup_Value, Lookup_Range, Match_type)

Cú pháp bằng tiếng việt = Match (Giá trị cho trước, Vùng tìm kiếm, kiểu tìm kiếm)

Áp dụng vào ví dụ trên, ad sẽ viết lại làm như sau:

= Match (“VĨNH”, B5:B11,0)

Trong đó:

VĨNH: là giá trị cần tìm kiếm số thứ tự

B5:B11: là danh sách học sinh trong đó có bạn VĨNH

0: Kiểu tìm kiếm chính xác 100%

Bạn có thể download được file excel mẫu ở phần 3 của bài viết này.

3. Bài tập hàm match kết hợp vlookup, index trong excel

Link download bài tập excel mẫu – Hàm match kết hợp với hàm vlookup và index trong excel.

Ví dụ về hàm Match ad giới thiệu tới các bạn trong phần 2 là cách sử dụng đơn giản nhất. Hàm match phát huy tác dụng tuyệt vời của mình khi được kết hợp với các hàm excel khác đặc biệt là hàm index và vlookup/ hlookup.

Trong mẫu bài tập bạn download được ở trên, ta có 2 cách để tìm được tên xe từ bảng danh sách tên xe

C1: Dùng hàm match kết hợp hàm vlookup

C2: Dùng hàm match kết hợp hàm hlookup

C3: Dùng hàm match kết hợp hàm index

Dựa vào Bảng tên loại xe, ta có thể tìm được tên xe theo 2 điều kiện: Tên hãng và Phân khối

Tên hãng trong Bảng tên loại xe nằm ở cột nên ta dùng hàm vlookup để tìm tên xe

Khi ta biết được xe đó có số phân khối bao nhiêu và hãng gì thì ta có thể biết được chính xác tên xe là gì.

Cú pháp hàm vlookup như sau:

= Vlookup (Tên hãng, Bảng tên loại xe, Số thứ tự phân khối của xe đang tìm, kiểu tìm kiếm)

=VLOOKUP(B3, $B$16:$E$19, MATCH(C3,$B$16:$E$16,0), 0)

Giải thích hàm:

B3 = Suzuki

B16:E19 là vùng dữ liệu của Bảng tên loại xe

MATCH(C3,$B$16:$E$16,0) dùng để tìm kiếm số thứ tự phân khối xe S11 tính từ cột đầu tiên của bảng tên loại xe. Trong ví dụ này, 110 phân khối có số thứ tự là 3 tính từ cột đầu tiên trong bảng tên loại xe

0 là kiểu tìm kiếm chính xác 100%

Từ các phân tích trên ta viết lại hàm vlookup như sau:

Hàm Match trong là hàm tìm kiếm một giá trị xác định trước trong một phạm vi ô rồi trả về đúng vị trí tương đối của giá trị trong phạm vi đó.

Hàm Match là hàm phổ biến trong các hàm Excel , được dùng khá nhiều khi xử lý các bảng dữ liệu Excel và tính toán. Trong 1 bảng dữ liệu, khi bạn muốn tìm kiếm một giá trị xác định nào đó trong 1 mảng, hay phạm vi ô, hàm Match sẽ trả về đúng với vị trí của giá trị đó trong mảng hay trong phạm vị của bảng dữ liệu.

Cú pháp hàm Match trong Excel

Cú pháp hàm Match trong Excel là: =Match(Lookup_value,Lookup_array,[Match_type]).

Trong đó:

Lookup_value: giá trị tìm kiếm trong mảng Lookup_array. Giá trị này có thể là số, văn bản, giá trị logic hoặc một tham chiếu ô đến một số, văn bản hay giá trị logic, bắt buộc phải có.

Lookup_array: mảng hay phạm vị ô được tìm kiếm, bắt buộc có.

Match_type: kiểu tìm kiếm, không nhất thiết phải có.

Có 3 kiểu tìm kiếm trong hàm Match trên Excel:

1 hoặc bỏ qua (Less than): hàm Match tìm kiếm giá trị lớn nhất mà giá trị đó nhỏ hơn hoặc bằng với lookup_value. Nếu người dùng chọn kiểu tìm kiếm này thì lookup_array phải được sắp xếp theo thứ tự tăng dần.

0 (Exact Match): hàm Match sẽ tìm kiếm giá trị thứ nhất bằng chính xác với lookup_value. Các giá trị trong lookup_array có thể được sắp xếp theo bất kỳ giá trị nào.

-1 (Greater than): hàm Match tìm kiếm giá trị nhỏ nhất mà giá trị đó lớn hoặc bằng với lookup_value. Giá trị trong lookup_array phải được sắp xếp theo thứ tự giảm dần.

Lưu ý khi dùng hàm Match:

Hàm Match sẽ trả về vị trí của giá trị tìm kiếm trong lookup_array, không trả về chính giá trị tìm kiếm.

Có thể dùng chữ hoa hay chữ thường trong khi tìm kiếm giá trị dạng text.

Khi không tìm được giá trị tìm kiếm trong lookup_array, hàm Match sẽ báo lỗi giá trị tìm kiếm.

Trong trường hợp Match_type là 0 và giá trị tìm kiếm lookup_value dạng text thì giá trị tìm kiếm có thể chứa các ký tự dấu * (cho chuỗi ký tự) và dấu hỏi chấm (cho ký tự đơn). Nếu muốn tìm dấu hỏi chấm hay dấu sao thì gõ dấu ngã trước ký tự đó.

Nếu không nhập gì thì hàm Match mặc định đó là 1.

Ví dụ về hàm Match

Trường hợp 1: Kiểu tìm kiếm là 1 hoặc bỏ qua

Tìm kiếm vị trí số 61 trong cột Tổng số ở bảng dữ liệu, nghĩa là tìm kiếm giá trị nhỏ hơn giá trị tìm kiếm. Chúng ta nhập công thức là =MATCH(64,C2:C6,1).

Vì giá trị 64 không có trong cột Tổng số nên hàm sẽ trả về vị trí của giá trị nhỏ gần nhất mà giá trị nhỏ hơn 64 là 63. Kết quả sẽ trả về giá trị ở vị trí thứ 2 trong cột.

Trường hợp 2: Kiểu tìm kiếm là 0

Tìm kiếm vị trí của giá trị 70 trong bảng dữ liệu. Chúng ta sẽ có công thức nhập là =MATCH(70,C2:C6,0) rồi nhấn Enter.

Kết quả trả về sẽ là vị trí của giá trị 70 trong cột Tổng số là vị trí thứ 4.

Công thức tìm kiếm thứ tự là =MATCH(D2,$D$6:$D$8,0) rồi nhấn Enter.

Ngay sau đó kết quả trả về sẽ là thứ tự chính xác của học sinh theo từng lớp, sắp xếp theo quy luật cho trước.