Index Match Trong Excel / 2023 / Top 14 # Xem Nhiều Nhất & Mới Nhất 12/2022 # Top View | Hoisinhvienqnam.edu.vn

Hàm Index Và Hàm Match Trong Excel / 2023

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…

Vì Sao Dùng Index Và Match Tốt Hơn Dùng Vlookup Trong Excel / 2023

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

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 **INDEX và MATCH lại tốt hơn VLOOKUP ở nhiều trường hợp.

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

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:

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à

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

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

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

* 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. Hãy tham gia ngay tại địa chỉ: Học Excel Online

Hàm Index Cú Pháp, Ví Dụ, Bài Tập (Index Kết Hợp Match = Vlookup) / 2023

Hàm INDEX kết hợp hàm match có hay hơn VLOOKUP. Cú pháp, ví dụ minh họa, bài tập và cách vận dụng hàm linh hoạt trong excel,…

Cam kết 100% các bạn đọc kỹ bài viết này sẽ hiểu và vận dùng hàm này một cách dễ dàng.

Now, let’s go!

1. ĐỊNH NGHĨA VÀ CÚ PHÁP HÀM INDEX

1.1. HÀM INDEX LÀ GÌ?

– Điểm tham chiếu (Tính từ địa chỉ nào)

– Số lượng dòng

– Số lượng cột

Bạn có thể coi hàm này là Hàm tìm kiếm trong excel.

Bạn có thể dùng để tìm ngược hay tìm xuôi một cách dễ dàng. Hơn hẳn hàm VLOOKUP chỉ tìm được theo chiều từ trái qua phải.

Hàm INDEX có 2 dạng:

Một là Kết quả của hàm INDEX sẽ trả về giá trị của một ô (Khi bạn đã cho các giá trị tham chiếu kèm theo)

Hai là trả về một dải ô tham chiếu, được quy định bởi [area_num]

1.2. CÚ PHÁP HÀM INDEX

= INDEX(Array, Row_Num, [Column_num])

= INDEX (Reference, Row_Num, [Column_num], [Area_num])

Khi bạn tìm một giá trị theo 1 điều kiện thì chọn phương án 1

Thường thì sử dụng công thức thứ 2, bạn sử dụng hàm mảng hoặc kết hợp với các hàm như Sum, sumif…

TRONG ĐÓ:

– Array: Mảng hoặc có thể hiểu là vùng dữ liệu

– Row_num: Số lượng dòng tính từ dòng đầu tiên của Array đến dòng chứa giá trị cần tìm kiếm

– Column_num: Số lượng cột tính từ cột đầu tiên của Array đến cột chứa trị cần tìm kiếm

– Area_num: Chọn 1 vùng dữ liệu để tham chiếu tại đó trả về sự giao nhau giữa dòng và cột

Row_num và Column_num giao nhau sẽ xác định được ô cần tìm là ô nào đúng không các bạn.

2. VÍ DỤ MINH HỌA CÁCH SỬ DỤNG HÀM INDEX TRONG EXCEL (CƠ BẢN)

Cho bảng dữ liệu như ảnh dưới.

Yêu cầu: tìm doanh số của cửa hàng có vị trí thứ 3 (Cửa hàng C) tính từ cửa hàng đầu tiên trong danh sách

Giả định đã biết trước vị trí cửa hàng C trong bảng thứ tự số 3 từ trên xuống.

=INDEX($B$2:$C$7,3,2)

– $B$2:$C$7 là Array/ Vùng chứa giá trị muốn tìm

