Trong bài viết mới này, bạn sẽ học cách sử dụng Vlookup để tìm giá trị cuối cùng không trống trong một hàng trong Google Sheets.
Khác biệt giữa VLOOKUP thông thường và công thức này
Thông thường, chúng ta cần chỉ định một ô trong hàng tìm kiếm để trả kết quả. Đó được gọi là chỉ mục (vị trí tương đối của cột kết quả) trong thuật ngữ của Vlookup.
Ví dụ, chúng ta có thể sử dụng công thức Vlookup sau trong Google Sheets để tìm tên “Prashant” trong A2: E4 và trả một giá trị từ cột thứ hai từ hàng tìm thấy.
= vlookup ("Prashant", A2: E4, **2**, 0)
Trong công thức Vlookup này, số chỉ mục (ô chỉ định để nhận kết quả trong hàng) là 2. Vì vậy, chúng ta sẽ nhận được giá trị 10 nằm trong ô B3.
Cú pháp: VLOOKUP (search_key, range, index, [is_sorted])
Vậy làm thế nào để sử dụng Vlookup để lấy giá trị cuối cùng không trống trong hàng tìm thấy trong Google Sheets?
Bạn có thể làm điều đó bằng cách sử dụng nhiều sự kết hợp mà thường liên quan đến Query, Filter, Index, v.v.
Công thức mà tôi sắp viết cũng là một sự kết hợp.
Nó bao gồm các hàm Vlookup, Query, Split, Flatten và Column.
Lợi ích của việc sử dụng công thức của tôi là, nó giúp bạn tìm kiếm nhiều tiêu chí trong cột đầu tiên và nhận kết quả mảng theo hàng.
Ý nghĩa là, tôi có một công thức mảng Vlookup để lấy các giá trị cuối cùng không trống trong hàng trong Google Sheets.
Công thức mảng Vlookup để lấy các giá trị cuối cùng không trống trong hàng
Vì Vlookup trong Google Sheets không có tính năng trả về giá trị từ ô cuối cùng không trống trong hàng tìm thấy, chúng ta nên làm một workaround.
Ý nghĩa là, chúng ta cần xử lý trước dãy mảng / Vlookup một cách cụ thể.
Logic công thức theo Dữ liệu mẫu
Chúng ta nên sửa đổi dữ liệu trong A2: E4 thành một mảng ba cột theo một cách cụ thể.
Lưu ý: – Chúng ta có thể sử dụng một công thức đơn giản cho điều đó (Tôi sẽ nói đến phần công thức lõi sau).
- Trong mảng đã sửa đổi, cột đầu tiên sẽ chứa các tên “Ben”, “Prashant” và “Maria”.
- Cột thứ hai sẽ chứa số cột của các giá trị trong B2: E4.
- Cột thứ ba sẽ chứa các giá trị trong B2: E4.
Bảng ba cột sẽ được sắp xếp theo thứ tự giảm dần dựa trên các số cột trong cột thứ hai.
Giải thích cho các điểm từ 1 đến 3 ở trên:
Nếu chúng ta sắp xếp dữ liệu theo cách đó bằng một công thức, hàng thứ ba của dữ liệu mẫu trong phạm vi A4: E4 tương ứng với “Maria” sẽ tương tự như bảng dưới đây.
Từ đó, chúng ta phải lọc ra các ô trống trong cột thứ ba.
Vì vậy, nếu chúng ta Vlookup “Maria” và nhận giá trị từ cột thứ ba, chúng ta sẽ nhận giá trị 1.
Bạn có thể cuộn lên và nhìn vào bản ghi của cô ấy trên hình ảnh.
Bạn sẽ hiểu giá trị được trả về bởi Vlookup là từ ô không trống cuối cùng.
Việc xử lý dữ liệu như trên không quá phức tạp.
Bạn có thể sử dụng phương pháp Unpivot của tôi (kết hợp + tách + làm phẳng) cho điều đó.
Unpivot dải dữ liệu cho Vlookup
Để unpivot, chúng ta nên kết hợp phạm vi theo một cách cụ thể, làm phẳng, sau đó tách.
Hãy xem phần đậm trong công thức sau là unpivoting:
Unpivot_and_Format_Formula:
=ArrayFormula (Truy vấn (split (flatten (A2: A4 & "|" & column (B1: E1) & "|" & B2: E4), "|"), "Chọn * nơi Col3 không null sắp xếp theo Col1 tăng dần, Col2 giảm dần", 0))
Câu lệnh QUERY trong phần bên ngoài thực hiện các hành động định dạng dữ liệu sau đây.
- Lọc ra các ô trống trong cột thứ ba.
- Sắp xếp cột đầu tiên theo thứ tự tăng dần (A-Z) và sau đó cột thứ hai theo thứ tự giảm dần (lớn nhất đến nhỏ nhất).
Cột thứ hai chứa dữ liệu, không phải là phần của phạm vi Vlookup gốc của chúng tôi, tức là A2: E4.
Như tôi đã đề cập ở trên, đó là các số cột của các giá trị không trống trong B2: E4. Nó chỉ để mục đích sắp xếp.
Bằng cách sử dụng Query, chúng ta đã sắp xếp dữ liệu giảm dần dựa trên cột này.
Nó giúp chúng tôi Vlookup trong phạm vi ba cột này để lấy giá trị cuối cùng không trống trong hàng trong Google Sheets.
Công thức Vlookup để lấy giá trị cuối cùng không trống trong một hàng trong Google Sheets
Bạn có thể tuân thủ công thức chung dưới đây:
=Vlookup (search_key, **Unpivot_and_Format_Formula,** 3,0)
Thay thế Unpivot_and_Format_Formula bằng công thức tương ứng và search_key bằng “Prashant” để lấy giá trị cuối cùng không trống trong hàng số 2 trong phạm vi A2: E4, tức là 20.
Muốn sử dụng nhiều tiêu chí?
Đây là một ví dụ (phần đậm là Unpivot_and_Format_Formula trên đây).
Các khóa tìm kiếm đặt trong G2: G4.
=ArrayFormula (Vlookup (G2: G4, **Query (split (flatten (A2: A4 & "|" & column (B1: E1) & "|" & B2: E4), "|"), "Chọn * nơi Col3 không null sắp xếp theo Col1 tăng dần, Col2 giảm dần",0)**, 3,0))
Bảng Ví dụ 100921
Tài liệu liên quan:
- Cách bỏ qua các ô trống trong Vlookup trong Google Sheets
- Di chuyển cột chỉ mục nếu trống trong Vlookup trong Google Sheets
- Tìm sự xuất hiện cuối cùng của nhiều tiêu chí trong Google Sheets
- Vlookup từ dưới lên trên trong Google Sheets
- Vlookup cuối cùng trong mỗi nhóm trong Google Sheets
- Làm thế nào để tìm kiếm các giá trị đầu tiên và cuối cùng trong một hàng trong Google Sheets
- 4 Công thức để tra cứu hàng cuối cùng và kết quả mảng trong Google Sheets
- Cách lấy kết quả LOOKUP từ một cột động trong Google sheets
- Lọc Các cột kết quả Vlookup trong Google Sheets (Ví dụ Công thức)
- Tìm cột cuối cùng không trống trong một hàng trong Google Sheets
- Google Sheets: Làm thế nào để trả lại giá trị không trống đầu tiên trong một hàng hoặc cột
- Tìm cột cuối cùng không trống trong một hàng trong Google Sheets
- Số lượng từ ô không trống đầu tiên đến ô không trống cuối cùng trong một hàng trong Google Sheets
