HÀM VLOOKUP TRONG GOOGLE TRANG TÍNH VÀ CÁC VÍ DỤ

Bài viết dưới đây, Học Excel Online sẽ giải thích hàm VLOOKUP trong Google trang tính và hướng dẫn ứng ứng dụng ví dụ hàm VLOOKUP để giải quyết các nhiệm vụ thực tế. 

Khi xử lý các dữ liệu liên quan đến nhau, một trong những khó khăn phổ biến nhất là tìm kiếm thông tin trên nhiều trang tính. Trong cuộc sống hàng ngày bạn cũng sẽ thực hiện những công việc như quét bảng lịch trình chuyến bay để biết thời gian khởi hành và trạng thái khởi hành. Google Sheet Vlookup hoạt động tương tự như vậy – tra cứu và truy xuất dữ liệu phù hợp từ một bảng khác trên cùng một trang tính hoặc từ một trang tính khác.

Hầu hết mọi người cho rằng hàm Vlookup là hàm khó và ít người biết đến. Nhưng trên thực tế rất dễ dàng thực hiện hàm Vlookup trong Google trang tính và bài viết dưới đây sẽ giúp bạn làm điều đó.

Google Sheets Vlookup – cú pháp và cách sử dụng

Hàm VLOOKUP trong Google Sheets được thiết kế thực hiện tra cứu theo chiều dọc (vertical lookup). Vlookup có chức năng tìm kiếm giá trị khóa (mã định danh duy nhất) xuống cột đầu tiên trong phạm vi được chỉ định và trả về một giá trị trong cùng một hàng từ cột khác.

Cú pháp cho hàm Vlookup của Google Trang tính như sau:

VLOOKUP(search_key, range, index, [is_sorted])

3 đối số đầu tiên là bắt buộc, đối số cuối cùng là tùy chọn:

Search_key – là giá trị cần tìm kiếm (giá trị tra cứu hoặc mã định danh duy nhất). Ví dụ: bạn có thể tìm kiếm từ “apple”, số 10 hay là giá trị trong ô A2.

Range (phạm vi): hai hoặc nhiều cột dữ liệu cho tìm kiếm. Hàm Vlookup của google trang tính luôn tìm kiếm trong cột đầu tiên của Range.

Index: số thứ tự cột trong phạm vi mà từ đó giá trị phù hợp (giá trị trong cùng hàng với Search_ key) sẽ được trả về.

Cột đầu tiên trong Range và có Index 1. Nếu Index nhỏ hơn 1, công thức Vlookup trả về giá trị lỗi #VALUE! Lỗi. Nếu nó lớn số cột trong phạm vi, hàm VLOOKUP trả về giá trị lỗi #REF! lỗi.

Is_Sorted: cho biết cột tra cứu được sắp xếp (TRUE) hay (False). Trong hầu hết các trường hợp thì FALSE được khuyến nghị.

• Nếu is_sorted là TRUE hoặc bị bỏ qua (mặc định), cột đầu tiên của phạm vi phải được sắp xếp theo thứ tự tăng dần, tức là từ A đến Z hoăc từ nhỏ nhất đến lớn nhất.
• Trong trường hợp này, công thức Vlookup trả về một kết quả gần đúng (tương đối) . Chính xác hơn, nó tìm kiếm kết quả phù hợp chính xác trước tiên. Nếu không tìm thấy đối sánh chính xác, công thức sẽ tìm kiếm đối sánh gần nhất nhỏ hơn hoặc bằng Search_ key . Nếu tất cả các giá trị trong cột tra cứu lớn hơn khóa tìm kiếm, thì sẽ trả về lỗi # N / A.
• Nếu is_sorted được đặt thành FALSE, không cần sắp xếp. Trong trường hợp này, công thức Vlookup tìm kiếm kết quả phù hợp chính xác . Nếu cột tra cứu chứa 2 hoặc nhiều giá trị chính xác bằng khóa tìm kiếm, giá trị đầu tiên tìm thấy sẽ được trả về.

