Hướng dẫn từ đầu đến cuối về cách sử dụng hàm Vlookup

Trong bài viết này, Học Excel Online sẽ hướng dẫn bạn cụ thể và chi tiết cách thức sử dụng hàm Vlookup một cách hiệu quả.

Định nghĩa cơ bản về Vlookup.

Vlookup là hàm chức năng trong Excel có tác dụng tham chiếu giá trị từ trái sang phải dựa trên giá trị gốc.

Cấu trúc của lệnh như sau: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• Lookup_value: giá trị cần tham chiếu, hay còn gọi là giá trị gốc. Bạn có thể điền bất cứ ký tự chữ hay số nào vào mục này (lưu ý là bạn nên chọn 1 giá trị đặc biệt để cho ra kết quả tham chiếu chính xác nhất)
• Table_array: Là tập hợp bảng dữ liệu nơi bạn muốn thực hiện thao tác tham chiếu để phát hiện dữ liệu cần tìm trong toàn bộ dữ liệu gốc.
• Col_index_num: Số lượng cột tính từ giá trị gốc (giá trị tham chiếu) trong bảng dữ liệu
• [range_lookup]– Ở đây quy định chất lượng tham chiếu, bạn có thể lựa chọn Approximate match để có kết quả tham chiếu chính xác ở mức tương đối, hoặc chọn Exact match để lấy kết quả tham chiếu đúng tuyệt đối (trong nhiều trường hợp phần lớn ta sẽ sử dụng exact match)

Hướng dẫn sử dụng hàm Vlookup căn bản:

Ví dụ #1:

Dưới đây ta có 2 bảng dữ liệu, một bảng chỉ chứa mã quốc gia và một bảng chứa cả mã quốc gia lẫn tên đầy đủ của quốc gia đó.

Bước 1: Ta điền lệnh “=Vlookup” vào ô C2

Bước 2: Điền vào nội dung hàm Vlookup giá trị cần tham chiếu, trong trường hợp này là giá trị “AU” của ô B2 và thêm một dấu phẩy

Bước 3: Điền tiếp tên bảng dữ liệu cần tham chiếu, cụ thể là E:F và thêm một dấu phẩy

Bước 4: Điền số thứ tự của cột cần tham chiếu, cụ thể ở đây ta đang cần tìm tên đầy đủ của quốc gia trong bảng dữ liệu E:F, vì thế cột dữ liệu chứa các tên đó sẽ là cột F, và số thứ tự là 2

Bước 5: Điều kiện tham chiếu. Ở đây ta cần tham chiếu tên chính xác, vì vậy ta sẽ điền là “FALSE”, tương đương với lựa chọn “exact match”

Dưới đây là kết quả

Sau khi hoàn thành các bước nói trên, bạn sẽ thấy kết quả ở ô C2 sẽ xuất hiện cụm từ “Australia”.

Hướng dẫn sử dụng hàm Vlookup nâng cao:

Ví dụ #2:

Trong ví dụ này, ta có 2 bảng dữ liệu khác nhau nhưng có chung nội dung, nhiệm vụ của bạn là phải viết một câu lệnh sử dụng hàm Vlookup để tham chiếu từ cả 2 bảng nói trên sao cho tìm được đúng từ khóa.

Bước 1: Điền câu lệnh của Vlookup theo ví dụ trước đó vào ô B2. Lúc này kết quả trả về ở ô B5 sẽ là #N/A, điều này hoàn toàn bình thường bởi vì từ khóa viết tắt “BE” thực ra nằm ở bảng dữ liệu thứ 2.

Bước 2: Một khi bạn nhìn thấy lỗi #N/A, điều đó chứng tỏ từ khóa “BE” mà bạn cần tìm không có trong bảng dữ liệu 1. Vì vậy bắt buộc ta phải tiếp tục thao tác tham chiếu sang bảng dữ liệu thứ 2.

Bước 3: Điền thêm hàm IFERROR lên trước hàm Vlookup ban đầu để hệ thống tự phát hiện lỗi và đề xuất cách giải quyết

