Bạn có thể sử dụng công thức ‘một trong các công thức tốt nhất’ để tìm kiếm một giá trị trong ô và trả về tiêu đề của nó bằng cách sử dụng Hlookup. Sử dụng một công thức Hlookup để tìm kiếm trong một bảng và tìm tiêu đề, ý tôi là lấy tên cột hoặc nhãn trường, trong Google Sheets.
Một trong những công thức tốt nhất?
Đúng vậy! Bởi vì tôi có hướng dẫn tương tự trên trang này. Tôi sẽ chắc chắn liên kết đến các trang đó sau trong bài viết này.
Tôi biết Hlookup dùng để tìm kiếm theo hàng đầu tiên. Nhưng bạn có thể sử dụng Hlookup để tìm kiếm theo bất kỳ hàng nào bằng cách vượt qua các rào cản. Tôi sẽ chia sẻ phương pháp làm việc tuyệt vời đó ở đây trong bài viết này.
Nếu bạn là độc giả thường xuyên của tôi hoặc quay lại, bạn có thể đã thấy các hướng dẫn tương tự trên trang này để thực hiện mục đích tương tự hoặc khác nhau.
Trước khi tiến xa hơn, cho phép tôi giới thiệu cho bạn các hướng dẫn trông giống nhau trước đây.
-
Tìm kiếm qua các cột và trả về tiêu đề trong Google Sheets – Trong hướng dẫn trước đây này, tôi đã chi tiết về giải pháp trên nhưng sử dụng một phương pháp rất khác. Ở đó, tôi đã sử dụng hàm Query làm công thức chính. Không quá khó để viết code, nhưng tất nhiên, phức tạp hơn một chút.
-
Tìm kiếm và lấy lại tiêu đề cột trong Google Sheets – Đây là một giải pháp hơi khác. Bạn có thể sử dụng nó để tìm kiếm xuống cột đầu tiên sau đó lấy lại tiêu đề từ các cột yêu cầu.
Lần này, tôi đang sử dụng Hlookup, đó là tối ưu hơn và rất đơn giản để viết code.
Công thức để tìm kiếm trong bảng và tìm tiêu đề trong Sheets
Như bạn có thể biết, Hlookup chỉ có thể tìm kiếm theo hàng đầu tiên trong một bảng (dải/mảng) và trả về giá trị từ cột tìm thấy. Nhưng ngược lại cũng hoàn toàn có thể! Ý tôi là bạn có thể tìm kiếm một bảng đầy đủ (mảng/dải) và trả về tiêu đề.
Hãy viết công thức cho cùng một dữ liệu mẫu trên. Bảng nằm trong dải B2:E6. Nhưng chúng ta có thể sử dụng một dải không giới hạn trong công thức để bao gồm các hàng tương lai. Do đó, tôi sẽ sử dụng dải B2:E trong công thức này.
Hlookup hàng đầu và Hlookup bất kỳ hàng
Hành vi mặc định của Hlookup là tìm kiếm theo hàng đầu tiên như dưới đây.
Bạn có thể tìm kiếm từ khóa “Bắc”, “Nam”, “Đông” hoặc “Tây” trong Hlookup vì nó nằm trong hàng đầu tiên của bảng hoặc dải.
Giả sử bạn sử dụng “Nam” là từ khóa. Sau đó, bạn có thể trả về bất kỳ giá trị nào trong dải C3:C.
Ví dụ:
=hlookup("Nam",B2:E,2,0)Điều này sẽ trả về giá trị “Adam” vì tôi đã chỉ định 2 là chỉ số hàng (hàng thứ hai trong dải).
Tôi muốn thực hiện ngược lại của điều này. Ý tôi là tìm kiếm từ khóa “Adam” và trả về tiêu đề “Nam”.
Dưới đây bạn có thể thấy công thức Hlookup để tìm kiếm một bảng đầy đủ và tìm thấy tiêu đề trong Google Sheets.
Công thức Hlookup để tìm kiếm trong bảng và tìm giá trị hàng tiêu đề
Công thức:
=hlookup(1,{SORTN(search(F3,B3:E));B2:E2},2,0)
Bạn có thể sử dụng cùng một công thức Hlookup này để tìm giá trị trong hàng tiêu đề của bất kỳ ô nào trong Google Sheets.
Đối với điều đó, ý tôi là đối với dữ liệu nguồn của bạn, bạn chỉ cần thay đổi tham chiếu bảng trong công thức đó nằm trong Dấu ngoặc nhọn.
Bạn phải học cách viết code cho công thức này, sau đó bạn mới có thể sử dụng nó trong dữ liệu nguồn của bạn. Tôi sẽ giải thích điều đó trong phần giải thích công thức dưới đây.
Giải thích công thức – Làm thế nào Hlookup tìm kiếm toàn bộ bảng và tìm tiêu đề
Trong công thức Hlookup trên, bạn chỉ cần thay đổi ‘dải’. Hiểu rõ các đối số Hlookup là một điều bắt buộc để theo dõi công thức của tôi. Vì vậy, cho phép tôi bắt đầu với cú pháp.
Cú pháp:
HLOOKUP(search_key, range, index, [is_sorted])Theo công thức của tôi, search_key là 1, range là {SORTN(search(F3,B3:E));B2:E2}, index là 2 và is_sorted là 0.
Trong đó, bạn chỉ cần hiểu về dải vì đó là phần duy nhất trong công thức mà bạn phải thay đổi cho dải của mình. Các giá trị khác trong công thức là hằng số.
Dải Hlookup ảo bằng Công thức Tìm kiếm để tìm kiếm qua Bất kỳ Hàng nào
Xem giá trị trong ô F3. Ô đó chứa search_key, là “Adam”.
Trong phạm vi Hlookup trên, bạn có thể thấy một công thức dựa trên chức năng Tìm kiếm. Tôi đã sử dụng ô F3 (Ý tôi là khóa tìm kiếm “Adam”) trong công thức đó.
=search(F3,B3:E)Nếu bạn nhập công thức này vào Bảng của mình, nó sẽ không hoạt động. Bởi vì nó cần hỗ trợ mảng. Bạn phải sử dụng Filter, Sort, Sortn, Index hoặc ArrayFormula function cùng với nó.
Chưa từng sử dụng những hàm mảng này? Sau này, bạn có thể thoải mái xem qua Hướng dẫn về các hàm của tôi.
Chỉ cần nhập công thức trên vào Bảng của bạn như dưới đây và xem kết quả.
=ArrayFormula(search(F3,B3:E6))
Trong công thức chính, tôi có công thức này bên trong SORTN. Vì vậy, tôi không sử dụng ArrayFormula ở đó. Sau đó, tôi sẽ nói cho bạn biết tại sao tôi đã chọn SORTN thay vì ArrayFormula.
Con số 1 trong công thức chỉ ra rằng ô đầu tiên trong cột thứ hai chứa khóa tìm kiếm “Adam”.
Thay đổi khóa tìm kiếm “Adam” thành “Roger” trong ô F3 và xem điều gì xảy ra.
Điều này có nghĩa là khóa tìm kiếm “Roger” nằm trong hàng thứ ba của cột thứ tư.
Bạn phải hiểu rằng Hlookup chỉ có thể tìm kiếm theo hàng đầu tiên. Nó không bao giờ tìm kiếm qua hàng thứ hai, hàng thứ ba hay bất kỳ hàng nào khác.
Vì vậy, dải ảo trên không có ý nghĩa gì, phải không? Nhưng nó có ý nghĩa khi bạn sử dụng nó trong SORTN! Hãy tiếp tục đọc để có thêm thông tin về việc sử dụng này.
Tinh chỉnh Dải Hlookup ảo với SORTN
Chỉ cần thay thế ArrayFormula trong công thức Tìm kiếm bằng SORTN. Sau đó, nó sẽ chỉ trả về một hàng chứa số 1.
Thực tế, SORTN sắp xếp bảng ảo (kết quả của Tìm kiếm) và giới hạn kết quả cho một hàng duy nhất.
Do sự sắp xếp liên quan, hàng chứa số # 1 sẽ luôn nằm trên đầu.
=SORTN(search(F3,B3:E6))Tôi đã kết hợp hàng tiêu đề B2:E2 với nó như là hàng thứ hai và đó là phạm vi Hlookup (ảo).
Hãy nhớ rằng khóa tìm kiếm là 1 trong Hlookup. Nó tìm kiếm qua hàng đầu trong phạm vi ảo này và trả về hàng tiêu đề (hiện là cột thứ hai).
Hãy làm theo phương pháp này để viết công thức Hlookup tìm kiếm một bảng đầy đủ và tìm tiêu đề trong Google Sheets.
Cảm ơn bạn đã đọc, chúc bạn có những phút thú vị!