Thoạt nhìn, cú pháp có vẻ hơi phức tạp, nhưng ví dụ về công thức Vlookup của Google Trang tính dưới đây sẽ giúp mọi thứ dễ hiểu hơn.

Giả sử bạn có hai bảng: bảng chính và bảng tra cứu như trong ảnh chụp màn hình bên dưới. Các bảng có một cột chung (order ID) là một mã định danh duy nhất. Mục đích là kéo trạng thái của từng đơn hàng từ bảng tra cứu sang bảng chính.

Bây giờ, làm thế nào để sử dụng Google Trang tính Vlookup để hoàn thành nhiệm vụ? Để bắt đầu, hãy xác định các đối số cho công thức Vlookup:

• Search_key – ID đơn hàng (A3), giá trị được tìm kiếm trong cột đầu tiên của bảng Tra cứu.
• Range (Vùng dữ liệu) – bảng Tra cứu ($F$3:$G$8). Lưu ý khóa phạm vi bằng cách sử dụng tham chiếu ô tuyệt đối nếu dự định sao chép công thức sang nhiều ô.
• Index: Index – 2 từ cột Status sẽ  trả về kết quả khớp là cột thứ 2 trong phạm vi.
•  Is_sorted:  FALSE vì cột tìm kiếm của chúng ta (F) không được sắp xếp.

Đặt tất cả các đối số lại với nhau, chúng ta nhận được công thức này:

=VLOOKUP(A3,$F$3:$G$8,2,false)

Nhập nó vào ô đầu tiên (D3) của bảng chính, sao chép xuống cột và bạn sẽ nhận được kết quả tương tự như sau:

Công thức Vlookup có còn khó hiểu đối với bạn không? Sau đó, hãy nhìn nó theo cách này:

5 điều cần biết về Google Sheets VLOOKUP

Để tránh gặp rắc rối và hạn chế những lỗi có thể mắc phải trong Vlookup thì nên ghi nhớ năm điều đơn giản sau đây:

1. Google Sheets VLOOKUP không thể lấy dữ liệu bên trái của nó, nó luôn tìm kiếm ở cột đầu tiên (ngoài cùng bên trái) của phạm vi. Để thực hiện một Vlookup bên trái , hãy sử dụng công thức Index & Match của Google Trang tính .
2. VLOOKUP trong Google Sheets là case-insensitive, có nghĩa là nó không phân biệt chữ thường và chữ in hoa. Để tra cứu phân biệt chữ hoa chữ thường , hãy sử dụng công thức trên.
3. Nếu hàm VLOOKUP trả về kết quả không chính xác, hãy đặt đối số is_sorted thành FALSE để trả về kết quả phù hợp chính xác.
4. Khi is_sorted được đặt thành TRUE hoặc bị bỏ qua, bạn nên nhớ sắp xếp cột đầu tiên của phạm vi theo thứ tự tăng dần. Trong trường hợp này, hàm VLOOKUP sẽ sử dụng thuật toán tìm kiếm nhị phân nhanh hơn và hoạt động chính xác dựa trên dữ liệu được sắp xếp.
5. Google Sheets VLOOKUP có thể tìm kiếm khớp một phần (partial match) dựa trên các ký tự đại diện : dấu hỏi (?) và dấu hoa thị (*). Vui lòng xem ví dụ về công thức Vlookup dưới đây để biết thêm chi tiết.

Cách sử dụng hàm VLOOKUP trong Google Trang tính – ví dụ về công thức

Với những kiến thức cơ bản về cách hoạt động của Vlookup trên đây, bây giờ chúng ta sẽ thử tạo một vài công thức. Để làm theo các ví dụ Vlookup dưới đây dễ dàng và hiểu rõ hơn, bạn có thể mở trang tính Vlookup để làm theo.

Cách tham chiếu Vlookup từ một trang tính khác

