Trong Excel, việc tìm kiếm và trích xuất dữ liệu một cách linh hoạt và hiệu quả là yêu cầu quan trọng đối với nhiều người dùng. Một trong những công cụ mạnh mẽ hỗ trợ nhiệm vụ này chính là bộ đôi hàm INDEX và MATCH trong Excel. Sự kết hợp giữa hai hàm này không chỉ khắc phục những hạn chế của VLOOKUP, HLOOKUP mà còn mang lại khả năng dò tìm dữ liệu theo nhiều điều kiện phức tạp. Trong bài viết này, HỌC TIN sẽ hướng dẫn bạn cách sử dụng và kết hợp hàm INDEX và MATCH để nâng cao kỹ năng làm việc với dữ liệu trong Excel.
Giới thiệu về hàm INDEX trong Excel
Khi làm việc với dữ liệu phức tạp trong Excel, việc truy xuất thông tin chính xác từ bảng dữ liệu lớn là một thách thức. Đây là lúc hàm INDEX và MATCH trong Excel phát huy hiệu quả vượt trội. Trong đó, hàm INDEX đóng vai trò quan trọng, cho phép bạn truy cập trực tiếp vào giá trị tại một vị trí cụ thể trong bảng hoặc mảng dữ liệu. Trước khi đi sâu vào sự kết hợp mạnh mẽ này, hãy tìm hiểu chi tiết về hàm INDEX và cách nó hoạt động.
Hàm INDEX là gì?
Hàm INDEX trong Excel là một hàm dùng để trả về giá trị hoặc tham chiếu đến một ô dữ liệu cụ thể trong bảng hoặc mảng, dựa trên số thứ tự dòng và cột mà bạn chỉ định. Đây là công cụ mạnh mẽ giúp bạn dễ dàng trích xuất dữ liệu từ bất kỳ vị trí nào trong bảng mà không cần thay đổi cấu trúc dữ liệu. Hàm INDEX thường được sử dụng độc lập hoặc kết hợp với các hàm khác để thực hiện các thao tác tìm kiếm và truy xuất dữ liệu linh hoạt.
Cú pháp hàm INDEX
Cú pháp của hàm INDEX trong Excel được viết như sau: =INDEX(array, row_num, [column_num])
Trong đó:
- array (đối số bắt buộc): Là vùng dữ liệu mà bạn muốn lấy giá trị, có thể bao gồm một hoặc nhiều hàng và cột.
- row_num (đối số bắt buộc): Số thứ tự hàng trong vùng dữ liệu để xác định vị trí lấy giá trị.
- column_num (đối số tùy chọn): Số thứ tự cột trong vùng dữ liệu để xác định vị trí lấy giá trị.
Hàm sẽ trả về giá trị tại vị trí giao giữa dòng và cột được chỉ định. Nếu bỏ qua đối số column_num, hàm sẽ mặc định trả về giá trị từ cột đầu tiên trong mảng.
Ví dụ về hàm INDEX
Để hiểu rõ hơn cách sử dụng hàm INDEX, chúng ta có thể tham khảo một số ví dụ cụ thể dưới đây. Mỗi công thức sẽ trả về giá trị khác nhau tùy thuộc vào vị trí của dòng và cột mà bạn chỉ định trong mảng dữ liệu.
- =INDEX(A1:B4; 2; 2): Hàm này sẽ tìm giá trị tại giao điểm của hàng thứ 2 và cột thứ 2 trong mảng A1:B4, trả về kết quả là “Vũ Hồng Ngọc”.
- =INDEX(A1:B1; 1): Ở công thức này, hàm INDEX sẽ tìm giá trị trong hàng đầu tiên của mảng A1:B1 và trả về kết quả là “Lê Thanh Khương”.
- =INDEX(2:2; 1): Với công thức này, hàm sẽ tìm giá trị tại giao điểm của cột đầu tiên trong hàng thứ 2, kết quả trả về là “Nguyễn Gia Viễn”.
- =INDEX(B1:B2; 1): Cuối cùng, hàm INDEX này sẽ tìm giá trị trong mảng B1:B2 tại hàng đầu tiên, trả về kết quả là “Bùi Thanh Tuyên”.
Xem thêm: Cách Sử Dụng Hàm IF Nhiều Điều Kiện Trong Excel Chi Tiết, Dễ Hiểu
Giới thiệu về hàm MATCH trong Excel
Sau khi đã hiểu về hàm INDEX, một công cụ mạnh mẽ khác trong Excel mà bạn cần biết là hàm MATCH. Khi kết hợp với hàm INDEX, hàm INDEX và MATCH trong Excel sẽ trở thành một bộ đôi tuyệt vời giúp bạn tìm kiếm và trích xuất dữ liệu chính xác hơn. Hàm MATCH đặc biệt hữu ích khi bạn muốn tìm kiếm vị trí của một giá trị trong một phạm vi nhất định, từ đó làm cơ sở để hàm INDEX hoạt động hiệu quả hơn. Hãy cùng khám phá cách hàm MATCH hoạt động và ứng dụng của nó trong Excel.
Hàm MATCH là gì?
Hàm MATCH trong Excel là một hàm dùng để tìm kiếm vị trí của một giá trị trong một phạm vi hoặc mảng dữ liệu. Khi bạn cung cấp một giá trị cần tìm và một phạm vi dữ liệu, hàm MATCH sẽ trả về vị trí của giá trị đó trong phạm vi, tính từ đầu mảng.
Hàm MATCH thường được sử dụng kết hợp với các hàm khác, như INDEX, để trích xuất dữ liệu một cách linh hoạt và chính xác hơn. Với khả năng tìm kiếm này, hàm MATCH giúp tiết kiệm thời gian và tăng hiệu quả khi làm việc với dữ liệu lớn.
Cú pháp hàm MATCH
Cú pháp của hàm MATCH trong Excel được viết như sau: =MATCH(lookup_value, lookup_array, [match_type])
Trong đó:
- lookup_value (đối số bắt buộc): Là giá trị mà bạn muốn tìm kiếm trong phạm vi dữ liệu. Giá trị này có thể là một số, văn bản hoặc giá trị logic, có thể được nhập thủ công hoặc tham chiếu đến một ô khác.
- lookup_array (đối số bắt buộc): Là phạm vi dữ liệu mà bạn muốn tìm kiếm giá trị, có thể là một hàng hoặc một cột.
- match_type (đối số tùy chọn): Xác định cách tìm kiếm giá trị trong mảng. Nó có thể là 1, 0 hoặc -1. Nếu bỏ qua, mặc định là 1.
- 1: Tìm kiếm giá trị gần nhất nhỏ hơn hoặc bằng lookup_value trong mảng đã sắp xếp theo thứ tự tăng dần.
- 0: Tìm kiếm giá trị chính xác khớp với lookup_value.
- -1: Tìm kiếm giá trị gần nhất lớn hơn hoặc bằng lookup_value trong mảng đã sắp xếp theo thứ tự giảm dần.
Ví dụ về hàm MATCH
Để hiểu rõ hơn về cách hàm MATCH hoạt động, hãy cùng tham khảo một số ví dụ dưới đây. Các ví dụ này sẽ giúp bạn hình dung cách thức hàm MATCH tìm kiếm vị trí của giá trị trong mảng và cách kết quả trả về, tùy thuộc vào các tham số được sử dụng.
- =MATCH(“Vũ Hồng Ngọc”, A2:D2, 0): Hàm sẽ tìm kiếm giá trị “Vũ Hồng Ngọc” trong phạm vi A2:D2. Kết quả trả về là 3, vì “Vũ Hồng Ngọc” nằm ở vị trí thứ ba trong phạm vi này.
- =MATCH(16, D1:D3): Trong trường hợp này, hàm MATCH sẽ tìm kiếm giá trị 16 trong phạm vi D1:D3. Tuy nhiên, vì 16 không tồn tại trong phạm vi, hàm sẽ tìm kiếm giá trị lớn nhất gần nhất nhỏ hơn hoặc bằng 16, và kết quả trả về là 13, nằm ở vị trí thứ nhất của mảng.
- =MATCH(16, D1:D3, -1): Tương tự ví dụ trên, nhưng với tham số -1, hàm yêu cầu tìm kiếm trong một mảng có thứ tự giảm dần. Vì mảng không theo thứ tự giảm dần, hàm trả về lỗi #N/A.
- =MATCH(13, A1:D1, 0): Hàm MATCH sẽ tìm kiếm giá trị 13 trong hàng đầu tiên của bảng dữ liệu (A1:D1). Kết quả trả về là 4, vì 13 là giá trị thứ tư trong mảng này.
Xem thêm: Cách Dùng Hàm SUMIF Trong Excel Để Tính Tổng Có Điều Kiện
Hàm INDEX kết hợp MATCH trong Excel là gì?
Hàm INDEX kết hợp với MATCH trong Excel là một công thức mạnh mẽ giúp bạn tìm kiếm và trả về giá trị từ một bảng dữ liệu, dựa trên cả hàng và cột. Trong khi hàm INDEX cho phép bạn xác định một giá trị trong một phạm vi ô theo vị trí hàng và cột, hàm MATCH lại giúp xác định chính xác vị trí của giá trị cần tìm trong phạm vi dữ liệu.
Khi kết hợp hai hàm này, bạn có thể thực hiện các phép tìm kiếm linh hoạt và chính xác hơn so với việc sử dụng một mình hàm VLOOKUP hay HLOOKUP, đặc biệt là trong trường hợp cần tìm kiếm theo chiều ngang và chiều dọc hoặc khi dữ liệu không được sắp xếp theo thứ tự nhất định.
Khi nào nên kết hợp hàm INDEX và MATCH trong Excel?
Kết hợp hàm INDEX và MATCH trong Excel là một giải pháp lý tưởng khi bạn cần truy xuất dữ liệu từ các bảng tính phức tạp, đặc biệt khi dữ liệu có nhiều tiêu chí và yêu cầu tìm kiếm trong nhiều chiều khác nhau. Việc sử dụng hai hàm này giúp bạn vượt qua những hạn chế mà các hàm như VLOOKUP hay HLOOKUP có, đặc biệt khi làm việc với bảng dữ liệu có cấu trúc thay đổi hoặc yêu cầu tìm kiếm không theo một thứ tự cố định.
Sự kết hợp này cho phép bạn thực hiện tìm kiếm linh hoạt hơn, có thể tra cứu dữ liệu theo cả chiều ngang và chiều dọc mà không bị giới hạn bởi vị trí cột hay hàng. Điều này không chỉ tăng tính linh hoạt và hiệu quả trong công việc, mà còn đảm bảo độ chính xác khi truy xuất thông tin từ các bảng dữ liệu phức tạp, giúp bạn xử lý nhanh chóng và chính xác các yêu cầu tìm kiếm đa dạng.
Ví dụ về hàm INDEX kết hợp hàm MATCH trong Excel
Kết hợp hàm INDEX và MATCH trong Excel mang lại khả năng linh hoạt cao trong việc tra cứu dữ liệu, đặc biệt khi bạn cần tìm kiếm thông tin dựa trên các tiêu chí khác nhau như hàng và cột. Dưới đây là hai ví dụ thực tế minh họa cách sử dụng hai hàm này trong cùng một công thức.
Ví dụ 1: Tìm tham chiếu ô trong bảng
Công thức: =INDEX(C2:C5;MATCH(F1;B2:B5))
Trong ví dụ này, chúng ta kết hợp công thức hàm MATCH vào trong hàm INDEX để tìm giá trị tương ứng với mã số đã cho. Cách thức hoạt động như sau:
- Hàm MATCH(F1;B2:B5) tìm kiếm giá trị trong ô F1 (8795) trong phạm vi B2:B5. Kết quả mà hàm MATCH trả về là vị trí của giá trị F1 trong phạm vi này, là vị trí số 2.
- Sau khi có kết quả từ hàm MATCH, hàm INDEX sẽ sử dụng giá trị 2 này để tra cứu giá trị trong mảng C2:C5, trả về giá trị tại vị trí thứ hai trong mảng đó. Kết quả là “Bút nước Thiên Long”.
Ví dụ 2: Tra cứu theo tiêu đề hàng và cột
Công thức: =INDEX(B2:E13;MATCH(G1;A2:A13;0);MATCH(G2;B1:E1;0))
Trong ví dụ này, công thức thực hiện tra cứu hai chiều theo cả hàng và cột để tìm số lượng bút tre đã bán trong tháng 5. Cách hoạt động cụ thể như sau:
- Hàm MATCH(G1;A2:A13;0) tìm kiếm giá trị trong ô G1 (5) trong phạm vi A2:A13 để xác định hàng mà chúng ta cần tra cứu, là vị trí số 6.
- Hàm MATCH(G2;B1:E1;0) tìm kiếm giá trị trong ô G2 (Bút tre) trong phạm vi B1:E1 để xác định cột tương ứng cần tra cứu, là vị trí số 3.
- Cuối cùng, hàm INDEX sẽ tra cứu trong bảng B2:E13 và trả về giá trị tại hàng thứ 6 và cột thứ 3, kết quả là 41. Đây chính là số lượng bút tre bán được trong tháng 5 mà bạn cần tìm.
Xem thêm: Cách Sử Dụng Hàm TRIM Trong Excel Loại Bỏ Khoảng Trắng Thừa
So sánh hàm INDEX và MATCH với VLOOKUP, HLOOKUP
Khi cần tra cứu dữ liệu trong Excel, nhiều người vẫn lựa chọn hàm VLOOKUP hoặc hàm HLOOKUP vì sự đơn giản và quen thuộc. Tuy nhiên, với những người sử dụng thành thạo Excel, hàm INDEX và MATCH trong Excel thường được đánh giá cao hơn nhờ vào những ưu điểm vượt trội mà các hàm VLOOKUP và HLOOKUP không thể sánh kịp.
- Tra cứu từ phải sang trái: Một trong những hạn chế lớn nhất của hàm VLOOKUP là nó chỉ có thể tra cứu dữ liệu theo chiều dọc từ trái qua phải. Điều này có nghĩa là giá trị tra cứu phải luôn nằm ở cột bên trái bảng dữ liệu. Trong khi đó, hàm INDEX và MATCH trong Excel cho phép tra cứu dữ liệu ở bất kỳ vị trí nào, bao gồm cả từ phải sang trái, điều mà VLOOKUP không thể làm được.
- Chèn hoặc xóa cột an toàn: Một điểm yếu của hàm VLOOKUP là nếu bạn thay đổi cấu trúc bảng, ví dụ như thêm hoặc xóa cột, công thức VLOOKUP sẽ bị sai do nó dựa vào chỉ số cột cố định. Hàm INDEX và MATCH trong Excel lại không gặp vấn đề này, bởi vì bạn có thể thay đổi cấu trúc bảng mà không làm ảnh hưởng đến công thức tra cứu, giúp công thức luôn chính xác dù có thay đổi dữ liệu.
- Không giới hạn kích thước giá trị tra cứu: Khi sử dụng hàm VLOOKUP, nếu giá trị tra cứu có độ dài vượt quá 255 ký tự, bạn sẽ gặp lỗi #VALUE!. Tuy nhiên, hàm INDEX và MATCH trong Excel không bị giới hạn như vậy, giúp bạn có thể xử lý các chuỗi ký tự dài mà không gặp phải vấn đề này.
- Tốc độ xử lý nhanh hơn: Nếu bảng dữ liệu của bạn có kích thước lớn với hàng nghìn dòng và công thức phức tạp, hàm INDEX và MATCH trong Excel sẽ xử lý nhanh hơn hàm VLOOKUP. Điều này là vì INDEX MATCH chỉ tra cứu ở các cột cần thiết, trong khi VLOOKUP phải quét toàn bộ bảng. Do đó, khi làm việc với các bảng dữ liệu lớn, hàm INDEX và MATCH giúp cải thiện hiệu suất và tiết kiệm thời gian xử lý.
- Tác động đến hiệu suất trong các công thức phức tạp: Trong trường hợp bạn đang làm việc với các công thức mảng phức tạp hoặc sử dụng nhiều hàm tra cứu trong một bảng tính, hàm VLOOKUP có thể gây ảnh hưởng lớn đến hiệu suất của Excel. Việc phải kiểm tra từng giá trị một trong mảng với VLOOKUP sẽ làm giảm tốc độ hoạt động của Excel, trong khi hàm INDEX và MATCH trong Excel hoạt động nhanh hơn và hiệu quả hơn trong những trường hợp này.
Tóm lại, hàm INDEX và MATCH trong Excel có nhiều ưu điểm vượt trội so với hàm VLOOKUP và hàm HLOOKUP, đặc biệt là khi làm việc với các bảng dữ liệu lớn, yêu cầu linh hoạt trong việc tra cứu, hoặc cần sự chính xác cao khi thay đổi cấu trúc bảng.
Xem thêm: 7 Hàm Trung Bình Nâng Cao Trong Excel: Công Thức Và Ví Dụ Thực Tế
Cách sử dụng hàm INDEX và MATCH trong Excel
Khi làm việc với dữ liệu trong Excel, việc sử dụng hàm INDEX và MATCH sẽ giúp bạn thực hiện các phép tìm kiếm và trích xuất dữ liệu chính xác hơn so với các hàm đơn lẻ như VLOOKUP hay HLOOKUP. Sự kết hợp này mang lại tính linh hoạt, cho phép tìm kiếm dữ liệu theo chiều ngang hoặc dọc mà không bị giới hạn bởi cấu trúc bảng. Trong phần này, chúng ta sẽ cùng tìm hiểu cách sử dụng hàm INDEX và MATCH trong Excel để tối ưu hóa việc truy xuất thông tin trong các bảng tính phức tạp.
Cách dùng hàm INDEX và MATCH dò tìm từ trái sang phải
Để dò tìm dữ liệu từ trái sang phải trong Excel, bạn có thể kết hợp hàm INDEX và MATCH trong Excel. Trong trường hợp này, hàm MATCH sẽ xác định vị trí của giá trị bạn muốn tra cứu, còn hàm INDEX sẽ dùng vị trí đó để trả về giá trị tương ứng từ cột cần tra cứu.
Công thức hàm INDEX và MATCH dò tìm từ trái sang phải như sau:
=INDEX(cột chứa giá trị cần trả về, MATCH(giá trị cần tìm, cột chứa giá trị tra cứu, 0))
Ví dụ minh họa: Giả sử bạn có danh sách các quốc gia, thủ đô của chúng và dân số tương ứng.
Để tìm dân số của thủ đô Nhật Bản, bạn có thể sử dụng công thức sau: =INDEX(C2:C10, MATCH(“Japan”, A2:A10, 0))
Giải thích công thức:
- Hàm MATCH tìm kiếm giá trị “Japan” trong phạm vi A2:A10 và trả về số hàng, ở đây là số 3, vì “Japan” nằm ở hàng thứ ba trong mảng.
- Số hàng này được chuyển tới đối số row_num của hàm INDEX, hướng dẫn nó trả về giá trị từ hàng đó trong phạm vi C2:C10.
- Kết quả là công thức INDEX(C2:C10, 3) trả về giá trị tại hàng thứ ba trong mảng C2:C10, tương ứng với dân số của Nhật Bản.
- Nếu không muốn cố định giá trị cần tra cứu, bạn có thể thay thế “Japan” bằng tham chiếu ô (ví dụ: F1) để tạo thành công thức tra cứu động: =INDEX(C2:C10, MATCH(F1, A2:A10, 0))
Lưu ý: Số hàng trong phạm vi của hàm INDEX phải khớp với số hàng trong phạm vi tra cứu của MATCH. Nếu không, kết quả sẽ không chính xác.
Cách dùng hàm INDEX và MATCH dò tìm từ phải sang trái
Hàm INDEX và MATCH trong Excel có khả năng dò tìm linh hoạt từ phải sang trái, điều mà hàm VLOOKUP không thể thực hiện được. Với sự kết hợp này, bạn có thể tra cứu dữ liệu ngay cả khi cột tra cứu nằm bên phải cột kết quả.
Ví dụ minh họa: Giả sử bạn thêm cột (Rank) vào bên trái bảng dữ liệu và muốn tìm thứ hạng dân số của thủ đô Moscow (Nga). Với giá trị cần tra cứu trong ô G1, bạn có thể sử dụng công thức sau để tìm trong phạm vi C2:C10 và trả về giá trị tương ứng từ A2:A10: =INDEX(A2:A10, MATCH(G1, C2:C10, 0))
Giải thích công thức:
- MATCH(G1, C2:C10, 0): Xác định vị trí của giá trị trong G1 trong cột C2:C10.
- INDEX(A2:A10, …): Lấy giá trị tương ứng từ cột A2:A10 dựa trên vị trí được trả về bởi MATCH.
Lưu ý: Nếu bạn muốn sử dụng công thức này cho nhiều ô, hãy đảm bảo khóa phạm vi bằng cách sử dụng tham chiếu tuyệt đối (ví dụ: $A$2:$A$10 và $C$2:$C$10). Điều này giúp giữ nguyên phạm vi khi sao chép công thức sang các ô khác.
Xem thêm: Cách Dùng Hàm DATE Trong Excel Xử Lý Dữ Liệu Ngày Tháng Năm
Cách dùng hàm INDEX và MATCH tìm kiếm theo hàng và cột
Hàm INDEX và MATCH trong Excel không chỉ hữu ích cho việc tìm kiếm giá trị trong một cột duy nhất, mà còn rất mạnh mẽ khi áp dụng cho việc tìm kiếm theo hàng và cột trong một bảng dữ liệu. Khi bạn cần tra cứu dữ liệu trong một ma trận (matrix) hoặc trong một phạm vi hai chiều, công thức này trở nên đặc biệt hữu ích. Để thực hiện việc này, bạn sẽ sử dụng hai hàm MATCH: một hàm để xác định số hàng và một hàm còn lại để xác định số cột.
Công thức hàm INDEX và MATCH tìm kiếm theo hàng và cột như sau:
=INDEX (array, MATCH(lookup_value, column_to_lookup_against, 0), MATCH(lookup_value, row_to_lookup_against, 0))
Ví dụ minh họa: Giả sử bạn có một bảng dữ liệu về dân số của các quốc gia trong các năm khác nhau và bạn muốn tìm dân số của Trung Quốc trong năm 2015. Nếu quốc gia được nhập ở ô G1 và năm được nhập ở ô G2, công thức sẽ như sau: =INDEX(B2:D11, MATCH(G1, A2:A11, 0), MATCH(G2, B1:D1, 0))
Giải thích công thức:
- MATCH(G1, A2:A11, 0) sẽ tìm kiếm giá trị trong ô G1 (Trung Quốc) trong phạm vi A2:A11 và trả về vị trí của nó, trong trường hợp này là số 2.
- MATCH(G2, B1:D1, 0) sẽ tìm kiếm giá trị trong ô G2 (2015) trong phạm vi B1:D1 và trả về vị trí của nó, là số 3.
- Sau khi có được số hàng (2) và số cột (3), công thức INDEX(B2:D11, 2, 3) sẽ trả về giá trị tại giao điểm của hàng thứ 2 và cột thứ 3 trong phạm vi B2:D11, đó là dân số của Trung Quốc trong năm 2015.
Cách dùng hàm INDEX và MATCH tìm kiếm với nhiều điều kiện
Nếu bạn đã quen thuộc với các công thức VLOOKUP trong Excel, có thể bạn đã nghe đến việc tra cứu dữ liệu với nhiều tiêu chí. Tuy nhiên, một hạn chế lớn khi sử dụng VLOOKUP là bạn cần phải thêm cột trợ giúp để kết hợp các tiêu chí. Điều này có thể làm dữ liệu của bạn trở nên phức tạp và khó quản lý. Thật may mắn, hàm INDEX và MATCH trong Excel có thể giúp bạn thực hiện tra cứu với nhiều tiêu chí mà không cần phải thay đổi cấu trúc hoặc dữ liệu ban đầu.
Công thức hàm INDEX và MATCH trong Excel để tra cứu với nhiều điều kiện có dạng sau:
{= INDEX(return_range, MATCH(1, (Criteria1 = RANGE1) * (Criteria2 = RANGE2), 0))}
Lưu ý: Công thức này phải được nhập dưới dạng công thức mảng, tức là bạn phải nhấn Ctrl + Shift + Enter thay vì chỉ nhấn Enter để hoàn tất công thức. Khi công thức được nhập đúng, Excel sẽ tự động đặt công thức trong dấu ngoặc nhọn {}.
Ví dụ minh họa: Bạn có một bảng dữ liệu chứa thông tin về khách hàng và sản phẩm. Giả sử bạn cần tìm số tiền dựa trên hai tiêu chí: Khách hàng (Customer) và Sản phẩm (Product). Công thức sẽ như sau: =INDEX(C2:C10, MATCH(1, (F1=A2:A10) * (F2=B2:B10), 0))
Giải thích công thức:
- C2:C10: Dải ô trả về giá trị (số tiền).
- F1: Tiêu chí 1 (tên khách hàng).
- A2:A10: Phạm vi để so sánh với tiêu chí 1.
- F2: Tiêu chí 2 (tên sản phẩm).
- B2:B10: Phạm vi để so sánh với tiêu chí 2.
Khi bạn nhấn Ctrl + Shift + Enter, Excel sẽ tự động đặt công thức trong dấu ngoặc nhọn, điều này cho thấy công thức mảng đã được áp dụng chính xác.
Lưu ý: Nếu không muốn sử dụng công thức mảng, bạn có thể kết hợp thêm một hàm INDEX khác để công thức hoạt động mà chỉ cần nhấn Enter thông thường như hình dưới đây:
Cách hoạt động của công thức:
- Từng tiêu chí được so sánh với cột tương ứng, tạo ra các mảng TRUE và FALSE.
- Phép nhân giữa các mảng này chuyển đổi TRUE thành 1 và FALSE thành 0, tạo ra một mảng mà chỉ các hàng khớp với tất cả tiêu chí mới có giá trị là 1.
- MATCH(1, array, 0) tìm vị trí của giá trị 1 đầu tiên trong mảng, đại diện cho hàng khớp.
- Kết quả từ MATCH được chuyển đến INDEX, trả về giá trị từ hàng tương ứng trong cột chỉ định. Đối với công thức không dùng mảng, hàm INDEX thứ hai được cấu hình với row_num = 0, giúp xử lý toàn bộ mảng trực tiếp mà không cần phím tắt.
Xem thêm: Cách Sử Dụng Hàm DATEDIF Trong Excel Tính Chênh Lệch Thời Gian
Cách kết hợp hàm INDEX và MATCH với hàm VLOOKUP
Hàm INDEX và MATCH trong Excel có thể được kết hợp với hàm VLOOKUP để tạo ra các công thức mạnh mẽ, giúp tra cứu dữ liệu một cách linh hoạt hơn. Khi kết hợp, bộ ba hàm INDEX – MATCH – VLOOKUP giúp bạn tra cứu dữ liệu một cách chi tiết hơn, đặc biệt khi bạn cần kết hợp nhiều điều kiện hoặc muốn tìm kiếm giá trị trong bảng dữ liệu có nhiều cột.
Công thức kết hợp hàm INDEX và MATCH với hàm VLOOKUP có dạng như sau:
=VLOOKUP(INDEX(tham_chiếu, [hàng], [cột]), table_array, col_INDEX_num, [range_lookup])
Trong đó:
- INDEX(tham_chiếu, [hàng], [cột]): Đây là phần sử dụng hàm INDEX để xác định giá trị cần tra cứu. Hàm này sẽ tìm kiếm giá trị trong phạm vi được chỉ định theo hàng và cột.
- table_array: Là bảng dữ liệu chứa giá trị bạn muốn tra cứu.
- col_INDEX_num: Là số thứ tự của cột trong bảng chứa dữ liệu mà bạn muốn tìm kiếm.
- [range_lookup]: Xác định kiểu tìm kiếm. Nếu sử dụng giá trị 0, hàm sẽ dò tìm chính xác, còn nếu là 1 thì tìm kiếm theo dạng gần đúng.
Ví dụ minh họa: Giả sử bạn có một bảng dữ liệu dùng để xác định điểm vùng của từng học sinh dựa trên khu vực. Công thức áp dụng sẽ là: =VLOOKUP(INDEX(A2:F9, 1, 6), $I$2:$J$9, 2, 0)
Giải thích công thức:
- INDEX(A2:F9, 1, 6): Dùng hàm INDEX để lấy giá trị tại ô giao giữa hàng 1 và cột 6 trong phạm vi A2:F9.
- $I$2:$J$9: Là phạm vi chứa bảng dữ liệu để dò tìm.
- 2: Là vị trí cột chứa điểm vùng mà bạn muốn tra cứu.
- 0: Là kiểu dò tìm chính xác, không cho phép tìm kiếm gần đúng.
Cách kết hợp hàm INDEX và MATCH với hàm IF
Kết hợp hàm INDEX và MATCH trong Excel với hàm IF là một kỹ thuật hữu ích giúp bạn tra cứu dữ liệu và áp dụng điều kiện vào kết quả. Công thức kết hợp này cho phép bạn thực hiện một phép so sánh, kiểm tra dữ liệu sau khi tra cứu, và trả về giá trị dựa trên điều kiện bạn chỉ định.
Công thức kết hợp giữa hàm INDEX và MATCH với hàm IF có dạng như sau:
=IF(INDEX(mảng_dữ_liệu, MATCH(khóa_tìm_kiếm, dải_ô, loại_tìm_kiếm), số_cột) + điều_kiện, giá_trị_nếu_đúng, giá_trị_nếu_sai)
Trong đó:
- mảng_dữ_liệu: Là phạm vi ô hoặc mảng dữ liệu bạn cần sử dụng.
- khóa_tìm_kiếm: Là giá trị bạn muốn tìm kiếm trong dải ô.
- dải_ô: Là phạm vi chứa dữ liệu để xác định vị trí của khóa tìm kiếm. Dải ô này phải là một hàng hoặc một cột duy nhất.
- loại_tìm_kiếm: Phương thức tìm kiếm, với 3 lựa chọn chính: 0 (tìm kiếm chính xác), 1 (mặc định giá trị), và -1 (dùng khi dải ô đã được sắp xếp giảm dần).
- số_cột: Là số cột trong mảng dữ liệu mà bạn muốn lấy giá trị trả về.
- giá_trị_nếu_đúng: Là giá trị được trả về nếu điều kiện đúng.
- giá_trị_nếu_sai: Là giá trị được trả về nếu điều kiện sai.
Ví dụ minh họa: Giả sử bạn có bảng dữ liệu về sản phẩm bánh quy, và bạn cần xác định xem bánh quy đã đạt chỉ tiêu KPI hay chưa. Cụ thể, số lượng bánh quy bán ra phải lớn hơn 10 mới được coi là đạt chỉ tiêu. Công thức áp dụng sẽ là: =IF(INDEX(A2:F5, MATCH(‘Bánh quy’, A2:A5, 0), 3) > 10, ‘Đạt’, ‘Không đạt’)
Giải thích công thức:
- MATCH(‘Bánh quy’, A2:A5, 0): Hàm MATCH tìm vị trí hàng của sản phẩm “Bánh quy” trong dải ô A2:A5.
- INDEX(A2:F5, … , 3): Hàm INDEX trả về giá trị trong cột thứ 3 (số lượng bán) của hàng chứa “Bánh quy”.
- Sau khi tra cứu số lượng bán, công thức so sánh xem giá trị này có lớn hơn 10 không. Nếu đúng, trả về “Đạt”, nếu sai thì trả về “Không đạt”.
Xem thêm: Cách Sử Dụng Hàm SUMPRODUCT Trong Excel Tính Tổng Các Tích
Cách kết hợp hàm INDEX và MATCH với hàm MIN/ MAX/ AVERAGE
Microsoft Excel cung cấp các hàm hữu ích để tìm giá trị nhỏ nhất, lớn nhất và trung bình trong một phạm vi dữ liệu. Tuy nhiên, trong trường hợp bạn muốn lấy giá trị tương ứng từ một ô khác có liên kết với các giá trị đó, bạn có thể sử dụng kết hợp hàm INDEX và MATCH trong Excel với các hàm MIN, MAX hoặc AVERAGE.
Kết hợp hàm INDEX – MATCH với hàm MAX
Để tìm giá trị lớn nhất trong một cột (ví dụ cột D) và trả về giá trị tương ứng từ cột C, bạn có thể sử dụng công thức sau: =INDEX(C2:C10, MATCH(MAX(D2:D10), D2:D10, 0))
Giải thích công thức:
- MAX(D2:D10) sẽ tìm giá trị lớn nhất trong phạm vi D2:D10.
- MATCH(MAX(D2:D10), D2:D10, 0) xác định vị trí của giá trị lớn nhất trong cột D.
- INDEX(C2:C10, …) sẽ trả về giá trị ở cùng một hàng nhưng trong cột C tương ứng.
Kết hợp hàm INDEX – MATCH với hàm MIN
Tương tự như với hàm MAX, nếu bạn muốn tìm giá trị nhỏ nhất trong cột D và lấy giá trị tương ứng từ cột C, bạn có thể dùng công thức sau: =INDEX(C2:C10, MATCH(MIN(D2:D10), D2:D10, 0))
Giải thích công thức:
- MIN(D2:D10) tìm giá trị nhỏ nhất trong phạm vi D2:D10.
- MATCH(MIN(D2:D10), D2:D10, 0) xác định vị trí của giá trị nhỏ nhất trong cột D.
- INDEX(C2:C10, …) trả về giá trị từ cột C liên quan đến hàng có giá trị nhỏ nhất trong cột D.
Kết hợp hàm INDEX – MATCH với hàm AVERAGE
Để tính giá trị gần nhất với giá trị trung bình trong một phạm vi (ví dụ D2:D10) và nhận giá trị liên quan từ cột C, bạn có thể sử dụng công thức sau: =INDEX(C2:C10, MATCH(AVERAGE(D2:D10), D2:D10, -1))
Giải thích công thức:
- AVERAGE(D2:D10) tính giá trị trung bình của các giá trị trong phạm vi D2:D10.
- MATCH(AVERAGE(D2:D10), D2:D10, -1) tìm vị trí của giá trị gần nhất với giá trị trung bình trong phạm vi D2:D10.
- INDEX(C2:C10, …) trả về giá trị tương ứng từ cột C dựa trên vị trí tìm được.
Lưu ý:
- Nếu cột tra cứu (D) được sắp xếp theo thứ tự tăng dần, bạn sẽ dùng giá trị 1 cho đối số thứ ba trong MATCH để tìm giá trị nhỏ nhất lớn hơn hoặc bằng giá trị trung bình.
- Nếu cột tra cứu (D) được sắp xếp theo thứ tự giảm dần, bạn sẽ dùng -1 để tìm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị trung bình.
- Nếu bạn muốn tìm chính xác giá trị trung bình, sử dụng 0 để khớp chính xác mà không cần phải sắp xếp.
Cụ thể, trong trường hợp này, các giá trị trong cột D được sắp xếp giảm dần nên bạn sẽ sử dụng -1 cho tham số match_type. Nếu giá trị trung bình của cột D là 12.269.006, công thức sẽ trả về “Tokyo” vì dân số của Tokyo (13.189.000) là giá trị gần nhất lớn hơn giá trị trung bình.
Cách kết hợp hàm INDEX và MATCH với hàm IFERROR
Cách kết hợp hàm INDEX và MATCH với hàm IFERROR trong Excel là một cách hữu ích để xử lý các lỗi phổ biến như lỗi #N/A hoặc #VALUE khi sử dụng hàm tra cứu. Cụ thể, khi bạn sử dụng hàm INDEX và MATCH trong Excel để tra cứu giá trị từ một bảng dữ liệu, đôi khi sẽ gặp phải các lỗi nếu giá trị không tồn tại hoặc không phù hợp trong phạm vi tìm kiếm. Việc sử dụng hàm INDEX và MATCH trong Excel kết hợp với hàm IFERROR cho phép bạn trả về một thông báo rõ ràng hoặc giá trị thay thế thay vì hiển thị lỗi khi không tìm thấy dữ liệu.
Cú pháp của hàm IFERROR như sau: =IFERROR(value, value_if_error)
Trong đó:
- value: Là giá trị bạn muốn kiểm tra lỗi (thường là kết quả của công thức INDEX và MATCH).
- value_if_error: Là giá trị hoặc thông báo bạn muốn trả về nếu công thức gặp lỗi.
Ví dụ minh họa: Giả sử bạn đang sử dụng hàm INDEX và MATCH trong Excel để tìm kiếm thông tin trong bảng, nhưng có thể xảy ra trường hợp không tìm thấy dữ liệu phù hợp. Để tránh lỗi và hiển thị thông báo dễ hiểu, bạn có thể kết hợp các hàm này với hàm IFERROR. Công thức áp dụng như sau:
=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!”)
Trong trường hợp này, nếu công thức INDEX và MATCH trong Excel không tìm thấy giá trị tương ứng, hàm IFERROR sẽ thay thế lỗi bằng thông báo “Không tìm thấy. Xin hãy thử lại!” thay vì hiển thị lỗi #N/A hoặc #VALUE.
Lưu ý: Nếu bạn không muốn hiển thị bất kỳ thông báo nào và muốn ô trống khi không tìm thấy giá trị, bạn chỉ cần thay thế phần “Không tìm thấy. Xin hãy thử lại!” bằng một cặp dấu nháy trống “”. Công thức sẽ trở thành:
=IFERROR(INDEX($A$1:$E$11, MATCH($G$2,$B$1:$B$11,0), MATCH($G$3,$A$1:$E$1,0)),””)
Xem thêm: Hàm WEEKDAY – Hàm Lấy Thứ Trong Excel: Cú Pháp & Cách Dùng
Những lưu ý quan trọng khi kết hợp hàm INDEX và MATCH
Kết hợp hàm INDEX và MATCH trong Excel mang lại rất nhiều lợi ích trong việc tra cứu và truy xuất dữ liệu, nhưng để sử dụng chúng hiệu quả, bạn cần lưu ý một số điểm quan trọng. Nếu không nắm vững những lưu ý này, bạn có thể gặp phải các lỗi hoặc kết quả không chính xác trong quá trình sử dụng công thức. Dưới đây là những điểm cần chú ý khi làm việc với hai hàm này:
- Hàm MATCH không phân biệt chữ hoa và chữ thường: Khi sử dụng MATCH, hàm sẽ xử lý chữ hoa và chữ thường giống nhau khi khớp các giá trị văn bản. Do đó, bạn không cần phải lo lắng về sự khác biệt giữa chúng khi tra cứu.
- Lỗi #N/A trong hàm MATCH: Hàm MATCH có thể trả về lỗi #N/A trong một số tình huống như:
- match_type là 0 nhưng giá trị tra cứu không tìm thấy.
- match_type là -1 nhưng lookup_array không theo thứ tự giảm dần.
- match_type là 1 nhưng lookup_array không theo thứ tự tăng dần.
- lookup_array không phải là một hàng hoặc một cột.
- Sử dụng ký tự đại diện trong MATCH: Khi match_type = 0 và bạn đang tra cứu một chuỗi văn bản, bạn có thể sử dụng các ký tự đại diện. Ví dụ, dấu chấm hỏi (?) sẽ khớp với một ký tự bất kỳ, và dấu hoa thị (*) sẽ khớp với bất kỳ chuỗi ký tự nào.
- Lỗi #REF! trong hàm INDEX: Nếu row_num hoặc column_num trong INDEX không trỏ đến một ô hợp lệ trong mảng, hàm sẽ trả về lỗi #REF!. Điều này cần được kiểm tra kỹ khi sử dụng công thức.
- Hàm MATCH chỉ trả về vị trí, không phải giá trị: Hàm MATCH chỉ tìm ra vị trí của giá trị trong mảng, không trả về giá trị thực tế. Do đó, bạn cần kết hợp nó với hàm INDEX để lấy giá trị tương ứng.
- Mảng trả về trong hàm INDEX: Nếu đối số cột hoặc hàng trong hàm INDEX bằng 0, kết quả trả về sẽ là một hàng hoặc một cột, thay vì một giá trị cụ thể.
- Tìm kiếm theo chiều ngang hoặc dọc: Khi sử dụng hàm INDEX và MATCH trong Excel, bạn có thể tìm kiếm giá trị theo chiều dọc hoặc chiều ngang tùy thuộc vào cách bạn chọn mảng. Nếu bạn chọn một cột hoặc một hàng, hàm sẽ tìm kiếm theo chiều dọc hoặc ngang tương ứng. Nếu chọn mảng hai chiều, hàm có thể tìm kiếm trong cả hai chiều.
- Tìm kiếm giá trị duy nhất: Hàm INDEX và MATCH trong Excel có thể tìm kiếm giá trị trong bất kỳ vị trí nào trong mảng, nhưng bạn cần đảm bảo rằng giá trị cần tìm là duy nhất. Nếu có nhiều giá trị trùng lặp, hàm sẽ trả về vị trí của giá trị đầu tiên được tìm thấy.
- Kết hợp với các hàm khác: Bạn có thể kết hợp INDEX và MATCH với các hàm khác như AND, OR, IF để tạo ra các công thức phức tạp hơn. Tuy nhiên, bạn cần chú ý đến thứ tự ưu tiên của các toán tử và hàm để đảm bảo kết quả chính xác.
- Thứ tự tham số trong công thức: Một lưu ý quan trọng khi kết hợp hàm INDEX và MATCH trong Excel là thứ tự tham số của chúng hoàn toàn khác nhau. Bạn cần nhớ công thức riêng biệt của mỗi hàm để tránh nhầm lẫn khi kết hợp chúng trong một công thức.
- Đảm bảo chỉ số hàng và cột khớp nhau: Khi làm việc với bảng dữ liệu có nhiều hàng và cột, bạn cần kiểm tra kỹ các chỉ số hàng và cột trong INDEX và MATCH để đảm bảo chúng khớp với nhau. Nếu không, kết quả trả về có thể không chính xác.
Những lưu ý trên sẽ giúp bạn sử dụng hàm INDEX và MATCH trong Excel một cách hiệu quả và tránh được những lỗi thường gặp khi làm việc với các bảng dữ liệu phức tạp.
Xem thêm: Cách Dùng Hàm SORT Trong Excel Sắp Xếp Dữ Liệu Nhanh Chóng
Một số lỗi thường gặp khi dùng hàm INDEX và MATCH
Khi sử dụng hàm INDEX và MATCH trong Excel, mặc dù chúng rất mạnh mẽ và linh hoạt trong việc tra cứu dữ liệu, nhưng người dùng thường gặp phải một số lỗi phổ biến. Những lỗi này có thể làm ảnh hưởng đến độ chính xác của kết quả và gây khó khăn trong quá trình áp dụng công thức. Trong phần dưới đây, chúng ta sẽ cùng điểm qua một số lỗi thường gặp khi sử dụng hàm INDEX và MATCH và cách khắc phục chúng.
Lỗi #REF!
Nguyên nhân: Lỗi #REF! là một trong những lỗi phổ biến khi sử dụng hàm INDEX và MATCH trong Excel. Lỗi này thường xuất hiện khi dải ô tham chiếu bị sai hoặc bị xóa, khiến Excel không thể xác định được vùng dữ liệu cần tra cứu. Ví dụ, khi sử dụng hàm INDEX, đối số đầu tiên là tham chiếu đến vùng dữ liệu và nó không cần phải được đặt trong dấu nháy kép.
Cách khắc phục: Để giải quyết lỗi này, bạn cần kiểm tra lại các tham chiếu trong công thức hàm INDEX và MATCH để đảm bảo rằng các dải ô đã được chỉ định chính xác và không bị thay đổi hoặc xóa.
Lỗi #N/A
Nguyên nhân: Lỗi #N/A trong hàm INDEX và MATCH trong Excel thường xuất hiện khi bạn nhập sai dải ô hoặc khi giá trị tra cứu không thể tìm thấy. Một ví dụ phổ biến là khi sử dụng hàm MATCH, nếu dải ô bạn chọn chỉ có một hàng hoặc một cột nhưng lại chọn nhiều cột, điều này có thể gây ra lỗi #N/A.
Cách khắc phục: Để giải quyết lỗi này, bạn cần kiểm tra và chắc chắn rằng dải ô đã chọn đúng, ví dụ như chọn một cột duy nhất (A2:A9) hoặc một hàng duy nhất (B1:B9) thay vì chọn nhiều cột. Điều này sẽ giúp công thức hoạt động chính xác và tránh lỗi #N/A.
Lỗi #ERROR!
Nguyên nhân: Lỗi #ERROR trong hàm INDEX và MATCH trong Excel thường xảy ra khi cú pháp của hàm bị sai. Một ví dụ phổ biến là khi bạn sử dụng hàm INDEX và nhập sai dấu phân cách giữa các đối số, như thay vì dấu phẩy (,), bạn lại sử dụng dấu chấm phẩy (;) hoặc ngược lại.
Cách khắc phục: Để sửa lỗi này, bạn chỉ cần thay dấu phân cách giữa các đối số trong công thức, ví dụ như sửa lại dấu phẩy (,) thành dấu chấm phẩy (;) tùy theo quy chuẩn của phiên bản Excel bạn đang sử dụng. Sau khi sửa cú pháp, công thức sẽ hoạt động bình thường.
Lỗi #VALUE!
Nguyên nhân: Lỗi #VALUE trong hàm INDEX và MATCH trong Excel thường xảy ra khi bạn nhập sai kiểu dữ liệu trong các đối số của hàm. Chẳng hạn, trong hàm INDEX, đối số thứ hai yêu cầu kiểu dữ liệu số, nhưng nếu bạn nhập giá trị văn bản (được bao bởi dấu nháy kép), Excel sẽ không thể xử lý đúng.
Cách khắc phục: Để sửa lỗi này, bạn cần kiểm tra kỹ lưỡng xem các đối số trong công thức đã được nhập đúng kiểu dữ liệu chưa. Đảm bảo rằng những giá trị yêu cầu số phải được nhập dưới dạng số, không phải dưới dạng văn bản.
Tổng kết lại, hàm INDEX và MATCH trong Excel là một sự kết hợp hoàn hảo cho những ai cần tra cứu dữ liệu một cách linh hoạt, chính xác và hiệu quả. Dù ban đầu có thể hơi phức tạp so với các hàm khác, nhưng khi đã hiểu rõ và sử dụng thành thạo, bạn sẽ thấy sức mạnh vượt trội của chúng trong việc xử lý các bảng dữ liệu lớn và phức tạp. Hy vọng bài viết này đã giúp bạn nắm vững cách sử dụng cũng như cách khắc phục những lỗi phổ biến để áp dụng hàm INDEX và MATCH trong Excel một cách tự tin hơn.
Xem thêm: Cách Sử Dụng Hàm INT Trong Excel Lấy Giá Trị Phần Nguyên