=IFERROR(VLOOKUP(A2,D:E,2,FALSE),0)

Ở đây giả sử nếu hàm Vlookup cho ra kết quả lỗi thì hàm IFERROR sẽ tự động gán giá trị 0 cho kết quả nhận được.

Bước 4: Tiếp theo ta sẽ thay thế chữ số 0 trong hàm IFERROR trên bằng 1 hàm Vlookup khác dùng để tham chiếu sang bảng dữ liệu thứ 2:

=IFERROR(VLOOKUP(A2,D:E,2,FALSE),VLOOKUP(A2,G:H,2,FALSE))

Sau khi bấm Enter, bạn sẽ thấy là các dòng kết quả sẽ hiện tương ứng những tên quốc gia nằm lẫn lộn ở cả 2 bảng dữ liệu

Chẳng hạn, mặc dù từ khóa “BE” và “US” đều nằm ở bảng thứ 2 nhưng nhờ có công thức trên mà ta vẫn có thể tham chiếu được chúng một cách dễ dàng.

Hướng dẫn dùng hàm Vlookup để thực hiện phép toán

Ví dụ #3

Ở đây ta có 2 bảng dữ liệu, bảng đầu tiên thể hiện tên các mặt hàng và doanh số tương ứng của từng mặt hàng, bảng thứ hai là bảng giá của từng mặt hàng.

Để biết được tổng doanh thu của 3 mặt hàng đầu ta chỉ cần nhân doanh số với giá tiền của từng mặt hàng một là xong, nhưng nếu thay vì chỉ có 3 mặt hàng bạn có đến 20 hay 50 mặt hàng thì sao?

Bước 1: Tạo lập 1 bảng mới, liệt kê từng sản phẩm riêng biệt ra cùng 1 cột

Bước 2: Để tham chiếu doanh số hàng được bán ra, bạn có thể sử dụng công thức sau:

=VLOOKUP(B13,B2:C8,2,FALSE)

Bước 3: Sau bước tham chiếu đầu tiên, kết quả tham chiếu sẽ được trình bày thành các ô tách biệt, nhiệm vụ tiếp theo là tạo phép toán nhân bằng cách thêm dấu “*” và sau đó là một lệnh Vlookup tham chiếu giá thành từng mặt hàng một. Cụ thể câu lệnh sẽ như sau:

=VLOOKUP(B13,B3:C8,2,FALSE) *VLOOKUP(B13,E3:F5,2,FALSE)

Kết quả sẽ cho ra doanh thu đạt mức 1000.

Hướng dẫn dùng hàm Vlookup để thực hiện phép toán nâng cao

Vlookup thường chỉ hoạt động hiệu quả nếu như danh sách các mặt hàng là riêng rẽ. Nếu như danh sách chỉ toàn chứa duy nhất 1 mặt hàng thì Vlookup sẽ tự động chỉ tham chiếu ô đầu tiên và bỏ qua tất cả các dòng còn lại.

Ví dụ #4

Ví dụ này sẽ lặp lại ví dụ 3 trước đó, nhưng có 1 chút thay đổi. Thay vì bán các mặt hàng khác nhau, ta sẽ chỉ bán duy nhất 1 mặt hàng qua các ngày khác nhau. Điều đó có nghĩa là một mặt hàng có thể bị lặp đi lặp lại nhiều lần. Chẳng hạn, hôm nay bạn có thể bán Web Hosting, và tiếp tục bán sang ngày mai hoặc ngày kia nữa.

Bước 1: Đầu tiên ta đi tìm tổng doanh số bán hàng của từng sản phẩm, thông qua hàm SUMIF như sau:

=SUMIF(B3:B8,B13,C3:C8)

Kết quả sẽ cho ra là 15. Trong câu lệnh trên

• B3:B8 là dải ô chứa các mặt hàng
• B13 là tiêu chí xét duyệt, ở đây chính là giá trị cần tìm “Web Hosting”
• C3:C8 là dải ô chứa tổng doanh số bán hàng của nó qua từng ngày