Trong thực tế, bảng chính và bảng tra cứu thường nằm trên các trang tính khác nhau. Để tham chiếu công thức Vlookup đến một trang tính khác trong cùng một bảng tính, hãy đặt tên bảng tính theo sau là dấu chấm than (!) trước tham chiếu phạm vi. Ví dụ:

=VLOOKUP(A2,Sheet4!$A$2:$B$7,2,false)

Công thức sẽ tìm kiếm giá trị trong A2 trong phạm vi A2: A7 trên trang tính 4 và trả về giá trị khớp từ cột B ( cột thứ 2 trong phạm vi ).

Nếu tên trang tính bao gồm khoảng trắng hoặc các ký tự không phải chữ cái, hãy đảm bảo đặt nó trong dấu ngoặc kép. Ví dụ:

=VLOOKUP(A2,’Lookup table’!$A$2:$B$7,2,false)

Chú ý: Thay vì nhập tham chiếu đến một trang tính khác theo cách thủ công, bạn có thể sử dụng Google sheet để chèn nó một cách tự động. Để làm điều này, bắt đầu nhập công thức Vlookup của bạn và khi đến đối số phạm vi(range) thì chuyển sang trang tính tra cứu và chọn phạm vi bằng chuột. Thao tác này sẽ thêm tham chiếu phạm vi vào công thức và bạn sẽ chỉ phải thay đổi tham chiếu tương đối (mặc định) thành tham chiếu tuyệt đối . Để thực hiện thao tác này, bạn nhập dấu $ trước ký tự cột và số hàng, hoặc chọn tham chiếu và nhấn F4 để chuyển đổi giữa các loại tham chiếu khác nhau.

Google Trang tính Vlookup với các ký tự đại diện

Trong trường hợp bạn không biết toàn bộ giá trị tra cứu (search_ key), và bạn chỉ biết một phần của giá trị thì có thể thực hiện tra cứu với các ký tự đại diện sau:

–          Dấu chấm hỏi (?) Để khớp với bất kỳ ký tự đơn .

–          Dấu hoa thị (*) để khớp với bất kỳ chuỗi ký tự chuỗi .

Giả sử bạn muốn truy xuất thông tin về một đơn đặt hàng cụ thể từ bảng bên dưới. Bạn không thể nhớ lại đầy đủ ID thứ tự, nhưng bạn nhớ rằng ký tự đầu tiên là “A”. Vì vậy, bạn sử dụng dấu hoa thị (*) để điền vào phần còn thiếu, như sau:

=VLOOKUP(“a*”,$A$2:$C$7,2,false)

Chúng ta có thể nhập phần đã biết của khóa tìm kiếm vào một số ô và nối chúng với “*” để tạo công thức Vlookup linh hoạt hơn:

–          Để kéo mục: =VLOOKUP($F$1&”*”,$A$2:$C$7,2,false).

–          Để kéo số tiền: =VLOOKUP($F$1&”*”,$A$2:$C$7,3,false).

Chú ý: Nếu bạn cần tìm kiếm một dấu chấm hỏi hoặc ký tự dấu hoa thị, hãy đặt dấu ngã (~) trước ký tự, ví dụ: “~ *”.

Công thức Index Match trong Google Trang tính thay cho Vlookup bên trái

Một trong những hạn chế đáng kể nhất của hàm VLOOKUP (cả trong Excel và Google Trang tính) là nó không thể tìm kiếm dữ liệu bên trái. Tức là, nếu cột tìm kiếm không phải là cột đầu tiên trong bảng tra cứu, Google Sheets Vlookup sẽ bị lỗi. Trong những tình huống như vậy, hãy sử dụng công thức Index Match:

INDEX ( return_range , MATCH ( search_key , lookup_range , 0))

Ví dụ: để tra cứu giá trị A3 (phím tìm kiếm) trong G3: G8 (lookup_range) và trả về kết quả khớp từ F3: F8 (return_range), chúng ta có thể sử dụng công thức sau:

=INDEX($F$3:$F$8, MATCH (A3, $G$3:$G$8, 0))

