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

hàm index và match trong excel là gì?

index và match là các hàm tìm kiếm trong excel. Mặc dù chúng là hai hàm hoàn toàn riêng biệt có thể được sử dụng riêng lẻ nhưng chúng cũng có thể được kết hợp để tạo ra các công thức nâng cao.

Hàm chỉ mục trả về một giá trị hoặc một tham chiếu đến một giá trị trong một lựa chọn được chỉ định. ví dụ: nó có thể được sử dụng để tìm các giá trị trong hàng thứ hai của tập dữ liệu hoặc trong hàng thứ năm và cột thứ ba.

Mặc dù hàm chỉ mục có thể được sử dụng một mình, nhưng việc kết hợp nó với hàm đối sánh trong một công thức sẽ làm cho nó hữu ích hơn. hàm đối sánh tìm kiếm một mục cụ thể trong một phạm vi ô và sau đó trả về vị trí tương đối của mục đó trong phạm vi. chẳng hạn, nó có thể được sử dụng để chỉ định rằng một tên cụ thể là mục nhập thứ ba trong danh sách các tên.

lập chỉ mục và đối sánh các đối số và cú pháp của hàm

Cú pháp và đối số của hai hàm này được hiển thị bên dưới:

ở đâu:

• mảng (đối số bắt buộc): là phạm vi ô mà công thức sẽ sử dụng. có thể là một hoặc nhiều hàng và cột.
• row_num (đối số bắt buộc): là hàng của mảng mà từ đó một giá trị được trả về.
• column_num (đối số tùy chọn): là cột của mảng mà từ đó một giá trị được trả về.

ở đâu:

• lookup_value (đối số bắt buộc): là giá trị bạn muốn đối sánh trong lookup_array. nó có thể là một số, văn bản hoặc giá trị lôgic được nhập theo cách thủ công hoặc được tham chiếu thông qua tham chiếu ô.
• lookup_array (đối số bắt buộc): là phạm vi ô cần xem xét. có thể là một hàng hoặc một cột.
• match_type có thể là -1, 0 hoặc 1. chỉ định cách lookup_value được so sánh với các giá trị trong lookup_array. 1 là giá trị mặc định nếu đối số này bị bỏ qua.
• sử dụng 1 hoặc -1 cho những thời điểm bạn cần chạy các tìm kiếm gần đúng trên thang điểm, chẳng hạn như khi xử lý các con số và khi giá trị gần đúng là ổn. nhưng lưu ý rằng nếu bạn không chỉ định match_type, 1 sẽ mặc định, điều này có thể làm sai lệch kết quả nếu bạn thực sự muốn có một kết quả khớp chính xác.

ví dụ về các hàm chỉ mục và đối sánh

Trước khi xem xét cách kết hợp các hàm đối sánh và chỉ mục trong một công thức, chúng ta cần hiểu cách hoạt động của từng hàm này trong excel.

ví dụ về hàm chỉ mục



Trong ví dụ đầu tiên này, có bốn công thức sử dụng hàm chỉ mục mà chúng ta có thể sử dụng để nhận các giá trị khác nhau:

• = index (a1: b4; 2; 2): hàm sẽ kiểm tra mảng a1: b4 để tìm giá trị ở cột thứ hai và hàng thứ hai là wuhong jade.
• = index (a1: b1; 1): hàm sẽ kiểm tra mảng a1: b1 để tìm giá trị trong cột đầu tiên là lê thanh khương.
• = index (2: 2; 1): hàm sẽ kiểm tra mọi thứ trong hàng thứ hai để tìm giá trị trong cột đầu tiên, đó là nguyen gia vien.
• = index (b1: b2; 1): hàm sẽ kiểm tra mảng b1: b2 để tìm giá trị trong hàng đầu tiên, là thanh.

ví dụ về hàm đối sánh

chúng ta có thể tìm hiểu cách hoạt động của hàm đối sánh trong excel thông qua 4 ví dụ đơn giản sau:

đây là bốn ví dụ đơn giản về hàm đối sánh:



ở đâu:

• = match (“ruby dance”; a2: d2; 0): chức năng match sẽ tìm kiếm ruby ​​dance trong phạm vi a2: d2 > và trả về 3.
• = match (16; d1: d3): hàm match sẽ tìm kiếm 16 trong phạm vi d1: d3 . nhưng vì 16 không nằm trong phạm vi tìm kiếm, kết quả khớp sẽ tìm giá trị lớn nhất tiếp theo nhỏ hơn hoặc bằng 14 , trong trường hợp này là 13, nằm ở vị trí 1 của lookup_array.
• = match (16; d1: d3; -1): tương tự như công thức trước đó, nhưng vì mảng không có thứ tự giảm dần theo yêu cầu của -1, chúng tôi thấy thông báo # n / a.
• = match (13; a1: d1; 0): hàm match sẽ tìm kiếm 13 trong hàng đầu tiên của bảng dữ liệu và trả về 4 vì nó là phần tử thứ tư trong bảng dữ liệu . mảng này.

ví dụ về hàm đối sánh chỉ mục

đây là hai ví dụ mà chúng ta có thể kết hợp chỉ mục và đối sánh trong một công thức:

tìm tham chiếu ô trong bảng



Ví dụ này sẽ lồng công thức hàm đối sánh trong công thức hàm chỉ mục. mục đích là để xác định loại hàng hóa thông qua số lượng quy định. ý nghĩa cụ thể của công thức trên sẽ hoạt động như sau:

• match (f1; b2: b5): hàm match sẽ tìm kiếm giá trị f1 (8795) trong phạm vi dữ liệu b2: b5 . như chúng ta có thể thấy trong bảng, vị trí của giá trị f1 trong phạm vi dữ liệu này là 2 và đó là kết quả mà hàm đối sánh tìm thấy.
• mảng chỉ mục là c2: c5 vì chúng tôi đang tìm kiếm giá trị trong cột đó.
• hàm chỉ mục hiện có thể được viết lại thành index (c2: c5; 2; [column_num]) vì 2 là hàm đối sánh được tìm thấy.
• column_num là tùy chọn, chúng tôi có thể xóa phần đó và giảm công thức thành index (c2: c5; 2).

để sau khi rút gọn, công thức ban đầu trông giống như một công thức có hàm chỉ số bình thường và giá trị của phần tử thứ hai trong phạm vi c2: c5 là một cây bút máy thiên thanh.

tìm kiếm theo tiêu đề hàng và cột



Trong ví dụ về đối sánh và chỉ mục này, chúng tôi sẽ thực hiện tìm kiếm hai chiều. Mục đích là để kiểm tra xem trong tháng 5 đã bán được bao nhiêu chiếc bút tre. giống như ví dụ trước, nhưng với một công thức đối sánh bổ sung được lồng trong chỉ mục.

• match (g1; a2: a13; 0): hàm match sẽ tìm kiếm g1 (số 5) trong phạm vi a2: a13 để lấy một giá trị cụ thể.
• match (g2; b1: e1; 0): là công thức so khớp thứ hai và giống với công thức đầu tiên, nhưng thay vào đó tìm kiếm g2 (cho “bút tre”) trong tiêu đề cột trong tổng số b1: e1 . như trong hình ảnh, đây là cột thứ ba.
• Cuối cùng, các công thức với hàm chỉ mục có thể được viết lại để dễ hiểu hơn: = index (b2: e13; 6; 3). có nghĩa là hàm chỉ mục sẽ tìm kiếm toàn bộ bảng b2: e13 cho hàng thứ sáu và cột thứ ba để trả về 41 như trong bảng trước. > li>
  

  lưu ý khi sử dụng các hàm đối sánh và chỉ mục

  • đối sánh không phân biệt chữ hoa chữ thường, vì vậy chữ hoa và chữ thường được xử lý giống nhau khi đối sánh các giá trị văn bản.
  • so khớp trả về lỗi # n / lỗi vì nhiều lý do, chẳng hạn như: match_type là 0 và lookup_value không được tìm thấy; match_type là -1 và lookup_array không theo thứ tự giảm dần; match_type là 1 và lookup_array không theo thứ tự tăng dần và lookup_array không phải là một hàng hoặc một cột.
  • Bạn có thể sử dụng ký tự đại diện trong đối số lookup_value nếu match_type là 0 và lookup_value là một chuỗi văn bản. dấu hỏi phù hợp với bất kỳ ký tự đơn nào và dấu hoa thị khớp với bất kỳ chuỗi ký tự nào.
  • index sẽ trả về lỗi #ref! nếu row_num và column_num không trỏ đến một ô trong mảng.

  xem thêm:

  • hàm trung gian trong excel là gì? cách sử dụng hàm trung gian trong thực tế
  • hướng dẫn cụ thể cho các hàm countif và countifs trong excel
  • hàm vlookup trong excel là gì? hướng dẫn cách sử dụng hàm vlookup đơn giản và dễ hiểu