Bước 2: Sau khi tính được tổng doanh số bán hàng bất luận có bị trùng lặp về mặt số liệu hay không, tiếp theo ta sẽ xác định giá thành bằng cách sử dụng câu lệnh đơn giản sau:

VLOOKUP(B13,E2:F5,2,FALSE)

Kết quả sẽ cho ra là 100. Trong câu lệnh trên

• Web Hosting là giá trị tham chiếu
• E2:F5 là dải ô chứa các giá trị liên quan đến giá thành sản phẩm
• 2 là số thứ tự cột tham chiếu
• Thiết lập điều kiện xét duyệt là “exact match”

Bước 3: Ta sẽ dụng phép nhân để tính doanh thu cho từng sản phẩm

=SUMIF(B3:B8,B13,C3:C8)* VLOOKUP(B13,E2:F5,2,FALSE)

5 điểm hạn chế của hàm Vlookup

1. Chỉ hỗ trợ tham chiếu từ trái sang phải

Phần nhiều người dùng cho rằng Vlookup chỉ hoạt động theo chiều từ trái sang phải, chứ không có chiều  ngược lại. Một số khác cho rằng để có thể dùng Vlookup tham chiếu từ phải sang thì bắt buộc phải kết hợp nhiều công thức lại với nhau.

Thực tế mà nói, việc kết hợp công thức là một thao tác khá là rắc rối và không hề dễ dàng gì. Vì thế đây vẫn được coi là một hạn chế lớn của Vlookup

Giải pháp: Sử dụng công thức Match & Index

1. Chỉ hoạt động hiệu quả với các giá trị riêng biệt

Vlookup có xu hướng chỉ quan tâm đến giá trị tham chiếu đầu và bỏ quên các dòng khác chứa giá trị tương tự. Nếu như dữ liệu của bạn không có quá nhiều sự phân hóa thì bạn bắt buộc phải lách luật một chút để Vlookup có thể tham chiếu được giá trị bạn cần.

Giải pháp: Tạo một bảng Pivot chứa các giá trị riêng biệt và khi đó bạn có thể dùng hàm Vlookup một cách bình thường

1. Số thứ tự cột tham chiếu luôn cố định

Việc số thứ tự của cột tham chiếu được điền thủ công vào nội dung của công thức khiến cho thao tác sao chép sang ô tính khác gặp nhiều khó khăn và bất tiện.

Giải pháp: Bạn có thể sử dụng số thứ tự biến đổi liên tục thông qua hàm Match

1. Mặc định thiết lập “approximate match”

Nếu để ý kỹ, bạn có thể thấy rằng điều kiện kiểm duyệt tham chiếu ở trong nội dung hàm Vlookup là yếu tố tùy chọn (được ký hiệu bằng hai dấu ngoặc vuông []) nên nếu như bạn không điền nội dung gì vào đó thì công thức sẽ tự mặc định là “approximate match”

Bạn thật sự cần phải chú ý nếu như không muốn kết quả tham chiếu trở nên sai lệch

Giải pháp: Luôn chú ý phải thiết lập là “exact match” để có kết quả tham chiếu chính xác nhất, tuy nhiên nếu bạn vẫn muốn chọn thiết lập mặc định “approximate match” thì phải thật sự cẩn thận

1. Làm tốc độ phản hồi của bảng tính bị chậm đi

Một vài ý kiến cho rằng việc sử dụng quá nhiều hàm Vlookup để tham chiếu sẽ làm giảm hiệu năng của chương trình, thậm chí gây ra hiện tượng crash.

Giải pháp: Bạn không bị giới hạn số lượng hàm Vlookup cần dùng, tuy nhiên cố gắng thay thế chúng bằng lệnh Paste Special để thay thế các hàm bằng giá trị cố định, điều đó sẽ giúp chương trình hoạt động hiệu quả hơn.