Mới: Bảng phím tắt Excel theo nhóm thao tác, in ra dán cạnh màn hình là dùng được.

Xem ngay

INDEX MATCH trả về nhiều giá trị: 3 cách làm trong Excel

INDEX MATCH trả về nhiều giá trị trong Excel? Hướng dẫn 3 cách làm: cột phụ, công thức mảng và FILTER, kèm ví dụ thực hành dễ làm theo ngay trên máy.

Bởi Ban biên tập Asean Edu

/

11 phút đọc

/ /

Bạn đã từng dùng INDEX MATCH để tra cứu nhưng chỉ thấy đúng một dòng đầu tiên, còn các dòng sau thì biến mất? Đây là tình huống đội ngũ bên mình gặp rất thường xuyên khi hướng dẫn học viên. Hàm INDEX MATCH vốn được thiết kế để lấy một giá trị duy nhất. Muốn lấy nhiều kết quả, bạn cần thêm một bước phụ trợ. Trong bài này, chúng tôi đi qua ba cách làm phổ biến, kèm ví dụ để bạn thực hành ngay trên máy của mình.

Vì sao INDEX MATCH chỉ cho ra một kết quả?

Để hiểu vấn đề, bạn cần nhìn vào cách MATCH hoạt động. Hàm này quét vùng tìm kiếm từ trên xuống dưới. Ngay khi gặp lần khớp đầu tiên, nó dừng lại và trả về số thứ tự của dòng đó.

INDEX sau đó lấy giá trị nằm tại vị trí ấy. Vì vậy, dù bảng có năm dòng cùng một nhà cung cấp, công thức vẫn chỉ đưa ra dòng đầu tiên. Đây không phải lỗi mà là thiết kế của nó. Khi hiểu điều này, bạn sẽ bớt bực bội và biết mình cần làm thêm gì.

Muốn lấy các dòng còn lại, bạn phải cho Excel biết cần lấy lần xuất hiện thứ mấy. Có hai hướng xử lý: đánh số thứ tự cho từng lần xuất hiện bằng cột phụ, hoặc dùng công thức mảng để đọc toàn bộ danh sách trong một lần. Hai cách này khác nhau về độ khó và độ linh hoạt. Bên dưới, chúng tôi phân tích từng cách.

Nắm nguyên lý INDEX và MATCH trước khi mở rộng

Trước khi ghép nhiều giá trị, bạn nên hiểu rõ từng hàm làm gì. Nói ngắn gọn, MATCH trả lời câu hỏi giá trị này nằm ở dòng thứ mấy. INDEX trả lời rằng nếu biết dòng thứ mấy thì lấy giá trị nào. Ghép hai việc này lại, bạn có một cặp tra cứu rất mạnh.

MATCH tìm vị trí dòng như thế nào

MATCH nhận ba tham số: giá trị cần tìm, vùng tìm kiếm và kiểu khớp. Với kiểu khớp chính xác (số 0), MATCH quét tuần tự và trả về vị trí của lần khớp đầu tiên. Nếu không thấy, nó báo lỗi #N/A. Điều cần nhớ là MATCH không quan tâm đến những dòng khớp phía sau.

INDEX lấy giá trị theo vị trí

INDEX lấy một ô trong vùng dữ liệu dựa trên số dòng bạn cung cấp. Khi kết hợp với MATCH, con số dòng ấy chính là kết quả của MATCH. Công thức đầy đủ có dạng INDEX(cột kết quả, MATCH(điều kiện, cột điều kiện, 0)). Nắm được cấu trúc này, bạn sẽ thấy các cách mở rộng bên dưới chỉ là biến thể của đúng một ý tưởng.

Một ví dụ đơn giản

Giả sử cột A chứa tên phòng ban, cột B chứa tên nhân viên. Công thức =INDEX(B2:B10, MATCH(“Kế toán”, A2:A10, 0)) sẽ trả về nhân viên đầu tiên thuộc phòng Kế toán. Những nhân viên còn lại vẫn nằm nguyên trong bảng, chỉ là hàm chưa được yêu cầu lấy.

