Các hàm INDEX và MATCH trong Google Trang tính, khi được sử dụng riêng, có thể bị hạn chế sử dụng. Tuy nhiên, khi kết hợp với nhau, chúng có thể khá mạnh. Trên thực tế, khi chúng ở cùng nhau, chúng có thể cung cấp một giải pháp thay thế tuyệt vời (và thậm chí tốt hơn) cho tính năng VLOOKUP.
Trong hướng dẫn này, chúng ta sẽ thảo luận về các hàm MATCH và INDEX của Google Trang tính, những gì mỗi hàm hoạt động riêng lẻ và cách chúng hoạt động cùng nhau. Chúng ta cũng sẽ thảo luận về lý do tại sao chúng ta nói kết hợp tổng thể này thực sự tốt hơn so với VLOOKUP.
Hàm MATCH
Hàm MATCH trong Google Trang tính cung cấp vị trí tương đối hoặc “chỉ mục” của một phần tử trong một phạm vi ô. Nó nhận một phạm vi ô và một giá trị và trả về vị trí của giá trị đó trong phạm vi ô.
Cú pháp của hàm MATCH như sau:
= MATCH (khóa_ tìm kiếm, phạm vi, kiểu_tìm kiếm)
Đây,
- khóa_tìm_kiếm là phần tử chúng tôi muốn đối sánh. Đây có thể là giá trị văn bản hoặc số, tham chiếu ô hoặc công thức.
- range là dải ô mà chúng ta muốn tìm phần tử khớp với khóa_ tìm kiếm.
- search_type là một tham số tùy chọn. Nó xác định loại đối sánh mà chúng tôi muốn. Nó có thể là một trong các giá trị sau:
- 0: Giá trị này chỉ định rằng tìm kiếm phải được thực hiện trên một kết hợp chính xác. Tùy chọn này thường được sử dụng khi phạm vi của chúng tôi được cho là không được sắp xếp theo bất kỳ thứ tự nào.
- 1: Đây là giá trị mặc định. Tùy chọn này giả định rằng phạm vi đã được sắp xếp theo thứ tự tăng dần. Đặt tham số này thành 1 sẽ trả về giá trị lớn nhất nhỏ hơn hoặc bằng khóa_ tìm kiếm.
- -1: Tùy chọn này giả sử phạm vi đã được sắp xếp theo thứ tự giảm dần. Đặt tham số này thành -1 trả về giá trị nhỏ nhất lớn hơn hoặc bằng khóa_ tìm kiếm.
Hãy lấy một ví dụ đơn giản để hiểu cách hoạt động của hàm MATCH. Giả sử bạn có danh sách tên nhân viên sau:
Nếu bạn muốn biết vị trí của Cierra Vega trong danh sách, bạn có thể sử dụng hàm MATCH như sau:
= MATCH (‘Đóng Vega’, A2: A8,0)
Điều này sẽ trả về giá trị 4 vì phần tử tương ứng là phần tử thứ tư trong danh sách, bắt đầu từ ô A2. Lưu ý rằng 4 không phải là số dòng của phần tử tương ứng. Đây là vị trí hoặc chỉ số của phần tử trong phạm vi được chỉ định (A2: A8).
Hàm INDEX
Hàm INDEX của Google Trang tính cung cấp nội dung của một ô hoặc dải ô cụ thể. Nói cách khác, nó nhận một phạm vi ô, một chỉ số hàng và một chỉ mục cột, rồi trả về giá trị trong ô nằm ở giao điểm của hàng và cột được chỉ định.
Cú pháp của hàm INDEX như sau:
INDEX (tham khảo, [row], [column])
Đây,
- tham chiếu là phạm vi ô mà chúng ta muốn trích xuất phần tử.
- row là phần bù của hàng trong liên kết mà chúng ta muốn trích xuất phần tử.
- cột là phần bù của cột trong liên kết mà chúng ta muốn trích xuất phần tử. Tham số này là tùy chọn.
Hãy lấy một ví dụ đơn giản để hiểu cách hoạt động của hàm INDEX. Giả sử bạn có danh sách tên nhân viên sau:
Nếu bạn muốn biết tên của nhân viên đứng ở vị trí thứ 4 trong danh sách tên, bạn có thể sử dụng hàm INDEX như sau: = INDEX (A2: A8,4,1)
Điều này sẽ trả về giá trị “Cierra Vega” vì nó là phần tử ở vị trí 4 của cột 1 bắt đầu từ ô A2. Lưu ý rằng 4 không phải là số dòng của phần tử tương ứng. Đây là vị trí hoặc chỉ số của phần tử trong phạm vi được chỉ định (A2: A8).
Tại sao sử dụng hàm INDEX và MATCH trong Google Trang tính?
Như bạn có thể nhận thấy, các hàm MATCH và INDEX không thực sự có nhiều giá trị như chúng vốn có. Nhưng đây là điểm nổi bật. Khi bạn kết hợp các hàm INDEX và MATCH trong Google Trang tính với nhau, chúng có thể làm những việc rất hữu ích!
Khi được kết hợp với nhau, hai công thức có thể tra cứu một giá trị trong một ô từ bảng và trả về giá trị tương ứng trong một ô khác trong cùng một hàng hoặc cột.
Hãy xem xét tập dữ liệu sau, nơi bạn có danh sách ID nhân viên, tên, phòng ban của họ và mức lương hàng tháng:
Nếu bạn muốn truy cập lương của nhân viên theo tên của họ, có thể không thực hiện được với một hàm duy nhất (ngoài hàm VLOOKUP).
Đây là lúc lệnh INDEX-MATCH có thể hữu ích. Với cặp chức năng này, bạn có thể truy cập phòng ban hoặc mức lương của bất kỳ nhân viên nào bằng tên của họ hoặc tên của bất kỳ nhân viên nào theo ID của họ.
Trong các phần sau, chúng ta sẽ thảo luận chính xác cách thức đạt được điều này.
Cách kết hợp hàm INDEX và MATCH trong Google Trang tính
Công thức chung để kết hợp các hàm INDEX-MATCH:
= INDEX (range2, MATCH (search_key, range1,0))
Đây,
- khóa_tìm_kiếm là giá trị chúng tôi muốn tìm kiếm trong phạm vi1.
- range1 là phạm vi ô trong đó hàm MATCH tìm chỉ mục cho giá trị tương ứng với khóa_ tìm kiếm.
- range2 là phạm vi ô mà từ đó hàm INDEX truy xuất giá trị tương ứng với vị trí / chỉ số được MATCH trả về.
Nói cách khác, hàm MATCH giúp hàm INDEX xác định vị trí của giá trị trả về.
Hãy xem một ví dụ để hiểu điều này nhiều hơn một chút.
Sử dụng hàm INDEX và MATCH với tham chiếu cột đơn
Trong ảnh chụp màn hình sau, bạn sẽ nhận thấy rằng chúng ta có một bảng bao gồm ID, Tên, Phòng ban và Mức lương. Ở cuối ảnh chụp màn hình, có một bảng nhỏ khác, nơi chúng tôi có tên của một trong các nhân viên và một khoảng trống cho bộ phận của nhân viên:
Nếu tên trong bảng dưới là động (có thể thay đổi), chúng tôi không thể mã hóa nó thành công thức tìm bộ phận thích hợp.
Trong trường hợp này, kết hợp các hàm MATCH và INDEX sẽ là lý tưởng. Đây là công thức chúng ta có thể sử dụng:
= INDEX (C2: C8, MATCH (B10, B2: B8,0))
Như bạn có thể thấy từ hình ảnh bên dưới, công thức này trả về bộ phận tương ứng với tên “Cierra Vega”:
Giải thích công thức
Để hiểu công thức này hoạt động như thế nào, chúng ta hãy hiểu nó. Hãy bắt đầu với hàm bên trong của công thức:
TRẬN ĐẤU (B10, B2: B8,0)
Hàm này tìm kiếm giá trị trong B10 trong phạm vi B2: B8 và trả về vị trí của giá trị đó trong phạm vi. Vì Cierra Vega là tên thứ 4 bắt đầu trong ô B2 nên hàm này trả về vị trí 4.
Bây giờ chúng ta hãy xem xét hàm công thức bên ngoài:
INDEX (C2: C8, MATCH (B10, B2: B8,0))
Công thức này tìm kiếm giá trị ở vị trí thứ 4 của phạm vi C2: C8 và trả về giá trị ở vị trí đó, đó là “Doanh số”. Đây là bộ phận sở hữu Sierra Vega.
Bây giờ, ngay cả khi chúng tôi thay đổi tên trong ô B10, chúng tôi vẫn sẽ nhận được kết quả chính xác trong ô B11:
Vì vậy, các hàm MATCH-INDEX đã giúp chúng tôi sử dụng một giá trị này để truy cập một giá trị khác trong một cột khác trong cùng một hàng.
Sử dụng hàm INDEX và MATCH với nhiều tiêu chí
Trong ví dụ trên, tôi đã truy cập một cột dưới dạng một dải ô. Tuy nhiên, các hàm MATCH-INDEX cũng có thể cung cấp tính linh hoạt hơn bằng cách cho phép chúng tôi truy cập một giá trị từ nhiều cột.
Trong bảng tính ví dụ của chúng tôi, giả sử chúng tôi có thể có tên bộ phận, tiền lương hoặc thậm chí là ID dưới dạng nhãn trong ô A11. Nói cách khác, điều gì sẽ xảy ra nếu nhãn trong ô A11 cũng là động và có thể thay đổi?
Trong trường hợp này, chúng ta sẽ cần xem xét nhiều cột và cột chúng ta tham chiếu đến sẽ phụ thuộc vào nhãn trong ô A11. Chà, điều này cũng có thể đạt được với INDEX-MATCH như thế này: = INDEX (A2: D8, MATCH (B10, B2: B8,0), MATCH (A11, A1: D1,0))
Lần này, chúng tôi cũng sử dụng tham số thứ ba của hàm INDEX (cho phép chúng tôi chỉ định chỉ mục của một cột trong phạm vi).
Như bạn có thể thấy từ hình ảnh bên dưới, nếu ô A11 chứa văn bản “Lương”, công thức này trả về mức lương tương ứng với tên được cung cấp trong ô B10:
Nếu ô A2 chứa văn bản “ID”, công thức tương tự này trả về ID hàng tháng tương ứng với tên trong ô B10:
Ngay cả khi bạn thay đổi tên trong ô B10, bạn vẫn sẽ nhận được id chính xác:
Giải thích công thức
Để hiểu công thức này hoạt động như thế nào, chúng ta hãy hiểu nó. Hãy bắt đầu với hàm MATCH đầu tiên:
TRẬN ĐẤU (B10, B2: B8,0)
Như đã giải thích trước đó, hàm này trả về vị trí của tên Cierra Vega trong phạm vi B2: B8. Vì vậy, nó trả về chỉ số 4.
Bây giờ chúng ta hãy xem xét hàm MATCH thứ hai:
TRẬN ĐẤU (A11, A1: D1,0)
Hàm này tìm kiếm giá trị trong ô A11 trong phạm vi A1: D1 và trả về vị trí của giá trị đó trong phạm vi. Khi A11 chứa văn bản “Cục”, hàm phát hiện rằng đó là phần tử thứ ba bắt đầu từ ô A1. Vì vậy, hàm này trả về vị trí 3.
Cuối cùng, hãy xem hàm bên ngoài:
INDEX (A2: D8, MATCH (B10, B2: B8,0), MATCH (A11, A1: D1,0))
Công thức này tìm kiếm giá trị ở vị trí hàng thứ 4 và vị trí cột thứ 3 trong phạm vi A2: D8 (với hiệu số hàng và cột tương ứng là 4 và 3) và trả về giá trị ở vị trí đó, là ‘Doanh số’. Đây là bộ phận tương ứng với Cierra Vega.
Vì vậy, bây giờ chúng tôi có rất nhiều sự linh hoạt để tiếp tục và thay đổi tên mà chúng tôi muốn truy cập cũng như thông tin chúng tôi cần cho tên đó.
Tại sao sử dụng INDEX và MATCH tốt hơn sử dụng hàm VLOOKUP?
Sau khi đọc toàn bộ hướng dẫn, bạn có thể nghĩ:
“Không thể thực hiện tất cả những điều này với tính năng VLOOKUP?”
Chà, có thể! Trên thực tế, những gì INDEX-MATCH thực hiện về cơ bản là hàm VLOOKUP. Tuy nhiên, có một số điều mà INDEX-MATCH có thể làm mà hàm VLOOKUP không làm được.
- INDEX-MATCH cho phép bạn tìm kiếm các cột ở cả bên trái và bên phải của một cột tìm kiếm nhất định, trong khi hàm VLOOKUP cho phép bạn chỉ tìm kiếm ở bên trái của một cột tìm kiếm. Nó tìm kiếm cột đầu tiên trong phạm vi đã cho và sau đó chỉ tìm kiếm các kết quả phù hợp ở bên phải của nó. Cố gắng truy cập vào cột bên trái của phạm vi bằng hàm VLOOKUP trả về lỗi # N / A.
- Việc thêm các cột mới hoặc di chuyển các cột hiện có không ảnh hưởng đến kết quả của công thức INDEX-MATCH vì nó đề cập đến các tham chiếu ô. Do đó, thứ tự hoặc sự thay đổi của chỉ mục cột không quan trọng. Tuy nhiên, kết quả của hàm VLOOKUP hoàn toàn bị cắt xén nếu một cột bị thay đổi hoặc bị xóa vì nó tham chiếu đến thứ tự cột chứ không phải tham chiếu ô.
Tóm lại, tính năng Chỉ mục và Đối sánh trong Google Trang tính là một sự thay thế tuyệt vời cho hàm VLOOKUP, thêm tính linh hoạt hơn cho các hoạt động tìm kiếm bằng cách sử dụng tham chiếu ô động. Chúng tôi hy vọng hướng dẫn này hữu ích và dễ hiểu cũng như sử dụng.
