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 trong Google Sheets.
Bài viết này giải thích cách trả về giá trị từ bên trái hoặc bên phải của một giá trị tìm kiếm. Kết quả có thể từ cùng một hàng hoặc một 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 được gì không.
- Cách lọc hàng tiếp theo để lọc dữ liệu trong Google Sheets.
- Cách dịch chuyển và trùng khớp sử dụng truy vấn trong Google Sheets.
Để tìm kiếm một giá trị, chúng ta sẽ không sử dụng hàm TÌM KIẾM. Thay vào đó, chúng ta sẽ sử dụng hàm LỌC.
Bằng cách sử dụng LỌC, chúng ta có thể dễ dàng tìm kiếm giá trị và dịch chuyển ô sang trái hoặc sang 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 ví dụ dữ liệu chỉ mang tính chất minh họa.
(Tổng hợp dữ liệu và hình ảnh từ Google Sheets)
Có thể có trường hợp chúng ta sắp xếp dữ liệu như trên. Ý tôi là tên các tháng trong một cột và giá trị tương ứng trong cột kế tiếp.
Hãy giả định rằng 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ẽ là 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 tiếp theo, tức là hàng # 3, tôi muốn lặp lại điều này và trả về giá trị “Feb” và cứ thế.
Tôi đã hiển thị kết quả mong đợi của mình ở J2:J7 (vui lòng tham khảo hình ảnh số 1 ở trên).
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 ô.
Thay vì hàng tiêu đề, bạn có thể chọn hàng tìm kiếm cùng vị trí với 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 sang 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, hàm đơn giản ở đây là LỌC.
Chúng ta muốn giá trị dịch chuyển từ hàng # 1.
Nếu chúng ta không muốn dịch chuyển ô sang trái hoặc sang phải của khóa tìm kiếm, chúng ta có thể sử dụng công thức sau:
=filter($A$1:$H$1,A2:H2="A")
Điều này sẽ trả về một ô trống. Nếu bạn nhập 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 công thức LỌC như sau:
=filter({0,$A$1:$H$1},{A2:H2,0}="A")
Thêm công thức này vào ô J2 (theo hình ảnh số 1 ở trên). Sau đó, kéo công thức này xuống.
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ố 0 vào 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. Chúng ta chỉ cần 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ên, số 0 được thêm vào trước hàng kết quả (phạm vi) và sau hàng tìm kiếm (điều kiện).
Đây chính là ngược lại của công thức trước đó. Tôi hy vọng công thức sau sẽ được giải thích rõ ràng:
=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 số 2 và dịch chuyển 2 ô sang phải trong hàng đầu tiên (hàng tiêu đề).
Làm thế nào để loại bỏ 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 số 4 vì có nhiều kết quả tìm kiếm.
Nếu bạn chỉ muốn lấy kết quả tìm kiếm đầu tiên, hãy sử dụng hàm INDEX như sau:
=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 hàm TEXTJOIN như sau:
=textjoin(", ",true,filter({$A$1:$L$1,0,0},{0,0,A2:L2}="A"))
Vậy còn lỗi #N/A thì sao?
Chỉ cần bọc công thức trong IFNA là được.
Sử dụng các công thức trên để tìm kiếm 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 hàm HLOOKUP. Nhưng nó sẽ không trả về nhiều giá trị dịch chuyển.
Tôi ưa thích LỌC hơn HLOOKUP vì công thức trước dễ hiểu hơn và có thể trả về nhiều giá trị 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)
Như bạn có thể thấy, chìa khóa để dịch chuyển là sửa đổi phạm vi HLOOKUP bằng cách thêm cột bổ sung ở 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, hãy sửa đổi hàng đầu tiên trong phạm vi bằng cách thêm hai ô thêm vào phía sau và hàng thứ hai bằng cách thêm hai ô thêm vào phía trước.
- Để tìm kiếm và dịch chuyển hai ô sang phải, hãy sửa đổi hàng đầu tiên trong phạm vi bằng cách thêm hai ô thêm vào phía trước và hàng thứ hai bằng cách thêm hai ô thêm vào phía sau.
Đó là tất cả. Chúc bạn thành công. Mời bạn tham khảo thêm tại Crawlan.com.
