Bạn có biết cách tìm ô kế bên trong Google Sheets bằng cách sử dụng Vlookup không? Trước khi trả lời câu hỏi này, bạn cần hiểu ý nghĩa của thuật ngữ “ô kế bên”.
Nếu bạn xem xét “search_key” (khóa tìm kiếm), ô kế bên có thể là ô ngay bên phải ô search_key trong phạm vi dữ liệu.
Tuy nhiên, khi xem xét kết quả của một công thức Vlookup, ô kế bên có thể là ô phía trên, ô phía dưới và cũng có thể là ô bên phải ô kết quả Vlookup.
Tôi sẽ giải thích cả hai trường hợp này. Đối với vấn đề đầu tiên, chúng ta có thể sử dụng Vlookup. Nhưng đối với vấn đề thứ hai (một phần vấn đề đầu tiên), Index-Match là giải pháp tốt hơn.
Dữ liệu mẫu – Nhận chi phiếu từ khách hàng
Hãy hiểu dữ liệu trước tiên.
Giả sử tôi đã nhận một số khoản thanh toán từ một số khách hàng và đã ghi lại chi tiết một cách chính xác trong một tệp Google Sheets.
Bạn có thể tham khảo: Phân bổ biên lai thanh toán cho hóa đơn trong Google Sheets.
Hãy xem cách sử dụng tên khách hàng để tìm số chi phiếu ngay bên phải tên khách hàng, sau khi tìm thấy, sau đó tìm số chi nhận được trước hoặc sau số chi đã tìm thấy.
Tìm ô kế bên trong Google Sheets
Làm thế nào để tìm số chi phiếu nhận từ khách hàng “C”?
Tìm Giá trị Kế tiếp (Bên phải) của Search_Key sử dụng Vlookup
Cú pháp: VLOOKUP(search_key, range, index, [is_sorted])
Trong Vlookup, khách hàng “C” sẽ là search_key.
Vlookup có thể tìm kiếm khóa này và trả về giá trị bất kỳ từ ô liền kề bên phải hoặc có thể nói là ô kế bên.
Lưu ý: Đối với việc lấy giá trị từ ô kế bên trái của search_key Vlookup, hãy sử dụng Vlookup ngược lại. Ví dụ, search_key là số chi phiếu 271058 và điều bạn muốn tìm là tên khách hàng (ô kế bên, bên trái của search_key).
Dưới đây là công thức ô kế bên sử dụng Vlookup hoạt động trong Google Sheets.
=vlookup("C",A2:E,2,0)Bạn có thể sử dụng tham chiếu ô (G1) như search_key như dưới đây.
=vlookup(G1,A2:E,2,0)Tìm Giá trị Kế tiếp (Bên phải) của Search_Key sử dụng Index-Match
Như bạn có thể thấy trong ảnh chụp màn hình ở trên, search_key nằm trong ô G1. Bằng cách sử dụng công thức Match trong Google Sheets, chúng ta có thể so khớp search_key “C” trong cột A.
=match(G1,A2:A,0)Điều này sẽ trả về số 3 là tên khách hàng “C” có sẵn trong hàng thứ ba trong phạm vi A2:A. Với Index, chúng ta có thể lùi 3 hàng trong cột B.
=index(B2:B,match(G1,A2:A,0))Điều này có nghĩa là chúng ta có thể sử dụng một sự kết hợp Index-Match như trên là một giải pháp thay thế cho công thức ô kế bên Vlookup trong Google Sheets.
Index-Match để Trả về Giá trị từ Trên và Dưới ô Kết quả Vlookup
Dễ nhớ tên khách hàng hơn là số chi phiếu do khách hàng đó phát hành, đúng không?
Ví dụ, giả sử tôi biết rằng tôi đã nhận được một số phiếu từ khách hàng “C”.
Nhưng điều tôi không nhớ là số chi phiếu nhận được từ khách hàng “C” cũng như từ ai tôi đã nhận được các phiếu ngay trước và sau đó.
Sử dụng Vlookup và Index-Match, chúng ta có thể tìm số chi phiếu nhận từ khách hàng “C” mà tôi đã giải thích với các công thức ở trên. Phần còn lại của vấn đề chúng ta có thể giải quyết với Index-Match.
Hiểu vấn đề trên tốt hơn với hình ảnh chụp màn hình này.
Chúng ta đã có công thức Vlookup và kết hợp Index và Match để trả về ô kế bên phía bên phải của search_key.
Các công thức đó trả về số chi phiếu 271058. Hãy xem cách lấy các giá trị ô kế bên phía trên và phía dưới của số chi này (kết quả Vlookup hoặc Index-Match).
Lấy Giá trị từ Ô Lên phía trên ô Kết quả Vlookup
Phần chính trong kết hợp Index-Match là offset hàng bằng cách sử dụng công thức Match.
Trong ví dụ trước, công thức Match trả về số 3 vì khách hàng “C” có sẵn trong hàng thứ ba của phạm vi.
Trừ đi 1 từ nó (công thức Match) để lấy giá trị ô phía trên của giá trị kết quả Vlookup.
=index(B2:B,match(G1,A2:A,0)-1)Công thức Index và Match này sẽ trả về số chi phiếu 271057 vì công thức lùi 2 hàng (3-1).
Lấy Giá trị từ Ô Xuống phía dưới ô Kết quả Vlookup
Để lấy giá trị từ ô xuống phía dưới ô kết quả Vlookup, sử dụng công thức sau.
=index(B2:B,match(G1,A2:A,0)+1)Kết quả: 912776
Kết luận
Bạn có phải là một người đam mê Vlookup và không muốn sử dụng Index-Match? Hãy tiếp tục đọc.
Chúng ta có thể giải quyết các vấn đề trên (hai vấn đề cuối cùng) mà không cần sử dụng Index và Match!
Làm thế nào?
Hãy xem xét lại công thức Vlookup mà chúng ta đã sử dụng để tìm ô kế bên bên phải của khóa tìm kiếm.
=vlookup(G1,A2:E,2,0)Dùng công thức này như sau,
=cell("address",vlookup(G1,A2:E,2,0))Kết quả: $B$4
Bây giờ chúng ta phải tách $B$ và số 4 từ kết quả trên. Hai công thức sau làm điều đó.
=regexextract(cell("address",vlookup(G1,A2:E,2,0)),"[A-Z$]+")Kết quả: $B$
=regexextract(cell("address",vlookup(G1,A2:E,2,0)),"[d]")Kết quả: 4
Để lấy giá trị từ ô phía trên của giá trị kết quả Vlookup, trừ đi 1 từ công thức REGEXEXTRACT thứ hai và sau đó kết hợp cả hai công thức sử dụng toán tử &.
=regexextract(cell("address",vlookup(G1,A2:E,2,0)),"[A-Z$]+")®exextract(cell("address",vlookup(G1,A2:E,2,0)),"[d]")-1Kết quả: $B$3
Đương nhiên cộng thêm 1 để lấy giá trị từ ô dưới phía dưới của kết quả Vlookup.
=regexextract(cell("address",vlookup(G1,A2:E,2,0)),"[A-Z$]+")®exextract(cell("address",vlookup(G1,A2:E,2,0)),"[d]")+1Kết quả: $B$5
Thực hiện gián trực tiếp công thức nào đó và xem kết quả trả về.
Ví dụ:
=indirect(regexextract(cell("address",vlookup(G1,A2:E,2,0)),"[A-Z$]+")®exextract(cell("address",vlookup(G1,A2:E,2,0)),"[d]")+1)Kết quả: 912776
Đó là tất cả về cách tìm ô kế bên trong Google Sheets.
