Trong bài viết này, chúng ta sẽ tìm hiểu cách sử dụng hàm XMATCH để tìm kiếm giá trị trong nhiều cột trên Google Sheets. Bạn sẽ được hướng dẫn từng bước để viết công thức và hiểu cách tùy chỉnh nó theo nhu cầu của mình.
Giới thiệu về hàm XMATCH
Công dụng chính của các hàm tìm kiếm, chẳng hạn như MATCH hoặc XMATCH trong Google Sheets là tìm vị trí của một mục trong một cột hoặc hàng. Nó sẽ trả về một số tương đối với các hàng trong mảng, không phải hàng trong bảng tính. Chúng ta có thể sử dụng số đó để tham chiếu hoặc trong các hàm khác như INDEX, FILTER, QUERY, vv., để thực hiện các nhiệm vụ bổ sung.
Hàm XMATCH được sử dụng để tìm kiếm trong một mảng một chiều. Nó yêu cầu ít nhất hai đối số để hoạt động: search_key và lookup_range. Lookup_range phải là một mảng một chiều.
Cách sử dụng XMATCH trên nhiều cột (không kéo)
Khi bạn muốn sử dụng XMATCH trên nhiều cột, bạn có thể phải kéo công thức qua. Tuy nhiên, điều này không đáp ứng yêu cầu của bạn khi bạn muốn sử dụng nó với các hàm khác như INDEX hoặc FILTER.
Vậy làm thế nào chúng ta mở rộng phạm vi tìm kiếm XMATCH cho tất cả các cột trong một bảng?
Chúng ta có thể sử dụng một trong các hàm LAMBDA, chính xác là hàm MAP, với một số hàm khác, để làm điều này.
XMATCH trên nhiều cột trong Google Sheets (không kéo)
Giả sử bạn có một mẫu xem lịch trên Google Sheets và muốn tìm kiếm một ngày cụ thể trên đó để tìm vị trí tương đối của nó.
Phạm vi xem lịch là B3:H16 và ngày cần tìm kiếm nằm trong ô J6.
Thường, để tìm kiếm trên nhiều cột, bạn sẽ sử dụng công thức sau trong ô K6 và kéo qua cho đến khi bạn nhận được một giá trị khác #N/A:
=XMATCH($J$6,B3:B16)
Công thức sẽ trả về số 9 trong cột thứ tư. Điều này là do trong công thức, tham chiếu search_key là tuyệt đối và tham chiếu lookup_range là tương đối. Vì vậy, tham chiếu cột trong lookup_range tăng khi bạn kéo qua.
Dưới đây là cách sử dụng hàm MAP với XMATCH để tìm kiếm trên nhiều cột mà không cần kéo công thức qua:
=SORTN(MAP(SEQUENCE(COLUMNS(B3:H16)),LAMBDA(col,XMATCH(J6,INDEX(B3:H16,0,col)))))
Quan trọng: Xóa bao bọc SORTN() để có các giá trị khớp từ tất cả các cột, nếu có. Trong trường hợp này, kết quả sẽ là {#N/A; #N/A; #N/A; 9; #N/A; #N/A; #N/A} vì chỉ có một giá trị khớp.
Công thức này áp dụng hàm XMATCH cho từng cột trong phạm vi B3:H16, sử dụng giá trị trong ô J6 làm khóa tìm kiếm. Hàm MAP sau đó trả về một mảng kết quả, được chuyển đến hàm SORTN để trả về giá trị đầu tiên trong mảng, ngoại trừ #N/A.
Bạn có thể sử dụng công thức này với INDEX theo hai cách:
- Để trả về giá trị từ bất kỳ cột nào trong cùng một hàng.
- Để trả về giá trị từ cùng một cột từ bất kỳ hàng nào.
Chúng ta sẽ xem xét hai mẹo bổ sung này sau khi giải thích công thức XMATCH cho nhiều cột.
Giải thích công thức
Chúng ta đã sử dụng các hàm INDEX, COLUMNS, SEQUENCE, SORTN và MAP để sử dụng XMATCH cho nhiều cột trong Google Sheets. Dưới đây là phân tích vai trò của từng hàm:
1. Công thức cơ bản
Công thức cơ bản là =XMATCH($J$6,B3:H16). Tuy nhiên, công thức này sẽ không hoạt động vì vi phạm cú pháp XMATCH, không cho phép sử dụng nhiều cột làm phạm vi tìm kiếm.
Để sửa điều này, chúng ta có thể sử dụng hàm INDEX để trả về cột đầu tiên từ B3:H16, điều này sẽ cung cấp cho XMATCH một cột duy nhất (mảng một chiều) để tìm kiếm. Công thức này được hiển thị bên dưới:
=XMATCH($J$6,INDEX(B3:H16,0,1)) // Công thức #1
Hàm INDEX có ba đối số: tham chiếu, hàng và cột. Trong công thức trên, đối số cột được đặt là 1, có nghĩa là hàm INDEX sẽ trả về cột đầu tiên từ tham chiếu (phạm vi tìm kiếm).
Hãy để công thức này qua một bên trong một thời gian.
2. Mảng ảo cho phép ánh xạ
Chúng ta có thể sử dụng hàm COLUMNS để tìm số cột trong phạm vi tìm kiếm. Nếu chúng ta bọc hàm COLUMNS bằng hàm SEQUENCE, chúng ta sẽ có một mảng các số như sau: {1; 2; 3; 4; 5; 6; 7}.
=SEQUENCE(COLUMNS(B3:H16)) // Công thức #2
Đúng rồi! Chúng ta có 7 cột trong phạm vi.
Mảng này có thể được sử dụng để đại diện cho các cột trong phạm vi tìm kiếm, cụ thể là đối số cột của công thức INDEX trong Công thức #1.
Chỉ cần thay thế 1 bằng Công thức #2 không đủ. Chúng ta cần ánh xạ từng giá trị trong Công thức #2 bằng cách sử dụng hàm MAP. Điều này tương đương với việc sử dụng XMATCH cho nhiều cột. Chúng ta sẽ tìm hiểu nó trong bước tiếp theo. Hãy để công thức này qua một bên nữa.
3. Hàm MAP để sử dụng XMATCH trên tất cả các cột
Dưới đây là công thức chung mà chúng ta sẽ làm theo để sử dụng XMATCH trên nhiều cột trong Google Sheets:
Generic Formula:
MAP(formula#2, LAMBDA(col,formula_expression))
Trong đó:
- formula#2 là dãy số thứ tự các cột.
- col là tên của formula#2.
- formula_expression là công thức cơ bản với đối số cột trong INDEX được thay thế bằng tên col.
Dưới đây là công thức được mã hóa dựa trên công thức chung trên:
=MAP(SEQUENCE(COLUMNS(B3:H16)),LAMBDA(col,XMATCH(J6,INDEX(B3:H16,0,col)))) // Công thức #3
Công thức này sẽ trả về #N/A trong các cột không có khớp. Chúng ta có thể bao bọc công thức này bằng SORTN để loại bỏ những lỗi đó và chỉ trả về giá trị khớp. Đây là công thức XMATCH trên nhiều cột cuối cùng của chúng ta.
XMATCH trên nhiều cột: Trả về giá trị từ hàng khớp
Tôi nghĩ việc giải thích cách sử dụng XMATCH trên nhiều cột và trả về giá trị từ hàng khớp sẽ đơn giản với công thức tổng quát sau:
=INDEX( reference, xmatch_multiple_columns_formula )
Trong đó:
- reference là cột mà bạn muốn trả về giá trị.
- xmatch_multiple_columns_formula là công thức trả về vị trí tương đối của khóa tìm kiếm sau khi khớp nó trên nhiều cột.
Hãy áp dụng điều này vào một kịch bản thực tế.
Giả sử bạn có bảng sau trong phạm vi A1:D, trong đó A1:A chứa tên các quốc gia và B1:D chứa số lượng lúa mì sản xuất trong triệu tấn trong ba năm gần đây. Bạn muốn XMATCH một số lượng lúa mì cụ thể trong cột B đến D và trả về tên quốc gia từ cột A.
Nếu khóa tìm kiếm nằm trong ô G2, công thức sau sẽ làm điều đó:
=INDEX( A1:A, SORTN(MAP(SEQUENCE(COLUMNS(B1:D)),LAMBDA(col,XMATCH(G2,INDEX(B1:D,0,col)))) )
Tôi biết rằng một số bạn có thể biết cách chỉnh sửa hàm XMATCH trong combo này để trả về một khớp xấp xỉ. Tuy nhiên, tôi không khuyến nghị làm như vậy khi sử dụng XMATCH trên nhiều cột và trả về giá trị.
Tạo một danh sách thả xuống từ phạm vi B2:D trong ô G2 là một cách tốt để đảm bảo rằng khóa tìm kiếm luôn là một khớp chính xác. Điều này sẽ ngăn không cho các lỗi xảy ra trong công thức.
XMATCH trên nhiều cột: Trả về giá trị từ cột khớp
Bạn có thể cần trả về giá trị từ hàng khớp theo cách khác nhau. Nếu bạn muốn trả về tiêu đề, hãy sử dụng công thức tổng quát sau:
=LET( key, xmatch_multiple_columns_formula, INDEX(B1:D,1,XMATCH(1,key,1)) )
Nếu bạn muốn trả về giá trị ngay bên dưới giá trị khớp, hãy sử dụng công thức tổng quát sau:
=LET( key, xmatch_multiple_columns_formula, INDEX(B1:D,SORTN(KEY)+1,XMATCH(1,key,1)) )
Để lấy giá trị ngay bên trên giá trị khớp, thay thế +1 bằng -1.
Trong cả hai công thức, thay xmatch_multiple_columns_formula bằng công thức mà không có bao bọc SORTN().
Kết luận
Bạn có thể sử dụng XLOOKUP để thực hiện khớp xấp xỉ của khóa tìm kiếm. Tuy nhiên, nếu bạn chỉnh sửa công thức, rất quan trọng là nhận thức về những ảnh hưởng.
Ví dụ, bạn có thể sử dụng khớp xấp xỉ để trả về vị trí tương đối của khóa tìm kiếm trong tất cả các cột. Để làm điều này, bạn cần xóa bao bọc SORTN() khỏi công thức. Tuy nhiên, quan trọng là tránh sử dụng khớp xấp xỉ khi bạn muốn trả về một giá trị sau khi khớp, vì điều này có thể dẫn đến kết quả không mong muốn.
