Trong Google Sheets, chúng ta có thể áp dụng khái niệm tìm kiếm giá trị và dịch chuyển ô trong một cột hoặc hàng. Bài viết này sẽ giải thích cách trả về một giá trị từ trái hoặc phải của giá trị tìm kiếm. Kết quả có thể là từ cùng một hàng hoặc hàng khác.
Nếu yêu cầu của bạn khác, nghĩa là tìm kiếm trong một cột, hãy xem liệu hai hướng dẫn dưới đây có giúp ích không.
- Cách sắp xếp hàng tiếp theo theo hàng tiêu chí lọc trong Google Sheets
- Cách dịch chuyển kết quả trùng khớp sử dụng truy vấn trong Google Sheets
Để tìm kiếm một giá trị, tôi sẽ không sử dụng chức năng TÌM. Thay vào đó, tôi sẽ sử dụng chức năng LỌC.
Sử dụng LỌC, chúng ta có thể dễ dàng tìm kiếm một giá trị và dịch chuyển ô sang trái hoặc phải của giá trị tìm thấy trong Google Sheets. Dưới đây là cách thực hiện.
Dưới đây là một số dữ liệu mẫu chỉ để minh họa.
Đôi khi chúng ta có thể sắp xếp dữ liệu như trên. Tức là tên các tháng trong một cột và các giá trị tương ứng trong cột kế tiếp.
Giả sử tôi muốn tìm kiếm giá trị “A” trong hàng #2 và trả về tiêu đề của nó.
Trong trường hợp thông thường, kết quả sẽ trống vì F1 là trống.
Nhưng điều tôi muốn là dịch chuyển một ô sang trái và trả về “Mar” từ ô E1.
Ở hàng kế tiếp, tức là hàng #3, tôi muốn làm tương tự và trả về giá trị “Feb” và vân vân.
Tôi đã thể hiện kết quả mong đợi của mình trong J2:J7 (xem hình #1 ở trên).
Liên quan: Hlookup để tìm kiếm trong toàn bộ bảng và tìm tiêu đề trong Google Sheets – Trong hướng dẫn này, bạn sẽ tìm thấy liên kết đến hai hướng dẫn tương tự khác.
Trong ví dụ trên, công thức của tôi trả về giá trị từ hàng tiêu đề sau khi dịch chuyển một ô.
Bạn có thể chọn hàng tìm kiếm giống thay vì hàng tiêu đề để dịch chuyển và trả về giá trị.
Hãy xem cách tìm kiếm giá trị và dịch chuyển ô sang trái hoặc phải trong Google Sheets.
Tìm kiếm giá trị và dịch chuyển ô sang trái trong Google Sheets
Như tôi đã đề cập ở đầu bài, chức năng đơn giản ở đây là LỌC.
Chúng ta muốn dịch chuyển giá trị từ hàng #1.
Nếu chúng ta không muốn dịch chuyển ô sang trái hoặc phải của khóa tìm kiếm, chúng ta có thể sử dụng công thức dưới đây:
=filter($A$1:$H$1,A2:H2="A")
Điều này sẽ trả về một ô trống. Nếu bạn đặt bất kỳ giá trị nào vào ô F1, công thức sẽ trả về giá trị đó.
Chúng ta muốn dịch chuyển một ô sang trái. Vì vậy, chúng ta có thể sửa đổi LỌC như sau:
=filter({0,$A$1:$H$1},{A2:H2,0}="A")
Chèn công thức này vào ô J2 (theo hình #1 ở trên). Sau đó, kéo công thức này xuống.
Theo cách này, chúng ta có thể tìm kiếm một giá trị trong mỗi hàng và dịch chuyển ô sang trái của giá trị tìm thấy trong hàng tiêu đề.
Nếu bạn muốn dịch chuyển hai ô sang trái, hãy chèn thêm một số không trong cả hai mảng (mảng 1 là phạm vi và mảng 2 là điều kiện) trong công thức.
=filter({0,0,$A$1:$L$1},{A2:L2,0,0}="A")
Tìm kiếm giá trị và dịch chuyển ô sang phải trong Google Sheets
Ở đây không có nhiều thay đổi. Ở đây chúng ta sẽ tạo hai mảng (phạm vi và điều kiện) trong công thức khác nhau.
Trong công thức trước đó, các số không được thêm đầu vào hàng kết quả (phạm vi) và thêm vào cuối hàng tìm kiếm (điều kiện).
Ở đây, nó hoàn toàn ngược lại. Tôi hy vọng công thức sau sẽ tự giải thích.
=filter({$A$1:$L$1,0,0},{0,0,A2:L2}="A")
Công thức này tìm kiếm giá trị “A” trong hàng #2 và dịch chuyển 2 ô sang phải trong hàng đầu tiên (tiêu đề).
Làm thế nào để xóa lỗi #N/A và xử lý nhiều giá trị
Vui lòng xem ảnh chụp màn hình ở trên.
Công thức trả về hai giá trị trong hàng #4 vì có nhiều kết quả tìm kiếm.
Nếu bạn chỉ muốn kết quả tìm kiếm đầu tiên, hãy sử dụng INDEX như dưới đây.
=index(filter({$A$1:$L$1,0,0},{0,0,A2:L2}="A"),0,1)
Nếu bạn muốn tất cả các giá trị, hãy sử dụng TEXTJOIN.
=textjoin(", ",true,filter({$A$1:$L$1,0,0},{0,0,A2:L2}="A"))
Như vậy, làm thế nào về lỗi #N/A?
Chỉ cần bọc công thức trong IFNA.
Sử dụng các công thức trên để tìm kiếm một giá trị và dịch chuyển ô trong Google Sheets.
Phiên bản HLOOKUP
Chúng ta có thể thay thế công thức LỌC bằng HLOOKUP. Nhưng nó sẽ không trả về nhiều giá trị.
Tôi ưu tiên LỌC hơn HLOOKUP vì công thức trước dễ hiểu hơn và có thể trả về nhiều kết quả dịch chuyển.
Nếu bạn tò mò và muốn tìm hiểu về cách sử dụng HLOOKUP trong trường hợp trên, đây là ví dụ đó.
Hãy sử dụng HLOOKUP để tìm kiếm giá trị “A” và dịch chuyển hai ô sang trái trong Google Sheets.
=hlookup("A",{{A2:L2,0,0};{0,0,$A$1:$L$1}},2,0)
Bằng cách nhìn vào, chìa khóa để dịch chuyển là sửa đổi phạm vi HLOOKUP bằng cách thêm các cột phụ vào bên phải (hàng tìm kiếm) và bên trái (hàng kết quả).
Tóm lại, trong HLOOKUP:
-
Để tìm kiếm và dịch chuyển hai ô sang trái, sửa đổi hàng đầu tiên trong phạm vi bằng cách thêm hai ô phụ và hàng thứ hai bằng cách thêm hai ô phụ vào trước.
-
Để tìm kiếm và dịch chuyển hai ô sang phải, sửa đổi hàng đầu tiên trong phạm vi bằng cách thêm hai ô phụ vào trước và hàng thứ hai bằng cách thêm hai ô phụ vào sau.
Đó là tất cả. Chúc bạn thành công!