Lấy nhiều giá trị bằng công thức mảng

Công thức mảng cho phép Excel xử lý cả một danh sách thay vì từng ô riêng lẻ. Lúc đầu cách này khá khó đọc, nhưng khi đã nắm logic, bạn sẽ thấy nó khá gọn gàng.

Kết hợp IF, ROW và SMALL

Ý tưởng cốt lõi như sau. IF đánh dấu những dòng thỏa điều kiện. ROW lấy số thứ tự của các dòng đó. SMALL sắp xếp các số ấy theo thứ tự tăng dần. Cuối cùng, INDEX lấy giá trị tương ứng với từng số. Một công thức điển hình là =IFERROR(INDEX(B$2:B$20, SMALL(IF($A$2:$A$20=”Kế toán”, ROW($A$2:$A$20)-ROW($A$2)+1), ROWS($1:1))), “”), sau đó kéo xuống các ô bên dưới.

Cách nhập công thức tùy phiên bản

Trên Excel 2019 trở về trước, bạn phải nhập công thức bằng tổ hợp Ctrl + Shift + Enter, Excel sẽ tự bao công thức trong dấu ngoặc nhọn. Trên Excel 365 và 2021, công thức chạy bình thường chỉ với phím Enter nhờ cơ chế mảng động. Nếu kết quả không như mong đợi, hãy kiểm tra lại bạn đang dùng cách nhập nào.

Ví dụ: liệt kê mã hàng của một nhà cung cấp

Hãy tưởng tượng bảng có cột nhà cung cấp và cột mã hàng. Đặt công thức trên vào ô đầu tiên của cột kết quả, rồi kéo xuống đủ số dòng. Khi hết dữ liệu khớp, các ô còn lại sẽ hiện trống nhờ IFERROR, nhìn gọn hơn nhiều so với việc để lộ lỗi #N/A khắp nơi.

Tạo cột phụ đánh số để lấy từng kết quả

Nếu công thức mảng khiến bạn rối, cột phụ là lựa chọn dễ hiểu hơn. Bạn đánh dấu từng dòng là lần xuất hiện thứ mấy của điều kiện, rồi tra cứu theo số thứ tự.

Đánh số thứ tự xuất hiện

Ở cột phụ, chẳng hạn cột C, nhập =IF(A2=”Kế toán”, COUNTIF($A$2:A2, “Kế toán”), “”). Mỗi dòng khớp sẽ có số 1, 2, 3 theo thứ tự xuất hiện. Các dòng không khớp để trống, nên nhìn vào là biết ngay chúng nằm ở đâu.

Kết hợp với INDEX MATCH

Ở ô kết quả, dùng công thức =IFERROR(INDEX(B$2:B$20, MATCH(ROWS($1:1), C$2:C$20, 0)), “”). Lúc này MATCH tìm đúng số thứ tự thay vì tên phòng ban, nên INDEX lấy đúng dòng cần thiết. Khi kéo công thức xuống, ROWS($1:1) tự tăng lên 2, 3, 4 và các kết quả lần lượt hiện ra.

Ưu điểm dễ kiểm tra

Điểm mạnh của cách này là bạn nhìn thấy ngay cột phụ đang đánh số gì. Khi kết quả sai, việc dò lỗi nhanh hơn hẳn so với một công thức mảng dài. Người nhận file sau này cũng dễ hiểu và dễ chỉnh sửa hơn.

Dùng hàm FILTER trên Excel 365 và Excel 2021

Nếu phiên bản Excel của bạn hỗ trợ, FILTER là cách ngắn nhất để lấy nhiều kết quả. Chỉ một công thức là đủ, không cần cột phụ hay cách nhập đặc biệt.

Cú pháp cơ bản

Cú pháp là =FILTER(vùng cần lấy, điều kiện). Ví dụ =FILTER(B2:B20, A2:A20=”Kế toán”) sẽ trả về toàn bộ nhân viên thuộc phòng Kế toán, và kết quả tự động tràn xuống các ô bên dưới.

So sánh với INDEX MATCH truyền thống

