Cách sử dụng hàm INDEX và MATCH trong Excel
Hàm INDEX và MATCH trong Excel, đây là cách thay thế Hàm VLOOKUP và HLOOKUP. Là hai công cụ phổ biến trong Excel cho việc dò tìm dữ liệu. 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 VLOOKUP và nằm ở dòng bên dưới đối với HLOOKUP.
Điểm khác nhau giữa hàm INDEX + MATCH và hàm VLOOKUP, HLOOKUP
Trong ví dụ dưới đây, chúng ta sử dụng 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.
Nếu thứ tự của bảng màu xanh đảo ngược, bạn không thể sử dụng VLOOKUP và HLOOKUP nữa.
Do đó, nếu bạn 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ải quyết vấn đề này.
Ngoài ra, các lợi ích khác khi kết hợp sử dụng hàm INDEX và MATCH so với VLOOKUP và HLOOKUP là:
Chèn hoặc xóa cột một cách an toàn: Vì hàm MATCH xác định 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 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 có thể tăng tốc độ xử lý từ 10% đến 15% trong các bảng tính lớn.
Hàm INDEX và hàm MATCH
Hàm INDEX trả về giá trị dựa trên vị trí hàng và cột trong một bảng hoặc phạm vi.
=INDEX(array;row_num;column_num)
Trong đó:
array: vùng ô hoặc một mảng số nào đó;
row_num: chọn hàng trong mảng để trả về giá trị;
column_num: chọn cột trong mảng để trả về giá trị.
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 hoặc phạm vi ô được tìm kiếm;
match_type: kiểu tìm kiếm.
Ví dụ về hàm INDEX kết hợp với hàm MATCH
Bạn có thể tải file Excel của ví dụ dưới đây. Trong các công thức, bạn cần sử dụng dấu $ để cố định giá trị của các phạm vi.
Giả sử chúng ta có đơn giá cho Sản phẩm và Hãng sản xuất trong Bảng 2 (B15:E18). Dựa trên Bảng 2, chúng ta cần điền đơn giá vào Bảng 1 (B3:D12).
Điền đơn giá cho Bảng 1 dựa trên Bảng 2:
Cách sử dụng hàm INDEX kết hợp với hàm MATCH để tìm kiếm giá trị cho ô D4 như sau:
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à 4, tương ứng với hàng số 4 trong Bảng 2.
hàm INDEX kết hợp hàm MATCH
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)
hàm INDEX kết hợp hàm MATCH
Kết quả là 2, tương ứng với cột số 2 trong Bảng 2.
Sử dụng hàm INDEX kết hợp với 2 hàm MATCH ở trên để trả về giá trị dựa trên 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ị tương ứng của hàng 4 (Hàng Mouse) và cột 2 (Cột Samsung) trong Bảng 2, là 5.
hàm INDEX kết hợp hàm MATCH
Sau đó, bạn có thể sao chép công thức này cho các ô khác để hoàn thành.
hàm INDEX kết hợp hàm MATCH
Một số lưu ý khi sử dụng
Như đã đề cập ở phần trước, do hạn chế của hàm VLOOKUP và HLOOKUP, nếu giá trị trả về nằm ở cột bên trái hoặc dòng ở trên so với giá trị tìm kiếm, hai công thức này sẽ không hoạt động. Hàm INDEX kết hợp với hàm MATCH sẽ linh hoạt hơn rất nhiều, không cần quan tâm đến vị trí cụ thể của các cột giá trị trả về.
Trong ví dụ dưới đây, chúng ta có bảng 1 liệt kê thủ đô của các nước và cần điền dữ liệu tương ứng ở bảng 2. Trong trường hợp này, chúng ta không thể sử dụng hàm VLOOKUP, nhưng hàm INDEX kết hợp với hàm MATCH sẽ giải quyết được vấn đề này.
Cách sử dụng hàm INDEX và MATCH trong Excel
