Tìm hiểu cách sử dụng hàm INDEX cũng như hàm kết hợp INDEX và MATCH trong Excel (có Ví dụ).
Hàm INDEX là một trong những hàm được sử dụng nhiều nhất trong Excel được phân loại là Hàm Tra cứu / Tham chiếu. Hàm INDEX được sử dụng để trích xuất giá trị hoặc tham chiếu của ô trong mảng hoặc phạm vi dựa trên số hàng và số cột bạn đã chỉ định. Nó cũng có thể được sử dụng để trích xuất toàn bộ hàng và cột trong bảng.
Ví dụ: chúng ta có một bảng gồm 700 hàng và 100 cột và chúng ta muốn trả về giá trị ở hàng thứ 522 và cột thứ 50. Để làm điều này, bạn không cần phải cuộn xuống hàng trăm hàng để đến giá trị, thay vào đó, bạn có thể sử dụng hàm INDEX để trả về giá trị tại giao điểm của số hàng và cột được chỉ định.
Hàm INDEX thường được sử dụng cùng với hàm MATCH như một sự thay thế mạnh mẽ cho hàm VLOOKUP. Trong bài viết, chúng tôi sẽ giải thích hàm INDEX là gì, cách sử dụng riêng hàm INDEX và cách sử dụng hàm INDEX và MATCH cùng với các ví dụ chi tiết trong Excel.
Xem nhanh
Các format của hàm INDEX
Có hai format của hàm INDEX trong excel: Mảng và Tham chiếu.
Dạng mảng của Hàm INDEX:
Hàm chỉ mục mảng được sử dụng để trả về (các) giá trị thực tế tại giao điểm của hàng và cột được chỉ định trong một phạm vi duy nhất.
Cú pháp :
=INDEX(array, row_num, [column_num])Các đối số của hàm trên:
- array – Điều này chỉ định một mảng hoặc dải ô mà bạn muốn trả về giá trị từ đó.
- row_num – Điều này chỉ định số hàng mà bạn muốn trích xuất giá trị.
- column_num – Điều này chỉ định số cột mà bạn muốn trích xuất giá trị. Nếu nó bị bỏ qua hoặc bằng không, hàm sẽ lấy 1 làm mặc định.
Nếu số hàng không được chỉ định thì số cột là bắt buộc và nếu số cột bị bỏ qua thì số hàng là bắt buộc.
Format tham chiếu của Hàm INDEX
Format Tham chiếu của hàm INDEX được sử dụng để trả về tham chiếu của ô tại giao điểm của số hàng và số cột.
Cú pháp:
=INDEX(reference, row_num, [column_num], [area_num])Hàm sử dụng các đối số dưới đây:
- reference – Đối số này chỉ định tham chiếu đến một hoặc nhiều phạm vi ô (vùng). Nếu nhiều phạm vi hoặc mảng được sử dụng trong một hàm, chúng cần được phân tách bằng dấu phẩy và đóng bằng dấu ngoặc vuông, tức là (A3: B11, B15: D5, E10: K50).
- row_num – Nó biểu thị số hàng trong tham chiếu hoặc mảng được chỉ định.
- [col_num] – Nó biểu thị số cột trong tham chiếu hoặc mảng được chỉ định. Nếu nó bị bỏ qua hoặc bằng không, hàm sẽ lấy 1 làm mặc định.
- [area_num] – Nếu đối số tham chiếu được tạo thành từ nhiều phạm vi hoặc mảng, điều này chỉ định phạm vi hoặc khu vực sẽ sử dụng. Các khu vực được đánh số từ trái sang phải trong đối số tham chiếu. Khu vực đầu tiên được nhập được coi là 1, khu vực thứ hai 2, v.v. Nếu đối số này bị bỏ qua, nó sẽ được mặc định là giá trị 1.
Cách sử dụng hàm INDEX trong Excel
Để hiểu rõ hơn về cách hoạt động của hàm INDEX trong Excel, chúng ta hãy xem xét một vài ví dụ.
Hàm INDEX với mảng một chiều (Array Format)
Hàm INDEX có thể được sử dụng trên mảng Một chiều (tra cứu một chiều) để trả về giá trị từ một hàng hoặc cột. Đối với hàm này, chỉ cần đối số mảng và số hàng hoặc số cột.
Giả sử bạn có tập dữ liệu dưới đây:
Bây giờ, hãy chọn ô mà bạn muốn trả về kết quả của hàm. Sau đó, gõ công thức sau để lấy điểm hàng thứ 10 của Bài thi 1 và nhấn Enter:
=INDEX(C2:C20,9)Trong công thức trên, C2:C20 chỉ định phạm vi hoặc cột và 9 tham chiếu đến số hàng để tìm nạp giá trị từ phạm vi đã cho. Vì chúng tôi đang tìm kiếm giá trị trong một cột duy nhất, nên đối số col_num bị bỏ qua.
Như bạn có thể thấy ở trên, chúng tôi đã loại trừ tiêu đề cột khi chúng tôi chỉ định phạm vi trong công thức, vì vậy chúng tôi có thể bắt đầu đếm hàng từ C2 tức là hàng thứ nhất trong phạm vi đã cho, do đó giá trị ở hàng 9 là 77.
Hàm INDEX với mảng hai chiều (Array Format)
Mảng hai chiều có nghĩa là một mảng có nhiều hơn một hàng và một cột. Để sử dụng hàm mảng trong tra cứu hai chiều, bạn sẽ cần một dải ô, số hàng và / hoặc số cột. Nếu số hàng hoặc số cột bị bỏ qua, nó sẽ được mặc định là 1.
Giả sử chúng ta có tập dữ liệu dưới đây, nơi chúng ta muốn tra cứu và trả về điểm của Laura trong Quiz 2.
Để làm điều đó, hãy chọn ô mà bạn muốn trả về kết quả và thực hiện công thức sau:
=INDEX(A2:G20,9,4)Trong đó A2:G20(tiêu đề cột bị loại trừ) là bảng hoặc phạm vi, 9 là số hàng và 4 là số cột trong công thức. Công thức trên trả về giá trị (92) tại giao điểm của hàng 9 và cột 4 trong bảng A2: G20.
Hàm INDEX chỉ có số hàng hoặc số cột (Array Format)
Nếu một đối số mảng chứa nhiều hàng và cột, chúng ta cần chỉ định cả số hàng và cột để tìm nạp một giá trị cụ thể. Tuy nhiên, nếu số hàng hoặc cột bị để trống hoặc được nhập bằng 0 trong công thức INDEX, thì công thức sẽ trả về một mảng giá trị.
Ví dụ 1:
Hãy thử công thức dưới đây trong đó số cột là 0 hoặc trống:
=INDEX(A2:F20,13,0)hoặc là
=INDEX(A2:F20,13,)Khi một công thức trả về nhiều giá trị, nó được coi là một công thức mảng. Vì vậy, nó phải được thực thi bằng cách nhấn Ctrl+ Shift+ Enter. Vì vậy, hãy nhập công thức trên và nhấn Ctrl+ Shift+ Enter để chạy nó dưới dạng công thức mảng.
Nếu công thức INDEX được nhập dưới dạng công thức mảng trong một ô, nó chỉ trả về giá trị đầu tiên trong hàng hoặc cột được chỉ định như được hiển thị bên dưới. Nếu một công thức được thực thi dưới dạng công thức mảng, thì các dấu ngoặc nhọn sẽ được tự động chèn vào.
Trong trường hợp, số hàng hoặc số cột hoặc cả hai đều được nhập dưới dạng số không hoặc số trống và bạn thực thi công thức như một công thức bình thường bằng cách chỉ cần nhấn Enter, kết quả đầu ra sẽ là lỗi ‘#VALUE!’ .
Ví dụ 2:
Trước khi nhập công thức INDEX dưới dạng công thức mảng, trước tiên, bạn cần chọn phạm vi đầu ra chính xác. Phạm vi đầu ra phải có cùng số ô trống với mảng của tập dữ liệu nguồn. Đây là cách bạn làm điều này:
Để trả về một mảng giá trị dưới dạng đầu ra từ công thức INDEX, trước tiên, hãy chọn phạm vi đầu ra trống A23: G23. Sau đó, nhập công thức =INDEX(A2:F20,13,0)vào ô đầu tiên được chọn (A23) và nhấn Ctrl+ Shift+ Enter đồng thời để thực thi nó dưới dạng công thức mảng.
Như bạn có thể thấy ở trên, toàn bộ hàng được trả về dưới dạng đầu ra trong phạm vi đã chọn.
Ví dụ 3:
Bạn cũng có thể sử dụng các ô để nhập Số hàng và Số cột của mình để nhận kết quả động từ công thức INDEX mà không cần phải thay đổi công thức của bạn mọi lúc để nhận được kết quả đầu ra khác.
Để thực hiện điều này, hãy sử dụng công thức dưới đây:
=INDEX(A2:G20,I5,I6)Phạm vi ở A2:G20, I5 tham chiếu cho công thức biết sử dụng giá trị trong ô đó làm số hàng và I6 tham chiếu đến giá trị trong ô đó dưới dạng số cột để trả về kết quả (86).
Giờ đây, bạn có thể dễ dàng điều chỉnh số hàng hoặc số cột trong ô I5 và I6 tìm nạp một kết quả khác mà không cần thực hiện bất kỳ điều chỉnh nào đối với công thức ban đầu.
Hàm INDEX với nhiều mảng (Reference Form)
INDEX Reference Form có thể được sử dụng để trích xuất giá trị nếu bạn có nhiều mảng hoặc bảng trong công thức. Bạn cũng có thể sử dụng một tham chiếu ô đơn không chỉ một mảng trong công thức. Biểu mẫu Reference Hàm INDEX cũng cần một đối số bổ sung – Số vùng cho biết hàm lấy dữ liệu từ mảng nào.
Giả sử, bạn có ba tập dữ liệu khác nhau chứa thông tin chi tiết về doanh số và lô hàng của các sản phẩm như được hiển thị bên dưới.
Để trích xuất giá trị từ nhiều mảng hai chiều bằng Chỉ mục, hãy nhập công thức bên dưới nơi bạn muốn kết quả và nhấn Enter:
=INDEX((A3:D9,A14:D18,F3:H5),3,4,2)Lần lượt A3:D9, A14:D18, F3:H5 là 3 vùng của đối số tham chiếu và chúng được gọi là Vùng 1, 2 và 3 tương ứng. Số hàng và số cột lần lượt là 3 và 4, từ đó chúng ta cần lấy giá trị. Và đối số cuối cùng 2 chỉ định phạm vi hoặc khu vực sẽ sử dụng.
Kết quả là, công thức INDEX sẽ xuất ra giá trị từ hàng thứ 3 của cột thứ tư từ mảng thứ 2 trong trang tính, tức là ‘7.2’.
Chức năng chỉ mục với các chức năng khác
Hàm Index có thể được sử dụng với nhiều hàm khác để thực hiện các phép tính khác nhau trong Excel.
Ví dụ 1:
Ví dụ: chúng tôi muốn tính tổng các giá trị của cột thứ 3 của bảng A2: C20 với sự trợ giúp của hàm INDEX. Bạn có thể làm điều đó với công thức dưới đây:
=SUM(INDEX(A2:E20,0,3))Ở đây, hàm INDEX được bao trong hàm SUM để tính tổng các giá trị của cột thứ 3.
Ví dụ 2:
Hãy thử hàm INDEX với một công thức trung bình để trả về giá trị trung bình của một dải ô. Theo mặc định, hàm Index trả về giá trị của một ô. Tuy nhiên, khi hàm INDEX đứng sau tham chiếu ô và dấu hai chấm (:), nó sẽ trả về một tham chiếu ô thay thế.
Ví dụ: công thức dưới đây trả về giá trị trung bình của phạm vi ô B2: B6:
=AVERAGE(B2:INDEX(B2:B10,5))Giá INDEX(B2:B10,5)trị trả về một tham chiếu đến ô B6 vì một tham chiếu ô và dấu hai chấm (:) đứng trước hàm. Kết quả là bây giờ chúng ta có công thức AVERAGE (B2: B6) và đầu ra là 75,8.
Nếu bạn thay đổi số hàng trong hàm INDEX, nó sẽ tạo ra kết quả động.
Sử dụng kết hợp INDEX và MATCH cùng nhau
Hàm INDEX hiếm khi được sử dụng một mình. Chúng thường được ghép nối với MATCH để thực hiện các phép tính mạnh mẽ. Khi hàm INDEX được kết hợp với hàm MATCH, nó có thể thực hiện tra cứu bên trái nâng cao. Nhiều người vẫn thích sử dụng hàm VLOOKUP để tra cứu giá trị, vì nó phổ biến hơn và đơn giản hơn nhưng INDEX MATCH linh hoạt hơn và nhanh hơn so với hàm VLOOKUP.
Không giống như hàm Vlookup, nó có thể làm việc với dữ liệu được sắp xếp theo cả chiều dọc và chiều ngang và nó có thể tra cứu các giá trị từ trái sang phải cũng như từ phải sang trái. INDEX MATCH không có giới hạn mảng và giới hạn kích thước giá trị tra cứu. Hơn nữa, INDEX và MATCH sử dụng phạm vi dữ liệu động khi tìm kiếm giá trị.
Hàm MATCH trong Excel
Trước khi chúng tôi kết hợp các hàm INDEX và MATCH, trước tiên, chúng ta hãy xem cách hoạt động của hàm MATCH với một ví dụ.
Hàm MATCH là một hàm tích hợp sẵn trong Excel để tìm kiếm một giá trị cụ thể trong một phạm vi ô và trả về vị trí tương đối của giá trị đó trong phạm vi đã cho.
Cú pháp của hàm MATCH:
=MATCH(lookup_value,lookup_array,[match_type})Ở đâu:
lookup_value – Giá trị bạn muốn tra cứu trong một phạm vi ô hoặc một mảng được chỉ định. Nó có thể là giá trị số, giá trị văn bản, giá trị lôgic hoặc tham chiếu ô có giá trị.
lookup_array – Mảng ô mà bạn đang tìm kiếm một giá trị. Nó phải là một cột hoặc một hàng.
match_type – Đây là một tham số tùy chọn có thể được đặt thành 0,1 hoặc -1 và mặc định là 1.
- 0 tìm kiếm kết quả phù hợp chính xác, khi không tìm thấy kết quả này, trả về lỗi.
- -1 tìm kiếm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value khi mảng tra cứu theo thứ tự tăng dần.
- 1 tìm kiếm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị look_up khi mảng tra cứu theo thứ tự giảm dần.
Thí dụ:
Trong tập dữ liệu dưới đây, chúng tôi muốn tìm vị trí chính xác của tên sản phẩm (Avery 510), hãy xem cách chúng tôi có thể thực hiện điều đó với hàm MATCH.
Để tìm vị trí của giá trị trong một hàng hoặc cột, hãy thử công thức dưới đây:
=MATCH("Avery 510",A2:A19,0)Trong công thức trên, Avery 510 là giá trị tra cứu mà chúng ta muốn tìm vị trí, A2:A19 là nơi cần tìm giá trị và 0 yêu cầu hàm tìm kiếm giá trị chính xác. Kết quả là, công thức trả về vị trí của giá trị là ‘8’ trong phạm vi đã cho.
Hàm MATCH không phân biệt chữ hoa chữ thường, không quan trọng giá trị tra cứu là chữ thường hay chữ hoa, nó sẽ tìm ra giá trị chính xác. Nếu giá trị tra cứu là một chuỗi, nó phải được đặt trong dấu ngoặc kép.
Bạn cũng có thể sử dụng các ô để nhập giá trị tra cứu của mình để nhận kết quả động mà không cần phải thay đổi công thức mỗi khi bạn muốn có một kết quả khác. Đối với điều đó, hãy sử dụng tham chiếu ô có chứa giá trị tra cứu trong đối số đầu tiên.
Công thức sau đây tìm vị trí của giá trị trong ô G2:
=MATCH(G2,A2:A19,0)
Kết hợp hàm INDEX và MATCH
VLOOKUP chỉ có thể tra cứu một giá trị theo chiều dọc, tức là các cột trong khi tổ hợp INDEX MATCH có thể thực hiện cả tra cứu theo chiều dọc và chiều ngang.
Hàm INDEX được sử dụng để tìm nạp một giá trị tại một vị trí cụ thể trong một bảng hoặc một dải ô trong khi hàm MATCH trả về vị trí tương đối của một giá trị trong một cột hoặc một hàng. Khi được kết hợp, MATCH có thể tìm số hàng hoặc cột (vị trí) của một giá trị cụ thể và hàm INDEX có thể truy xuất giá trị dựa trên số hàng hoặc cột đó.
Thí dụ:
Giả sử, chúng ta có tập dữ liệu dưới đây như hình dưới đây:
Trong bảng trên, giả sử bạn muốn truy xuất điểm ‘Toán’ (bảng trên là Maths) của sinh viên tên ‘Tyisha’, hãy thử công thức dưới đây:
=INDEX(B2:G22,MATCH("Tyisha",A2:A22,0),4)Trong công thức trên, hàm MATCH lồng nhau tìm số hàng (vị trí) của giá trị ‘Tyisha’ (5). Sau đó, số hàng và số cột (4), mà chúng tôi đã chỉ định làm đối số cuối cùng được cung cấp cho hàm INDEX. Kết quả là, công thức truy xuất các giá trị tại giao điểm của số hàng (5) và cột (4) trong bảng B2: G22 và trả về điểm số ’65’.
Bạn cũng có thể sử dụng tham chiếu ô làm đối số cho giá trị tra cứu trong công thức:
=INDEX(B2:G22,MATCH(I4,A2:A22,0),4)
Tra cứu hai chiều với INDEX và MATCH
Trong ví dụ trên, chúng ta biết số cột (được nhập thủ công) và chúng ta chỉ sử dụng hàm MATCH để tìm số hàng để lấy dữ liệu. Nhưng giả sử, chúng ta cũng không có số cột và chúng ta chỉ biết hàng (tên sinh viên) và tiêu đề cột (môn học) để sử dụng. Trong trường hợp đó, bạn có thể sử dụng hàm INDEX và MATCH để tìm một giá trị bằng cách tra cứu hai chiều (mảng hai chiều).
Đối với điều này, chúng ta cần lồng hai hàm MATCH trong hàm INDEX: Một cho số hàng và một cho số cột.
Ví dụ 1:
Bây giờ, chúng ta muốn tìm hiểu xem ‘Lura’ đã ghi được bao nhiêu điểm trong môn học ‘Lịch sử’ (trong bảng tiếng anh là History). Đây là cách chúng tôi có thể làm điều đó:
Để áp dụng tra cứu hai chiều với INDEX và MATCH, bạn có thể sử dụng công thức dưới đây:
=INDEX(B2:G22,MATCH("Lura",A2:A22,0),MATCH("History",B1:G1,0))Như chúng ta đã biết, hàm MATCH có thể tìm kiếm một giá trị theo cả chiều ngang và chiều dọc. Trong công thức này:
- MATCH (“Lura”, A2: A22,0) tìm kiếm giá trị ‘Lura’ theo chiều dọc trong phạm vi A2: A22 và trả về vị trí của nó, là số hàng (9).
- MATCH (“Lịch sử”, B1: G1,0) tìm kiếm giá trị ‘Lịch sử’ theo chiều ngang trong phạm vi B1: G1 và trả về vị trí của nó, là số cột (4).
Sau đó, cả số hàng và số cột được cung cấp cho hàm INDEX để lấy điểm (86) tại giao điểm trong bảng B2: G22.
Tra cứu hai chiều động với INDEX và MATCH
Nếu bạn có dữ liệu lớn, bạn có thể muốn làm cho các giá trị tra cứu động trong hàm MATCH, vì vậy bạn không phải nhập các giá trị tra cứu trong hàm theo cách thủ công. Bạn có thể chỉ cần cập nhật các giá trị tra cứu trong các ô cụ thể để các hàm MATCH có thể tự động xác định các giá trị trong các ô được chỉ định và trả về vị trí chính xác (số hàng và cột).
Đây là công thức để tra cứu hai chiều hoàn toàn động với INDEX và MATCH:
=INDEX(B2:G22,MATCH(I3,A2:A22,0),MATCH(I4,B1:G1,0))Trong công thức trên, MATCH(I3,A2:A22,0) trả về một số hàng động bằng cách xác định giá trị trong ô I3 và tìm kiếm nó trong phạm vi A2: A22. MATCH (I4, B1: G1,0) trả về một số cột động bằng cách xác định giá trị trong ô I4và tìm kiếm nó trong phạm vi B1:G1. Sau đó, cả hai giá trị được cung cấp cho hàm INDEX để lấy điểm của Yuette trong môn Hóa học: 90.
Bạn có thể thay đổi tên sinh viên và / hoặc chủ đề trong các ô ‘I3’ và ‘I4’ để nhận được các kết quả động khác nhau.
Ví dụ 2 :
Nếu bạn muốn nhận các giá trị từ toàn bộ một hàng hoặc cột thay vì một giá trị ô đơn lẻ. Ví dụ: chúng ta có thể sử dụng công thức INDEX MATCH để lấy tất cả điểm của ‘Yuette’ bao gồm điểm của tất cả các môn học, tổng số và điểm của chúng.
Để làm điều đó, hãy thử công thức dưới đây:
=INDEX(B2:G22,MATCH(I3,A2:A22,0),0)Đầu tiên, chọn phạm vi đầu ra có nhiều ô như trong phạm vi nguồn, nhập công thức và nhấn Ctrl+ Shift+ Enter . Ở đây, chúng tôi đã sử dụng 0 làm số cột. Kết quả là, chúng tôi nhận được một mảng kết quả như hình dưới đây.
Nếu bạn muốn xem mảng xuất ra công thức, chỉ cần chọn công thức trong chế độ chỉnh sửa và nhấn F9.
Giả sử chúng ta không có tổng điểm trong bảng trên, chúng ta có thể nhận tổng điểm của ‘Yeutte’ trong cả bốn môn học bằng cách lồng công thức trên vào trong một hàm SUM.
Đây là điểm của tất cả học sinh trong bốn môn học.
Bây giờ, hãy nhập công thức dưới đây và chạy nó dưới dạng công thức mảng ( Ctrl+ Shift+ Enter):
=SUM(INDEX(B2:E17,MATCH(G5,A2:A17,0),0))Ở đây, các hàm INDEX và MATCH trả về điểm trong cả bốn môn học cho Yeutte và hàm SUM tổng hợp chúng lại và cho ra tổng số là 378.
Tương tự, bạn có thể sử dụng hàm MAX với INDEX để tính điểm cao nhất và hàm MIN để tìm ra điểm thấp nhất.
Tạo danh sách thả xuống để nhập giá trị cho công thức INDEX và MATCH
Trong các ví dụ trên, chúng tôi phải mã hóa giá trị tra cứu trong hàm MATCH hoặc nhập giá trị tra cứu vào các ô được chỉ định để làm cho công thức động. Tuy nhiên, nếu bạn có một tập dữ liệu lớn, đây có thể là một quá trình tốn thời gian và dễ xảy ra lỗi (nếu bạn viết sai chính tả hoặc chỉ định giá trị sai).
Bạn có thể dễ dàng tránh điều này bằng cách tạo danh sách thả xuống gồm các giá trị tra cứu (Tên và môn học của sinh viên) và chọn một giá trị từ danh sách thả xuống. Sau khi chọn một giá trị, công thức sẽ tự động cập nhật đầu ra. Đây là cách bạn làm điều này:
Đầu tiên, hãy chọn ô mà bạn muốn tạo menu thả xuống. Ở đây, chúng tôi muốn danh sách thả xuống tên học sinh trong ‘I3’. Sau đó, chuyển đến tab ‘Data’ và nhấp vào nút ‘Data Validation’ trong nhóm Data Tools.
Trong hộp thoại Data Validation, hãy chuyển đến tab Settings và chọn ‘List’ từ menu thả xuống ‘Allow:’.
Tiếp theo, nhấp vào trường ‘Source’ và chọn phạm vi có tên Student: ‘$ A $ 2: $ A $ 22’.
Sau đó, đảm bảo rằng cả hai tùy chọn ‘Ignore blank’ và ‘In-cell dropdown’ đều được chọn và nhấp vào ‘OK’.
Thao tác này sẽ tạo một danh sách thả xuống trong ô đã chọn như hình dưới đây. Hãy nhớ rằng, menu thả xuống sẽ chỉ hiển thị khi bạn chọn ô đó. Bạn có thể chọn ô và nhấp vào nút thả xuống để xem danh sách tên.
Bây giờ, lặp lại các bước tương tự để tạo danh sách thả xuống cho các chủ đề. Để làm điều đó, hãy chọn một ô mà bạn muốn có danh sách thả xuống ‘Subjects’. Tiếp theo, chuyển đến tab ‘Data’ và nhấp vào nút ‘Data Validation’ trong nhóm Data Tools.
Từ hộp thoại Data Validation, hãy chọn tùy chọn ‘List’ từ menu thả xuống ở mục ‘Allow:’ trong tab Cài đặt. Sau đó, nhấp vào trường Source và chọn phạm vi có chủ thể ($ B $ 1: $ E $ 1). Sau đó, nhấp vào ‘OK’.
Thao tác này sẽ tạo một danh sách thả xuống cho các đối tượng trong ô đã chọn.
Bây giờ, hãy chọn tên và môn học của sinh viên từ các danh sách thả xuống đó.
Sau đó, bạn cần nhập công thức nhận giá trị đầu vào từ danh sách thả xuống. Đối với điều đó, hãy thử công thức dưới đây, gần giống với công thức chúng tôi đã sử dụng trong ví dụ tra cứu hai chiều Động:
=INDEX(B2:E22,MATCH(J3,A2:A22,0),MATCH(J4,B1:E1,0))
Bây giờ, bạn không cần phải mã hóa các giá trị tra cứu vào công thức hoặc trong các ô đầu vào, bạn có thể chỉ cần chọn tên và chủ đề từ danh sách thả xuống và nó sẽ tự động cập nhật kết quả.
Tra cứu ba chiều với công thức INDEX / MATCH (Reference form)
Bạn cũng có thể sử dụng công thức INDEX và MATCH để truy xuất một giá trị bằng cách tra cứu ba chiều. Trong ví dụ trên, chúng tôi đã sử dụng tra cứu động hai chiều (hai giá trị tra cứu) để lấy điểm của học sinh từ một bảng.
Trong các ví dụ trên, chúng tôi đã sử dụng một bảng với điểm số cho các học sinh ở các môn học khác nhau. Đây là một ví dụ về tra cứu hai chiều vì chúng tôi sử dụng hai biến để lấy điểm (tên học sinh và môn học).
Thí dụ:
Tuy nhiên, nếu bạn muốn bạn muốn lấy điểm của sinh viên trong một môn học nhất định từ một học kỳ – Semester cụ thể thì sao? Ví dụ, giả sử bạn có ba bảng (mỗi bảng cho một Semester khác nhau) với danh sách các sinh viên và điểm số của họ ở các môn học khác nhau.
Giờ đây, bạn có thể sử dụng kết hợp INDEX và MATCH để lấy điểm của học sinh cho một môn học cụ thể từ Semester cụ thể bằng cách sử dụng tra cứu ba chiều. Để tra cứu ba chiều, bạn cần ba đầu vào, số hàng (tên học sinh), số cột (môn học) và số khu vực (Semester).
Đầu tiên, chúng ta cần tạo ba menu thả xuống, một menu cho tên sinh viên, một menu khác cho các môn học và một menu khác cho các Semester. Tạo trình đơn thả xuống sinh viên và đối tượng, giống như cách bạn đã làm cho ví dụ trước. Đối với danh sách Semester thả xuống, bạn cần nhập tên Semester vào một phạm vi trống và sử dụng tên đó để tạo danh sách thả xuống.
Để làm điều đó, hãy nhập tên Semester vào một dải ô, sau đó chọn ô mà bạn muốn menu thả xuống và đi tới Data –> Data Tools –> Data Validation.
Trong hộp thoại Data Validation, chọn List cho các Validation criteria, chọn phạm vi có tên Semester và nhấp vào ‘OK’
Bây giờ, trình đơn thả xuống Semester được tạo.
Tương tự, tạo danh sách thả xuống cho Tên và môn học của Sinh viên. Sau đó, chọn một ô mà bạn muốn đầu ra và nhập công thức sau:
=INDEX((B3:E11,B15:E23,J3:M9),MATCH(J16,A3:A11,0),MATCH(J17,B2:E2,0),IF(J15="Semester 1",1,IF(J15="Semester 2",2,3)))Vì đó là một công thức lớn, hãy để chúng tôi chia nhỏ cho bạn:
(B3:E11,B15:E23,J3:M9)là tham chiếu cho các mảng (bảng cho mỗi Semester). Khi chỉ định nhiều mảng, chúng phải được đặt trong dấu ngoặc đơn.MATCH(J16,A3:A11,0)là công thức MATCH được sử dụng để tìm số hàng của tên học sinh từ danh sách thả xuống trong ô J16.MATCH(J15,B2:E2,0)là công thức MATCH được sử dụng để tìm số cột của chủ đề từ danh sách thả xuống trong ô J17.IF(J15="Semester 1",1,IF(J15="Semester 2",2,3)))là đối số số vùng thông báo cho hàm INDEX bảng nào cần tìm kiếm giá trị. Nếu ‘Semester 1’ được chọn trong danh sách thả xuống, nó sẽ trả về 1. Nếu sai, nó sẽ kiểm tra ‘Semester 2’ và trả về 2 và nếu điều đó cũng sai, nó sẽ trả về 3.
Sau đó, chọn Semester, sinh viên và môn học từ menu thả xuống để lấy điểm của sinh viên cụ thể cho một môn học cụ thể trong Semester đã chọn.
Kết quả:
Bây giờ, bất cứ khi nào bạn chọn một tùy chọn khác trong menu thả xuống, đầu ra sẽ tự động điều chỉnh.
Tra cứu bên trái bằng INDEX và MATCH
Không giống như hàm Vlookup, hàm INDEX và MATCH có thể nhìn sang bên trái và trích xuất giá trị từ bên trái của cột có giá trị tra cứu.
Ví dụ, trong tập dữ liệu dưới đây, chúng tôi có danh sách các ngọn núi, chiều cao của chúng và vị trí của chúng. Bây giờ, chúng tôi có vị trí làm giá trị tra cứu và chúng tôi muốn tìm hiểu ngọn núi nằm ở đó. Đây là cách bạn có thể làm điều đó với kết hợp INDEX MATCH bằng cách nhìn từ phải sang trái.
Chọn ô mà bạn muốn đầu ra và nhập công thức dưới đây:
=INDEX(A2:C22,MATCH(F5,C2:C22,0),1)Trong công thức trên, hàm MATCH tìm kiếm giá trị tra cứu (F5) trong phạm vi C2: C22 và khi tìm thấy một kết quả phù hợp, nó sẽ truy xuất giá trị tương ứng trong cột 1 bên trái.
Tra cứu phân biệt chữ hoa chữ thường với INDEX và MATCH
Theo mặc định, kết hợp INDEX và MATCH không phân biệt chữ hoa chữ thường. Giá trị tra cứu không quan trọng là trường hợp nào, nó trả về cùng một kết quả. Ví dụ: nếu bạn tìm kiếm ‘Dexter’, ‘dexter’ hoặc ‘dEXteR’, tất cả các giá trị tra cứu sẽ trả về cùng một số vị trí.
Tuy nhiên, nếu bạn có danh sách điểm kiểm tra của học sinh và nhiều học sinh trùng tên, bạn có thể sử dụng hàm EXACT trong hàm INDEX để phân biệt các ký tự viết hoa và viết thường.
Để thực hiện tra cứu phân biệt chữ hoa chữ thường bằng INDEX và MATCH, hãy sử dụng công thức dưới đây:
=INDEX(B2:E22,MATCH(TRUE,EXACT(J5,A2:A22),0),2)Đây là công thức mảng, vì vậy hãy nhập công thức trên và nhấn Ctrl+ Shift+ Enternếu không bạn sẽ nhận được N / A! lỗi.
Bảng dưới đây chứa ba Evan (Evan, chẵn, EVAN). Hàm Chính xác kiểm tra từng giá trị trong phạm vi A2: A22 với giá trị tra cứu trong ô J5 (EVAN) để tìm khớp chính xác. Khi một kết hợp chính xác được tìm thấy, nó trả về TRUE và hàm MATCH trả về vị trí đó dưới dạng số hàng (13). Và số cột được chỉ định trong đối số cuối cùng là ‘2’. Kết quả là, chúng tôi nhận được điểm EVAN trong hóa học là 59.
Mặc dù chúng tôi đã sử dụng tham chiếu ô cho giá trị tra cứu, bạn cũng có thể mã hóa giá trị tra cứu trực tiếp vào công thức.
Tra cứu nhiều tiêu chí với INDEX và MATCH
Với các hàm INDEX và MATCH, bạn có thể thực hiện tra cứu dựa trên nhiều tiêu chí. Trong phương pháp này, để tra cứu một giá trị, hàm MATCH phải đáp ứng tiêu chí trong 2 hoặc nhiều cột cùng một lúc.
Ví dụ: nếu bạn có một cơ sở dữ liệu lớn và bạn muốn tra cứu tên ‘John’. Nhưng vấn đề là nhiều bản ghi có cùng tên ‘John’. Điều này có thể gây ra sự cố vì bạn có thể tìm nạp sai giá trị. Để khắc phục điều này, bạn có thể sử dụng hàm INDEX và MATCH có thể tra cứu giá trị khớp trên nhiều cột (nhiều tiêu chí) và truy xuất giá trị tương ứng.
Cú pháp cho INDEX MATCH với nhiều tiêu chí:
=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))ở đâu,
- return_range là phạm vi mà từ đó giá trị sẽ được tìm nạp
- tiêu chí1, tiêu chí2,… là những tiêu chí cần được đáp ứng
- range1, range2,… là các phạm vi mà các tiêu chí bắt buộc cần được kiểm tra.
Thí dụ:
Trong tập dữ liệu dưới đây, chúng tôi muốn truy xuất mức lương cho ‘Anne’ nhưng chúng tôi không thể tìm kiếm ‘Anne’ vì có ba bản ghi khác nhau với tên đầu tiên là ‘Anne’.
Công thức dưới đây tra cứu và trả về tiền lương cho bản ghi có họ ‘Anne’ và họ ‘Scotland’ (nhiều tiêu chí):
=INDEX(C2:C20,MATCH(1,(F4=A2:A20)*(F5=B2:B20),0))Đây là một công thức mảng, vì vậy hãy đảm bảo chạy nó bằng cách nhấn Ctrl+ Shift+ Enter.
Ở đây, hàm MATCH kiểm tra tên đầu tiên ‘Anne’ (F4) trong cột A2: A20 và họ ‘Scotland’ (F5) trong cột B2: B20. Nếu cả hai cột khớp với các giá trị tra cứu được chỉ định, nó sẽ trả về mức lương tương ứng từ cột C2: C20.
Tìm kết quả phù hợp nhất với INDEX và MATCH
Việc tìm kiếm giá trị chính xác với INDEX và MATCH rất dễ dàng nhưng việc tìm một giá trị phù hợp nhất với một giá trị cụ thể trong phạm vi hơi phức tạp và nó liên quan đến hai hàm khác ABS và MIN cùng với INDEX và MATCH.
Ví dụ: nếu bạn có danh sách các sự kiện và ngày của chúng trong bảng và bạn muốn tìm một sự kiện gần nhất với một dữ liệu cụ thể, bạn có thể sử dụng ví dụ sau.
Để tìm kết quả phù hợp nhất với một giá trị cụ thể trong một cột, hãy sử dụng cú pháp công thức dưới đây:
{=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0))}Ở đâu
datalà phạm vi giá trị đầu vào.valuelà giá trị đầu vào mà đầu ra sẽ gần nhất.MINhàm được sử dụng để trả về giá trị nhỏ nhất từ mảng giá trị dữ liệu.ABStrả về giá trị tuyệt đối của một số.1(chính xác hoặc nhỏ nhất tiếp theo) hoặc0(khớp chính xác) hoặc-1(chính xác hoặc lớn nhất tiếp theo)
Thí dụ:
Giả sử bạn có tập dữ liệu dưới đây và bạn muốn tìm ngày gần nhất với ngày đã cho và lấy tên thành phố tương ứng.
Để tìm ngày gần nhất với ngày 25 tháng 3 năm 2020 trong phạm vi, hãy thử công thức dưới đây:
=INDEX(A2:A23,MATCH(MIN(ABS(B2:B23-G6)),ABS(B2:B23-G6),0))Sau đó, nhấn Ctrl+ Shift+ Enter để thực thi công thức dưới dạng công thức mảng.
Trong công thức trên, hãy ABS(B2:B23-G6)tìm sự khác biệt giữa mảng tìm kiếm (B2: B23) và ngày đã cho bằng cách trừ tất cả các giá trị ngày cho ngày được chỉ định trong ô G6. Điều này tạo ra một mảng được trả về với các giá trị bị trừ. Sau đó, MIN(ABS(B2:B23-G6)tìm giá trị đóng từ các giá trị bị trừ. Sau đó, hàm MATCH tìm vị trí của giá trị nhỏ nhất đã đóng trong hằng số mảng và số vị trí đó được trả về hàm INDEX dưới dạng số hàng.
Kết quả là, hàm INDEX trả về tên thành phố tương ứng cho ngày sắp xếp.
Đối sánh ký tự đại diện với INDEX và MATCH
Các ký tự đại diện có thể được sử dụng trong tổ hợp hàm INDEX và hàm MATCH có thể được sử dụng để tìm một giá trị có khớp một phần. Nó chỉ có thể hoạt động khi match_type được đặt thành ‘0’ và giá trị tra cứu là một chuỗi văn bản trong hàm MATCH. Có những ký tự đại diện bạn có thể sử dụng trong hàm MATCH: dấu hoa thị (*) và dấu chấm hỏi (?).
- Dấu hỏi (?) Được sử dụng để so khớp bất kỳ ký tự hoặc chữ cái đơn lẻ nào với chuỗi văn bản.
- Dấu hoa thị (*) được sử dụng để so khớp bất kỳ số ký tự nào với chuỗi.
Ví dụ: bên dưới là tập dữ liệu về các sản phẩm, số lượng và giá cả của chúng.
Giả sử bạn muốn lấy giá cho một sản phẩm cụ thể nhưng bạn chỉ biết tên một phần của sản phẩm. Bạn vẫn có thể lấy đúng dữ liệu bằng cách sử dụng ký tự đại diện có giá trị tra cứu. Bạn có thể sử dụng ký tự đại diện dấu hoa thị (*) để khớp với bất kỳ số ký tự nào trong khi dấu chấm hỏi (?) Được sử dụng để khớp với bất kỳ ký tự đơn lẻ nào.
Dấu hoa thị (*)
Bạn có thể thử công thức dưới đây để đạt được điều này:
=INDEX(C2:C19,MATCH("Canon*",A2:A19,0),1)hoặc là
=INDEX(C2:C19,MATCH(F4&"*",A2:A19,0),1)Trong đó hàm MATCH tìm kiếm giá trị bắt đầu bằng ‘Canon’ theo sau là bất kỳ ký tự nào, trong cột Sản phẩm (A2: A19). Điều này có nghĩa là “Canon * trong đối số tra cứu có nghĩa là bất kỳ chuỗi văn bản nào bắt đầu bằng từ ‘Canon’ và có thể có bất kỳ số ký tự nào sau nó.
Trong bảng dưới đây, chúng tôi có ‘Canon PC940 Copier’ trong phạm vi, vì vậy nó trả về số hàng (12) cho hàm INDEX. Sau đó, hàm INDEX lấy giá trị từ hàng thứ 12 trong Phạm vi giá (C2: C19).
Dấu chấm hỏi (?)
Bạn có thể sử dụng ký tự đại diện dấu chấm hỏi (?) Để đại diện cho bất kỳ ký tự bị thiếu nào trong chuỗi.
Để tìm một giá trị khớp với chuỗi văn bản với bất kỳ ký tự đơn nào (ở vị trí ký tự đại diện):
=INDEX(C2:C19,MATCH("Xerox 19?0",A2:A19,0),1)Ở đây, công thức MATCH tìm kiếm Xerox 19?0 trong phạm vi A2: A19 và trả về vị trí của ‘Xerox 1980’, vì nó có chuỗi với ‘8’ ở vị trí của ký tự đại diện. Vị trí của chuỗi là 3 được chuyển đến hàm INDEX để tìm nạp giá (44,28) từ phạm vi C2: C19.