FILTER gọn và dễ đọc hơn hẳn. Tuy vậy, INDEX MATCH vẫn rất đáng học, vì nó chạy được trên nhiều phiên bản cũ và bạn sẽ gặp nó trong rất nhiều file có sẵn. Hiểu INDEX MATCH cũng giúp bạn đọc được những bảng tính do người khác để lại.

Lưu ý về tương thích

Nếu gửi file có FILTER cho người dùng Excel 2016, họ sẽ thấy lỗi #NAME?. Trước khi chia sẻ, hãy hỏi người nhận xem họ có dùng phiên bản hỗ trợ hay không, hoặc chọn cách làm tương thích hơn.

Những lỗi thường gặp khi lấy nhiều giá trị

Trong thực tế, phần lớn lỗi không nằm ở logic mà ở cách đặt công thức. Dưới đây là ba lỗi đội ngũ gặp nhiều nhất khi hướng dẫn học viên.

  • Lỗi #N/A khi không có kết quả khớp
  • Thiếu dấu $ khiến vùng tìm kiếm trôi khi kéo công thức
  • Công thức mảng nhập sai cách trên phiên bản cũ

Lỗi #N/A

Khi điều kiện không khớp với dòng nào, MATCH trả về #N/A. Đây thực ra không phải lỗi công thức mà là thông báo rằng không tìm thấy gì. Bọc công thức trong IFERROR để ô hiện trống hoặc một thông báo gọn gàng là cách xử lý phổ biến. Tuy nhiên, đừng che lỗi quá sớm; hãy chắc chắn dữ liệu gốc không có khoảng trắng thừa trước khi kết luận.

Thiếu neo tham chiếu

Nếu quên dấu $ khi cố định vùng tìm kiếm, vùng sẽ dịch chuyển theo khi bạn kéo công thức xuống, và kết quả sai dần mà khó nhận ra. Hãy khóa vùng bằng $ trước khi sao chép công thức.

Nhập công thức mảng sai cách

Trên phiên bản cũ, nếu chỉ nhấn Enter mà không dùng Ctrl + Shift + Enter, công thức sẽ chỉ trả về một giá trị hoặc báo lỗi. Đây là nguyên nhân khiến nhiều người lầm tưởng công thức của mình sai ngay từ đầu.

Ví dụ thực tế: tra cứu đơn hàng theo khách hàng

Chúng tôi chọn một tình huống quen thuộc: bạn cần liệt kê tất cả đơn hàng của một khách. Làm theo từng bước dưới đây sẽ giúp bạn thấy rõ cách vận hành.

Chuẩn bị dữ liệu mẫu

Tạo bảng gồm ba cột: Mã đơn, Khách hàng và Số tiền. Nhập khoảng hai mươi dòng, trong đó một khách hàng xuất hiện bốn lần. Dữ liệu có chút đa dạng như vậy sẽ giúp bạn kiểm tra kết quả chính xác hơn, thay vì chỉ thử với một dòng.

Xây dựng công thức

Chọn một trong ba cách đã học. Nếu dùng cột phụ, đặt cột đếm bên cạnh bảng, sau đó tạo cột kết quả với INDEX MATCH theo số thứ tự. Kéo công thức đủ số dòng để chứa toàn bộ đơn hàng của khách đó.

Đối chiếu kết quả

Dùng bộ lọc để lọc đúng khách hàng ấy trên bảng gốc, rồi so sánh số dòng và tổng tiền với cột kết quả. Nếu khớp, công thức đã đúng. Bước đối chiếu này rất đáng làm, vì nó giúp bạn tự tin hơn khi áp dụng cho dữ liệu thật.

Mẹo tối ưu khi bảng dữ liệu lớn

Khi bảng có vài nghìn dòng, công thức mảng có thể khiến file chạy chậm. Một vài thói quen đơn giản sẽ giúp file nhẹ hơn đáng kể.

  • Hạn chế dùng công thức mảng trên vùng quá rộng, chỉ tham chiếu đúng số dòng cần thiết.
  • Đặt tên cho vùng dữ liệu để công thức dễ đọc hơn, ví dụ đặt tên cột khách hàng là KhachHang.
  • Khi dữ liệu lên đến hàng chục nghìn dòng, hãy cân nhắc chuyển sang công cụ chuyên xử lý dữ liệu.