Kết quả của công thức này như sau:

Một ưu điểm khác của công thức này so với Vlookup là nó không bị tác động bởi những thay đổi cấu trúc bạn thực hiện trong trang tính vì nó tham chiếu trực tiếp đến cột trả về. Đặc biệt, việc chèn hoặc xóa một cột trong bảng tra cứu sẽ phá vỡ công thức Vlookup vì số chỉ mục (Index) “được mã hóa cứng”không hợp lệ, trong khi công thức trên vẫn an toàn và hợp lý.

Để biết thêm thông tin về INDEX MATCH, vui lòng xem Tại sao INDEX & MATCH là giải pháp thay thế tốt hơn cho VLOOKUP . Mặc dù hướng dẫn ở trên nhắm mục tiêu đến Excel, INDEX MATCH trong Google Trang tính hoạt động theo cùng một cách, ngoại trừ các tên khác nhau của các đối số.

Vlookup phân biệt chữ hoa chữ thường trong Google Trang tính

Trong trường hợp văn bản quan trọng, hãy sử dụng hàm INDEX MATCH kết hợp với các hàm TRUE và EXACT để tạo công thức mảng Vlookup phân biệt chữ hoa chữ thường :

ArrayFormula (INDEX ( return_range , MATCH (TRUE, EXACT ( lookup_range , search_key ), 0)))

Giả sử khóa tìm kiếm nằm trong ô A3, phạm vi tra cứu là G3: G8 và phạm vi trả về là F3: F8, công thức như sau:

=ArrayFormula(INDEX($F$3:$F$8, MATCH (TRUE,EXACT($G$3:$G$8, A3),0))) 

Như được hiển thị trong ảnh chụp màn hình bên dưới, công thức không có vấn đề gì với việc phân biệt các ký tự viết hoa và viết thường như A-1001 và a-1001:

Chú ý:  Nhấn Ctrl + Shift + Enter sẽ tự động chèn hàm ARRAYFORMULA vào đầu công thức.

Công thức Vlookup là cách phổ biến nhất nhưng không phải là cách duy nhất để tra cứu trong Google Trang tính. Phần tiếp theo và phần cuối cùng của hướng dẫn này trình bày một giải pháp thay thế.

Hợp nhất Trang tính (Merge Sheets): thay thế không cần công thức cho Google Trang tính Vlookup

Nếu bạn đang tìm kiếm cách khác để hợp nhất trang tính mà không cần công thức Vlookup thì hãy cân nhắc sử dụng tiện ích bổ sung Merge Sheets. Bạn có thể tải miễn phí từ cửa hàng tiện ích bổ sung của Google Trang tính .

Sau khi tiện ích bổ sung được thêm vào Google Trang tính của bạn, bạn có thể tìm thấy nó trong tab Tiện ích bổ sung :

Bây giờ chúng ta sẽ thử nghiệm với ví dụ ban đầu: chúng ta sẽ lấy thông tin từ cột Trạng thái dựa trên ID đơn đặt hàng :

1. Chọn bất kỳ ô nào có dữ liệu trong Trang tính chính và nhấp vào Tiện ích bổ sung > Hợp nhất Trang tính > Bắt đầu .

Trong hầu hết các trường hợp, tiện ích bổ sung sẽ tự động chọn toàn bộ bảng cho bạn. Nếu không, bạn có thể nhấp vào nút Tự động chọn hoặc chọn phạm vi trong trang tính chính của bạn theo cách thủ công, sau đó nhấp vào Tiếp theo.

2. Chọn phạm vi trong Bảng tra cứu . Phạm vi không nhất thiết phải có cùng kích thước với phạm vi trong trang tính chính. Trong ví dụ này, bảng tra cứu có nhiều hàng hơn bảng chính 2 hàng.

3. Chọn một hoặc nhiều cột chính (số nhận dạng duy nhất) để so sánh. Vì chúng ta đang so sánh các trang tính theo ID đặt hàng , chúng ta chỉ chọn cột này:

