Để lấy địa chỉ ô chứa giá trị tìm kiếm trong Google Sheets, tôi có ba công thức khác nhau mà bạn có thể thử. Hai trong số chúng dựa trên hàm CELL.
Trong hàm CELL, có một loại thông tin gọi là “address”. Nó giúp chúng ta trả về địa chỉ của một ô cụ thể.
Cú pháp: CELL(info_type, reference)
Dưới đây là hai ví dụ nhanh.
=cell("address",B9)
Công thức trên sẽ trả về $B$9 (địa chỉ ô), trong khi công thức dưới đây sẽ trả về chiều rộng của ô A9.
=cell("width",A9)
Điều này có nghĩa là loại thông tin quan trọng.
Trong hai công thức trên, B9 và A9 được gọi là tham chiếu ô.
Hàm CELL hỗ trợ biểu thức (công thức) làm tham chiếu thay vì tham chiếu trực tiếp đến một ô.
Ở đây mở ra khả năng sử dụng hàm CELL để trả về địa chỉ ô chứa giá trị tìm kiếm trong Google Sheets.
Làm thế nào?
Chúng ta sẽ sử dụng một công thức tìm kiếm như tham chiếu trong CELL. Tôi sẽ giải thích chi tiết về điều đó ở các phần dưới đây.
Công thức để lấy địa chỉ ô chứa giá trị tìm kiếm trong Google Sheets
Dữ liệu mẫu:
Dưới đây là các lựa chọn công thức khác nhau. Như đã đề cập, tôi có ba công thức, như sau:
- Hai công thức dựa trên hàm CELL.
- Sử dụng Vlookup.
- Sử dụng Index-Match.
- Một công thức dựa trên IF và SORTN.
Công thức dựa trên hàm CELL
Hãy bắt đầu với sự kết hợp của Vlookup và CELL.
Trong bộ dữ liệu trên Google Sheets, tôi muốn tìm kiếm tên “Tim” và trả về địa chỉ ô từ cột “Score 2”.
Ý tưởng ở đây là sử dụng Vlookup để trả về giá trị tìm kiếm thay vì ID ô và sử dụng nó như tham chiếu (biểu thức) trong CELL.
Công thức sau sẽ tìm kiếm “Tim” trong C2:F2 (cột đầu tiên trong mảng C2:F7) và trả về giá trị (Score 2) từ cột thứ ba.
=VLOOKUP("Tim",C2:F7,3,0)
Chỉ cần sử dụng công thức trên như tham chiếu trong một công thức CELL như dưới đây để lấy địa chỉ ô của giá trị tìm kiếm, tức là “Tim”.
Công thức 1:
=cell("address",vlookup("Tim",C2:F7,3,0))
Kết quả: $E$5
Công thức Index-Match tuân theo cùng một hướng. Ở đây chúng ta lùi các hàng và cột cụ thể để trả về kết quả Vlookup ở trên. Chúng ta có thể sử dụng nó như một tham chiếu trong CELL như đã nói ở trên.
Đây là tham chiếu đó.
=index(C2:F7,match("Tim",C2:C7,0),3)
Nếu chúng ta rút gọn công thức trên, nó sẽ trở thành như sau.
=index(C2:F7,4,3)
Công thức Index lùi lại bốn hàng để đến hàng chứa “Tim” và ba cột để đến cột “Score 2” tương ứng.
Chỉ cần sử dụng Index-Match trong CELL giống như Vlookup để lấy địa chỉ ô của Kết quả tìm kiếm trong Google Sheets.
Công thức 2:
=cell("address",index(C2:F7,match("Tim",C2:C7,0),3))
Công thức dựa trên IF và SORTN
Đây sẽ là một phương pháp hoàn toàn khác và rất dễ hiểu. Bạn có thể nhanh chóng làm theo hướng dẫn từng bước dưới đây của tôi.
Đầu tiên, chúng ta sẽ sử dụng một công thức IF logic để trả về số hàng tương ứng với tên “Tim”.
=ArrayFormula(if(C2:C7="Tim",row(C2:C7)))
Công thức sẽ trả về các giá trị như hình minh họa. Thay thế ArrayFormula bằng SORTN để chỉ lấy số hàng.
=SORTN(if(C2:C7="Tim",row(C2:C7)))
Thêm tiền tố “E” là tiêu đề cột “Score 2” vào đầu đầu ra trên.
Công thức 3:
="$E$"&SORTN(if(C2:C7="Tim",row(C2:C7)))
Đó là tất cả! Công thức trên là công thức thứ ba để lấy địa chỉ ô chứa giá trị tìm kiếm trong Google Sheets.
Bây giờ là một bí quyết thêm nữa.
Hai công thức đầu tiên (Công thức 1 và Công thức 2) có một ưu điểm. Bởi vì trong hai công thức đó, chúng ta đã cố định số cột. Trong khi đó, trong công thức cuối (thứ ba), chúng ta đã cố định một chữ cái cột.
Giả sử chúng ta muốn tìm kiếm và tìm địa chỉ ô của một giá trị giao cắt. Hãy xem chúng ta có thể làm gì ở đây.
Giá trị cần tìm kiếm: “Tim”
Cột cần tìm kiếm: “Score 1”
Thay thế số ba trong hai công thức đầu tiên bằng công thức khớp ngang sau đây.
match("Score 1",C2:F2,0)
Vì vậy, Công thức 1 sẽ trở thành;
=cell("address",vlookup("Tim",C2:F7,match("Score 1",C2:F2,0),0))
và Công thức 2 sẽ trở thành;
=cell("address",index(C2:F7,match("Tim",C2:C7,0),match("Score 2",C2:F2,0)))
Đó là tất cả. Cảm ơn vì đã đọc. Chúc bạn có những phút giây thú vị!