Một thói quen khác cũng hữu ích là tách riêng bảng dữ liệu gốc và các cột tính toán. Khi cần sửa, bạn biết chắc phần nào là dữ liệu thô, phần nào là công thức, và file sẽ ít lỗi hơn nhiều.

Khi nào nên dùng Power Query thay vì công thức

Công thức phù hợp với bảng đơn giản và ít thay đổi. Khi tình huống phức tạp hơn, công cụ khác thường là lựa chọn tốt hơn, và Power Query là một ví dụ điển hình. Nếu bạn muốn tìm hiểu thêm về các giải pháp xử lý và phân tích dữ liệu, có thể tham khảo thêm tại đây. Ngoài ra, bạn nên đọc hướng dẫn hàm VLOOKUP từ cơ bản đến nâng cao để so sánh với cách tra cứu quen thuộc, và giải thích công thức mảng trong Excel nếu phần mảng ở trên còn khiến bạn băn khoăn.

Khi dữ liệu thay đổi thường xuyên

Nếu mỗi tuần bạn nhận một file mới, Power Query cho phép làm lại toàn bộ bước xử lý chỉ bằng một lần làm mới. Với công thức, bạn phải kiểm tra lại từng chỗ, rất dễ bỏ sót.

Khi cần kết hợp nhiều bảng

Ghép dữ liệu từ nhiều bảng khác nhau bằng Power Query thường gọn hơn. Công thức dồn nhiều tầng INDEX MATCH lồng nhau sẽ khó bảo trì và rất dễ phát sinh lỗi.

Cân nhắc giữa độ phức tạp và sự tiện lợi

Hãy tự hỏi hai câu: người khác có hiểu công thức này không, và sáu tháng sau bạn có còn hiểu không. Nếu câu trả lời là không, đã đến lúc đổi cách làm.

Câu hỏi thường gặp

INDEX MATCH có trả về nhiều giá trị mà không cần công thức mảng không?

Không. Bản thân INDEX MATCH chỉ trả về một giá trị. Muốn có nhiều kết quả, bạn phải kết hợp với cột phụ đánh số, dùng công thức mảng, hoặc chuyển sang FILTER.

Có cách nào lấy tất cả kết quả trùng khớp trong một lần không?

Có, nếu bạn dùng FILTER trên Excel 365 hoặc 2021. Một công thức sẽ trả về toàn bộ danh sách. Trên phiên bản cũ, bạn cần công thức mảng hoặc cột phụ.

Công thức mảng có chạy được trên mọi phiên bản không?

Chạy được trên hầu hết các phiên bản, nhưng cách nhập khác nhau. Bản cũ cần Ctrl + Shift + Enter, bản mới chỉ cần Enter.

Vì sao công thức của tôi trả về #N/A?

Thường là do giá trị tìm kiếm không tồn tại trong vùng dữ liệu, hoặc có khoảng trắng thừa, hoặc giữa số và chữ không cùng kiểu dữ liệu. Hãy kiểm tra lại từng yếu tố này trước khi đổi sang công thức khác.

Nên học INDEX MATCH hay FILTER trước?

Đội ngũ bên mình khuyên bạn học INDEX MATCH trước. Hiểu được cách hai hàm này phối hợp sẽ giúp bạn đọc được hầu hết công thức tra cứu khác. Sau đó, FILTER sẽ rất dễ tiếp thu vì bạn đã nắm logic nền tảng.

Hãy mở một file Excel bất kỳ và thử lấy danh sách theo một điều kiện bạn quan tâm, vừa bằng cột phụ vừa bằng FILTER. Làm trực tiếp như vậy sẽ giúp bạn hiểu nhanh hơn rất nhiều so với chỉ đọc lý thuyết.

Có câu hỏi về bài này?

Làm theo mà kết quả khác bài, hoặc thấy bước nào chưa rõ? Gửi email kèm tên bài và ảnh chụp màn hình, chúng tôi sẽ xem và cập nhật bài khi cần.