Để tìm kiếm qua các cột và trả về tiêu đề, công thức tốt nhất trong Google Sheets sẽ là công thức Match. Nhưng khi số cột rất lớn, công thức Match sẽ không giúp ích được. Đây là sự quan trọng của bài viết này.
Tôi sử dụng công thức Dynamic Query để tìm kiếm một từ khóa trên nhiều cột hoặc ô trong một dải dữ liệu và trả về tiêu đề cột tương ứng.
Tìm kiếm qua các cột cho một từ khóa có thể là một trường hợp hiếm gặp, nhưng đôi khi rất hữu ích.
Ví dụ về tìm kiếm qua các cột và trả về tiêu đề
Dưới đây, từ khóa tìm kiếm nằm trong ô B2 và các cột tìm kiếm là C, D và E.
Vì chỉ có ba cột để tìm kiếm, bạn có thể sử dụng công thức Match.
=ifs(isna(match(B2,C3:C12,0))=FALSE,C2,isna(match(B2,D3:D12,0))=FALSE,D2,isna(match(B2,E3:E12,0))=FALSE,E2)
Lưu ý: Để tìm hiểu cách sử dụng từng hàm, vui lòng xem hướng dẫn về các hàm trong Google Sheets của tôi.
Công thức trên tìm kiếm từ khóa được cung cấp trong ô B2 trên nhiều cột (C, D và E) và trả về tiêu đề của cột được tìm thấy.
Trong một tình huống thực tế, bạn có thể nói rằng công thức này tìm kiếm tên nhân viên trên nhiều cột và trả về dự án được giao cho anh ta.
Công thức này sẽ không khả dụng nếu số cột quá lớn. Ví dụ, trong một tập dữ liệu gồm 10 cột, bạn có thể thấy nó là một công việc khó khăn để mã hóa.
Công thức Dynamic để tìm kiếm qua nhiều cột và trả về tiêu đề
Cập nhật: Đây là một phương pháp truyền thống. Tôi đã thêm một giải pháp Lambda mới ở cuối bài viết này.
Tôi có một công thức động để tìm kiếm một giá trị trong nhiều cột trong Google Sheets và trả về nhãn trường, hoặc có thể nói là tiêu đề.
Nếu bạn chỉ có 1-3 cột, hãy sử dụng công thức Match đã nêu ở trên. Nếu số cột lớn hơn 3, hãy sử dụng truy vấn sau. Lưu ý rằng nó sẽ hoạt động cho bất kỳ số cột nào.
=Query(transpose(query({C2:E},"Select * where "&join(" or ",ArrayFormula("Col"&column(C2:E2)-column(C2)+1&" ='"&B2&"'")),1)),"Select Col1 where Col2='"&B2&"'",0)
Công thức này dùng để tìm kiếm trong dải cột C2:E (ba cột) như đã nêu ở trên.
Để thay đổi các cột tìm kiếm, thay đổi tham chiếu C2:E (nó xuất hiện hai lần trong công thức) để tăng hoặc giảm số cột tìm kiếm.
Ví dụ, nếu dải là C2:H (6 cột), hãy thay đổi công thức như sau:
=Query(transpose(query({C2:H},"Select * where "&join(" or ",ArrayFormula("Col"&column(C2:H2)-column(C2)+1&" ='"&B2&"'")),1)),"Select Col1 where Col2='"&B2&"'",0)
Vì vậy, hãy sử dụng công thức động trên để tìm kiếm qua các cột và trả về tiêu đề trong Google Sheets.
Giải thích công thức (Tìm kiếm qua nhiều cột)
Hãy để tôi chỉ bạn từng bước trong việc phát triển công thức Query này.
Bên dưới là một kết hợp của hai công thức Query.
Đây là Query nội bộ, trả về dòng chứa từ khóa tìm kiếm.
=query({C2:H},"Select * where "&join(" or ",ArrayFormula("Col"&column(C2:H2)-column(C2)+1&" ='"&B2&"'")),1)
Làm thế nào nó có thể trả về số dòng?
Tôi sẽ giải thích sau (xem cột OR động phía dưới).
Dưới đây là đầu ra.
Trong công thức chính, như bạn có thể thấy, Query nội bộ được bao quanh bởi Transpose. Điều này làm cho đầu ra trên dọc.
Trong Query bên ngoài, dữ liệu hai cột này là dải.
Nó trả về giá trị cột 1 (tiêu đề) nếu giá trị cột 2 là từ khóa tìm kiếm.
Cột OR động trong Query
Query nội bộ được sử dụng trong công thức trên bằng cách sử dụng công thức sau.
=query({C2:H},"Select * where Col1='"&B2&"' or Col2='"&B2&"' or Col3='"&B2&"' or Col4='"&B2&"' or Col5='"&B2&"' or Col6='"&B2&"'",1)
Trong Query này, mệnh đề Where chứa nhiều điều kiện OR.
Tương tự: Cách sử dụng nhiều OR trong Google Sheets Query.
Col1='”&B2&”‘ or Col2='”&B2&”‘ or Col3='”&B2&”‘ or Col4='”&B2&”‘ or Col5='”&B2&”‘ or Col6='”&B2&”‘`
Query kiểm tra từng cột cho từ khóa tìm kiếm trong ô B2.
Không nên sử dụng nhiều OR như trên trong Query.
Nếu như vậy, không có ý nghĩa trong việc sử dụng Query thay vì công thức Match đã cung cấp để tìm kiếm qua các cột và trả về tiêu đề trong Google Sheets.
Công thức dưới đây tự động hóa việc sử dụng nhiều OR trong Query.
&join(" or ",ArrayFormula("Col"&column(C2:H2)-column(C2)+1&" ='"&B2&"'"))
Có một lợi ích rõ ràng khi sử dụng phương pháp này, đó là nó có thể bao gồm một số lượng lớn các cột.
Để sử dụng nó trong các dải khác nhau, hãy thay đổi các tham chiếu ô dưới đây:
C2:H2 - dải
C2 - địa chỉ ô đầu tiên trong dải
B2 - ô chứa từ khóa tìm kiếm (tiêu chí)Nếu bạn muốn tìm kiếm qua nhiều cột cho một số, thì tiêu chí nên được sử dụng như sau.
"&B2&" thay vì '"&B2&"'
Giải pháp Lambda
Phương pháp này cũng hoạt động cho nhiều cột.
Hơn nữa, nếu một tên lặp lại trong nhiều cột, nó sẽ trả về tiêu đề của cả hai cột.
=textjoin(",",true,BYCOL(C2:E11,LAMBDA(c,IFNA(IF(MATCH(B2,c,0),INDEX(c,1),""),""))))
Vì nó hoạt động tương tự trong Excel, tôi sẽ dẫn bạn đến bài hướng dẫn liên quan của tôi ở đây – Tìm kiếm một giá trị và trả về tiêu đề của nó trong Excel 365.
Để tìm kiếm qua nhiều cột và trích xuất tiêu đề, chỉ sử dụng công thức Match trong Google Sheets nếu dữ liệu của bạn chứa 2-3 cột. Ngược lại, hãy sử dụng Query hoặc Lambda.
