Kết hợp hàm INDEX và MATCH giúp ta dễ dàng tìm kiếm và truy xuất dữ liệu theo nhiều điều kiện khác nhau, giúp tiết kiệm thời gian và tăng hiệu quả công việc trong Excel. Việc kết hợp giữa hàm INDEX và hàm MATCH sẽ mang lại tính linh hoạt cao hơn trong việc tìm kiếm dữ liệu so với các hàm khác trong Excel. Trong bài viết này, Tin học văn phòng PRO sẽ hướng dẫn bạn cách sử dụng hai hàm này cùng nhau trong Excel để đạt được hiệu quả tối đa.
Ví dụ trong bài được thực hiện trên phiên bản Excel 2016. Bạn có thể áp dụng tương tự trên các phiên bản Excel khác như: 2007, 2010, 2013, 2017 và Microsoft Excel 365.
Điểm khác biệt giữa hàm INDEX + MATCH và hàm VLOOKUP, HLOOKUP
Hàm VLOOKUP, HLOOKUP là hai công cụ tìm kiếm dữ liệu phổ biến và được sử dụng rộng rãi. Tuy nhiên, chúng có một hạn chế là giá trị trả về phải nằm ở cột bên phải đối với hàm VLOOKUP và ở cột bên dưới đối với hàm HLOOKUP.
Trong ví dụ dưới đây, ta có thể sử dụng hàm VLOOKUP và HLOOKUP để tìm kiếm giá trị cho cột Tên hãng và hàng Tên hãng trong hai bảng màu vàng từ hai bảng dữ liệu màu xanh.
Tuy nhiên, nếu thứ tự bảng màu xanh bị đảo ngược, bạn sẽ không thể sử dụng hàm VLOOKUP và HLOOKUP nữa.
Vì vậy, khi cần tìm kiếm giá trị theo chiều ngược lại, hàm INDEX kết hợp với hàm MATCH sẽ giúp bạn giải quyết vấn đề này.
Ngoài ra, việc sử dụng hàm INDEX kết hợp với hàm MATCH còn có nhiều lợi ích khác so với hàm VLOOKUP và HLOOKUP như:
- An toàn khi chèn hoặc xóa cột: Vì hàm MATCH giúp xác định rõ cột chứa giá trị cần tìm một cách trực tiếp.
- Không giới hạn kích thước dữ liệu cần tìm: Với hàm VLOOKUP và HLOOKUP, giá trị cần tìm không được vượt quá 255 ký tự.
- Tốc độ xử lý nhanh hơn: Việc sử dụng hàm INDEX kết hợp với hàm MATCH sẽ tăng tốc độ xử lý từ 10% đến 15% trong các bảng tính lớn.
Xem thêm: Hàm SUMIF
Hàm INDEX và hàm MATCH trong Excel
Công thức hàm INDEX trong Excel
Hàm INDEX trả về giá trị theo vị trí của hàng và cột trong một bảng hoặc một phạm vi.
=INDEX(array;row_num;column_num)
Trong đó:
- array: vùng ô hoặc một hàng số mảng nào đó;
- row_num: chọn hàng trong mảng từ đó trả về một giá trị;
- column: chọn cột trong mảng từ đó trả về một giá trị.
Công thức hàm MATCH trong Excel
Hàm MATCH trả về vị trí tương đối của một giá trị trong một phạm vi.
=MATCH(lookup_value,lookup_array,match_type)
Trong đó:
- Lookup_value: giá trị tìm kiếm trong mảng Lookup_array.
- Lookup_array: mảng hay phạm vị ô được tìm kiếm.
- Match_type: kiểu tìm kiếm.
Xem thêm
Ví dụ hàm INDEX kết hợp với hàm MATCH trong Excel
Bạn có thể tải file Excel của ví dụ bên dưới tại đây. Trong các công thức, bạn cần sử dụng $ để cố định giá trị các phạm vi.
Giả sử chúng ta có bảng giá theo Sản phẩm và Hãng sản xuất trong Bảng 2 (B15:E18). Dựa vào bảng 2, chúng ta cần điền giá vào Bảng 1 (B3:D12).
Cách sử dụng hàm INDEX kết hợp với hàm MATCH để tìm giá trị cho ô D4 như sau:
1. Sử dụng hàm MATCH để xác định vị trí hàng tương ứng của sản phẩm CDRom trong phạm vi B15:B18 của Bảng 2:
=MATCH(B4,$B$15:$B$18,0)
Kết quả là số 4, tương ứng với hàng số 4 trong Bảng 2.
2. Sử dụng hàm MATCH để xác định vị trí cột tương ứng của hãng sản xuất Samsung trong phạm vi B15:E15:
=MATCH(C4,$B$15:$E$15,0)
Kết quả là số 2, tương ứng với cột số 2 trong Bảng 2.
3. Sử dụng hàm INDEX kết hợp với 2 hàm MATCH ở trên để trả về giá trị dựa vào hàng và cột trong Bảng 2:
=INDEX($B$15:$E$18,MATCH(B4,$B$15:$B$18,0),MATCH(C4,$B$15:$E$15,0))
Kết quả trả về là giá trị của hàng 4 (Hàng Mouse), cột 2 (Cột Samsung) trong Bảng 2 là 5.
Sau đó, chúng ta có thể sao chép công thức cho các ô khác để hoàn thành bảng tính.
Những điều cần nhớ khi sử dụng
Như đã đề cập ở phần trước, do hàm VLOOKUP và HLOOKUP có những hạn chế riêng của chúng, nên khi giá trị trả về nằm ở cột bên trái hoặc cột phía trên so với giá trị tìm kiếm, hai công thức này sẽ không hoạt động. Tuy nhiên, việc kết hợp hàm INDEX và MATCH sẽ mang lại tính linh hoạt cao hơn, không quan tâm đến vị trí các cột giá trị trả về.
Trong ví dụ dưới đây, chúng ta có một bảng liệt kê các thủ đô của các nước và cần điền dữ liệu tương ứng vào bảng khác. Trong trường hợp này, không thể sử dụng hàm VLOOKUP, nhưng việc kết hợp hàm INDEX và MATCH sẽ giải quyết được vấn đề này.
=INDEX($B$3:$C$12,MATCH(E4,$C$3:$C$12,0),1)
Các lỗi thường gặp
Trong quá trình sử dụng hàm INDEX kết hợp với hàm MATCH, bạn có thể gặp phải hai loại lỗi là #NA và #VALUE. Những nguyên nhân thường gây ra các lỗi này bao gồm:
Lỗi #NA
- Khi hàm MATCH không tìm thấy giá trị trong phạm vi được chỉ định, nó sẽ trả về kết quả là #N/A.
- Nếu bạn sử dụng một phạm vi trong hàm INDEX hoặc MATCH thay vì một giá trị cụ thể, bạn cần nhấn tổ hợp phím Ctrl+Shift+Enter để chuyển sang công thức mảng.
- Để sử dụng hàm MATCH, bạn cần đảm bảo rằng giá trị trong đối số match_type và thứ tự sắp xếp các giá trị trong phạm vi tìm kiếm là nhất quán, nếu không sẽ gặp lỗi #N/A.
Lỗi #VALUE
Nếu bạn sử dụng hàm INDEX như một công thức mảng để tìm kiếm một giá trị phù hợp, bạn cần chuyển đổi công thức thành công thức mảng bằng cách nhấn tổ hợp phím Ctrl+Shift+Enter, nếu không sẽ xuất hiện lỗi #VALUE!.
Đó là những chia sẻ về cách sử dụng hàm INDEX kết hợp với hàm MATCH trong Excel. Hy vọng những thông tin này sẽ giúp bạn hiểu rõ hơn và áp dụng thành công hai hàm này trong công việc.