– 2 – Số thứ tự cột chứa doanh số tính từ cột đầu tiên của vùng – array (Từ cột B là cột thứ nhất, tính đến cột thứ 2 = cột C

Như vậy thông qua ví dụ đơn giản này, chúng ta có thể kết luận:

Hàm INDEX có chức năng như một hàm tìm kiếm nâng cao trong excel.

Ở phần dưới Trường sẽ có ví dụ sâu hơn và phân tích mạnh yếu để bạn hiểu thật sâu hàm này.

3. VẬN DỤNG HÀM INDEX KẾT HỢP HÀM MATCH TRONG EXCEL (NÂNG CAO)

Một so sánh nhanh về 3 hàm quan trọng: VLOOKUP, MATCH, INDEX, để dễ hình dung trước khi bắt đầu:

Hàm VLOOKUP tìm kiếm trong bảng với điều kiện tìm kiếm tương ứng.

Trong khi đó, hàm INDEX trả về giá trị của một ô nếu có số tham chiếu dòng và cột.

Xem ví dụ bên dưới

Trở lại câu hỏi ở trên: Làm thế nào để biết được cửa hàng C có số thứ tự dòng thứ 3 tính từ dòng đầu tiên của vùng/ Array.

VÍ DỤ CÁCH KẾT HỢP HÀM INDEX VÀ MATCH

Ví dụ sau đây sẽ giải thích rõ cách Hàm INDEX kết hợp hàm MATCH trong excel như thế nào.

Tìm doanh số của cửa hàng D

Tìm tên cửa hàng khi biết trước doanh số

3.1. YÊU CẦU SỐ 1: TÌM DOANH SỐ CỦA CỬA HÀNG D

Bạn có thể dùng hàm VLOOKUP để tìm nếu gặp yêu cầu này đúng không nào.

Cú pháp hàm INDEX kết hợp hàm MATCH để giải quyết yêu cầu này như sau:

=INDEX($B$2:$C$7,MATCH(C9,$B$2:$B$7,0),2)

– $B$2:$C$7: Array/ vùng dữ liệu

– MATCH(C9,$B$2:$B$7,0): Kết quả của hàm này sẽ trả về số thứ tự dòng của cửa hàng D trong Array

– 2: Là cột thứ 2 tính từ cột đầu tiên mà cột đó chứa giá trị tìm kiếm = Doanh số

Kết quả ta nhận được như sau:

Hoặc bạn có thể thay đổi tương ứng doanh thu bạn sẽ thấy kết quả thay đổi theo.

3.2. YÊU CẦU SỐ 2: TÌM TÊN CỬA HÀNG KHI BIẾT TRƯỚC DOANH SỐ

Sử dụng hàm VLOOKUP để tìm ngược từ phải qua trái là một thách thức thực sự với nhiều người học excel.

Nhưng khi dùng hàm INDEX thì quá dễ dàng bạn ạ.

Phương án xử lý trong trường hợp này như sau:

Tìm ra vị trí của Doanh số biết trước trong cột doanh số

Tìm trong cột tên cửa hàng: cửa hàng đang có doanh số tương ứng

=INDEX($B$2:$B$7,MATCH(C12,$C$2:$C$7,0))

Cụ thể cách sử dụng hàm có trong hình sau:

Cái nào mạnh hơn, hay hơn

Trong excel, để biết được hàm nào mạnh hơn thì phải xem trình độ của người dùng ở mức nào.

Nếu ở mức cơ bản thì hàm VLOOKUP hay hơn vì nó phổ biến hơn. Hàm VLOOKUP là một trong những hàm bạn phải học khi dùng excel, còn INDEX thì không.

Nếu ở mức độ cao hơn thì tùy vào sở thích mỗi người.

Nhưng có một số điểm bạn có thể tham khảo để xem lựa chọn hàm nào nhiều hơn.

VLOOKUP chỉ tìm được khi cột chứa dữ liệu tìm kiếm đứng đầu bảng

VLOOKUP chỉ tìm được từ trái qua phải

INDEX không phân biệt cột chứa dữ liệu là cột nào, chỉ cần tìm ra vị trí

Do đó INDEX đa di năng hơn

Bài toán vẫn cũ, nhưng đổi yêu cầu:

Tính doanh số cột C:

=SUM(C2:C7)

=SUM(INDEX(A2:C7,,3))

Tính doanh số 3 cửa hàng trên cùng

=SUM(C2:C4)

=SUM(C2:INDEX(A2:C7,3,3))

=SUM(INDEX(A2:C7,1,3):INDEX(A2:C7,3,3))

6. INDEX KẾT HỢP CÙNG HÀM MIN/ MAX

Bài toán vẫn cũ, nhưng đổi yêu cầu khi kết hợp MIN/ MAX

Tìm cửa hàng doanh số thấp nhất:

=INDEX(B2:B7,MATCH(MIN(C2:C7),C2:C7,0))

Tìm cửa hàng doanh số cao nhất:

=INDEX(B2:B7,MATCH(MAX(C2:C7),C2:C7,0))

7. INDEX KẾT HỢP CÙNG NHÓM HÀM AVERAGE

Bài toán vẫn cũ, nhưng đổi yêu cầu:

Tính trung bình cộng doanh số 4 cửa hàng từ số 2 đến số 5

=AVERAGE(INDEX(C2:C7,2):INDEX(C2:C7,5))

Tính trung bình cộng doanh số 3 cửa hàng đầu:

=AVERAGE(C2:INDEX(C2:C7,3))

8. DOWNLOAD FILE MẪU VÀ BÀI TẬP THỰC HÀNH

Các bạn muốn có được file mẫu cho các ví dụ phía trên.

Muốn thực hành để thành thạo hàm này hơn nữa.

Lưu ý:

Dấu [] xuất hiện ngoài [Column_num] và [Area_num] sẽ tương đương với việc bạn không cần có tham số đó trong cú pháp hàm. Khi không nhập tham số đó, excel sẽ tự động lấy giá trị là 1 khi tính toán.

Cách áp dụng thứ 2, đặc biệt là Area_num rất khó hiểu đúng không bạn. Trường cũng hiếm khi dùng nó lắm, nên bạn có thể bỏ qua.

Cách thứ nhất, Trường cũng hay dùng chính xác cho từng cột thay vì phải nhập cả Column_num

Truongpx – Admin Hệ thống các website chuyên về kỹ năng làm việc.

Cách Sử Dụng Hàm Match Và Index Trong Excel Và Kèm Ví Dụ / 2023

Nếu như bạn đang băn khoăn chưa biết về công thức cũng như cách sử dụng thì trong bài viết này chúng tôi sẽ chia sẻ về chi tiết cách sử dụng hai hàm là MATCH và INDEX và kèm ví dụ minh họa cụ thể trong phần nội dung dưới.

Cách sử dụng hàm MATCH và INDEX trong Excel

Sử dụng Hàm INDEX

Hàm INDEX có tác dụng trả về một giá trị hay một vùng dữ liệu tùy thuộc vào các tham số đầu vào. Hàm INDEX có hai cách sử dụng đó là dạng mảng (array) và dạng tham chiếu (reference).

1. INDEX dạng mảng

– Cú pháp:

=INDEX(array, row_num, column_num)

Trong đó:

array: Là một dãy ô (VD: A1:E5) hoặc là một hằng mảng (VD: {1;3;”a”;”TXD”})

row_num: số thứ tự dòng trong array để lấy giá trị trả về.

column_num: số thứ tự cột trong array để lấy giá trị trả về.

Lưu ý:

Nếu hai đối số column_num và row_num đều sử dụng, INDEX sẽ trả về giá trị của ô là giao điểm của column_num và row_num.

Để hàm INDEX trả về kết quả dưới dạng mảng, các bạn cần điền công thức ở dạng mảng.

2. INDEX dạng tham chiếu

– Cú pháp:

=INDEX(reference, row_num, column_num, area_num)

Trong đó

reference: chứa các vùng tham chiếu. Nếu các vùng tham chiếu này rời rạc thì phải được để trong dấu ngoặc (). Ví dụ về reference: A1:F4; (A2:B3,T6:$X$5), ….

row_num: Là số thứ tự dòng trong tham chiếu.

column_num: Là số thứ tự cột trong tham chiếu.

area_num: số thứ tự vùng tham chiếu (được đánh số bắt đầu là 1, 2, 3, …) để trả về giao điểm của column_num và row_num trong dãy đó. Nếu bỏ qua area_num, INDEX sẽ mặc định dùng vùng 1.

Ví dụ, nếu reference = (A2:B4, C1:F4, G1:J5), thì vùng 1 là dãy A2:B4, vùng 2 là dãy C1:F4, và vùng 3 là dãy G1:J5.

Lưu ý:

Đối với tham chiếu chỉ gồm một dòng hoặc một cột, các bạn có thể sử dụng cú pháp:

INDEX(reference,,column_num); INDEX(reference,row_num,)

Để hàm INDEX trả về kết quả dưới dạng mảng, các bạn cần điền công thức ở dạng mảng.

– Hàm MATCH trả về vị trí tương đối của một giá trị trong một dãy giá trị.

– Cú pháp:

=MATCH(lookup_value, lookup_array, match_type)

Trong đó

lookup_value: Giá trị dùng để tìm kiếm trong hàng đầu tiên của table_array. lookup_value có thể là một số, một chuỗi hoặc một tham chiếu. VD: “một”; 1; C4; …

lookup_array: vùng tìm kiếm

match_type: kiểu tìm kiếm.

Với match_type có thể là một trong các giá trị 1, 0 hoặc -1:

-1: MATCH sẽ tìm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value. Với kiểu tìm kiếm này, lookup_array phải được xếp theo thứ tự giảm dần từ lớn đến nhỏ.

0: MATCH sẽ tìm chính xác giá trị lookup_value. Với kiểu tìm kiếm này, MATCH không phân biệt chữ HOA hay chữ thường.

1: (mặc định) MATCH sẽ tìm giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value. Với kiểu tìm kiếm này, lookup_array phải được xếp theo thứ tự tăng dần từ nhỏ đến lớn.

Lưu ý:

Nếu match_type = 0 và lookup_value kiểu text, bạn có thể dùng các ký tự đại diện cho lookup_value. Dấu * đại diện cho nhiều ký tự và dấu ? đại diện cho một ký tự.

Trong ví dụ sau đây, chúng ta có danh sách các học sinh với điểm thi tương ứng từng môn thi. Mỗi học sinh này đều thuộc một lớp chuyên nào đó và chúng ta sẽ sử dụng hàm INDEX để xác định điểm môn chuyên tương ứng với từng học sinh.

– Ô chứa điểm môn chuyên trong các môn thi của từng học sinh tương ứng với vị trí của môn chuyên trong danh sách các môn chuyên ở vùng D5:L5. Do đó, chúng ta sử dụng hàm MATCH để lấy về cột sẽ chứa điểm môn chuyên cho mỗi học sinh. Ví dụ, với học sinh Vũ Thanh Khiết, chúng ta sử dụng công thứcMATCH(C10;$D$5:$L$5;0).

Với mỗi học sinh:Lên đầu trang ↑

So sánh hàm INDEX + MATCH với VLOOKUP và VLOOKUP

– Hàm HLOOKUP và VLOOKUP chỉ sử dụng được nếu cột (dòng) chứa giá trị tìm kiếm nằm ở vị trí trước so với cột (dòng) chứa giá trị cần trả về. Nếu không, chúng ta sẽ cần phải sử dụng kết hợp với công thức mảng để lấy được giá trị. Tuy nhiên, như các bạn thấy với cặp MATCH + INDEX, chúng ta có thể lấy được giá trị cần tìm mà không cần quan tâm tới vị trí của vùng tìm kiếm và vùng kết quả như hai hàm VLOOKUP và HLOOKUP.

Hàm nhân trong Excel

Hàm tính điểm trung bình trong Excel

Sử dụng hàm Indirect trong Excel

Tải các tập tin minh hoạ

Lên đầu trang ↑

Lời kết