4. Trong Cột tra cứu , hãy chọn (các) cột trong Trang tính tra cứu mà bạn muốn truy xuất dữ liệu. Trong Cột chính, hãy chọn các cột tương ứng trong trang tính chính mà bạn muốn sao chép dữ liệu vào.

Trong ví dụ này, chúng ta đang kéo thông tin từ cột trạng thái trên trang tính tra cứu vào cột trạng thái trên trang tính chính:

5. Theo tùy chọn, hãy chọn một hoặc nhiều hành động bổ sung. Thông thường, bạn muốn thêm các hàng không khớp vào cuối bảng chính, tức là sao chép các hàng chỉ tồn tại trong bảng tra cứu vào cuối bảng chính:

Nhấp vào Hoàn tất , cho phép tiện ích bổ sung Merge Sheets được xử lý trong giây lát và bạn đã sẵn sàng!

Kết hợp nhiều Vlookup (Vlookup multiple matches)  một cách dễ dàng

Kết hợp nhiều Vlookup là một công cụ khác của Google Trang tính để tra cứu nâng cao. Theo giống như tên gọi, nó có thể trả về tất cả các kết quả phù hợp, không chỉ là kết quả đầu tiên như hàm VLOOKUP. Hơn nữa, nó có thể đánh giá nhiều điều kiện, tra cứu theo bất kỳ hướng nào và trả về tất cả hoặc số lượng kết hợp được chỉ định dưới dạng giá trị hoặc công thức .

Cách hoạt động thực tế của tiện ích này như thế nào? Giả sử, một số đơn đặt hàng trong bảng mẫu của chúng tôi chứa một số mục và bạn muốn lấy tất cả các mục của một đơn đặt hàng cụ thể. Một công thức Vlookup không thể làm được điều này, trong khi một hàm QUERY mạnh hơn có thể. Vấn đề là chức năng này yêu cầu kiến ​​thức về ngôn ngữ truy vấn hoặc ít nhất là cú pháp SQL. Bạn không muốn dành nhiều ngày để nghiên cứu điều này? Cài đặt tiện ích bổ sung Multiple VLOOKUP Matches và nhận một công thức hoàn hảo trong vài giây!

Trong Google Trang tính của bạn, nhấp vào Tiện ích bổ sung > Nhiều trận đấu VLOOKUP > Bắt đầu và xác định tiêu chí tra cứu:

1. Chọn phạm vi với dữ liệu của bạn (A1: D9).
2. Chỉ định có bao nhiêu kết quả phù hợp để trả về (tất cả trong trường hợp của chúng tôi).
3. Chọn các cột để trả về dữ liệu ( Mục , Số lượng và Trạng thái ).
4. Đặt một hoặc nhiều điều kiện. Chúng ta muốn lấy thông tin về đầu vào số thứ tự trong F2, vì vậy chúng ta chỉ định cấu hình một điều kiện: ID đơn hàng = F2.
5. Chọn ô trên cùng bên trái cho kết quả.
6. Nhấp vào Xem trước kết quả để đảm bảo rằng bạn sẽ nhận được chính xác những gì bạn đang tìm kiếm.
7. Nếu tất cả đều OK, hãy nhấp vào Chèn công thức hoặc Dán kết quả .

   

Đối với ví dụ này thì sẽ là chọn trả về kết quả phù hợp dưới dạng công thức. Vì vậy, bây giờ chúng ta có thể nhập bất kỳ số thứ tự nào vào F2 và công thức hiển thị trong ảnh chụp màn hình bên dưới sẽ tự động tính toán lại:

Để tìm hiểu thêm về tiện ích bổ sung, hãy truy cập trang chủ VLOOKUP Multiple hoặc tải xuống ngay bây giờ từ G Suite Marketplace.

Trên đây là những cách bạn có thể thực hiện tra cứu Google Trang tính. Hy vọng những thông tin trên sẽ giúp bạn có thêm những kinh nghiệm bổ ích về Google trang tính.