Cách sử dụng hàm Vlookup trong Excel

Vlookup là một trong những hàm Excel hữu ích nhất nhưng lại ít người hiểu về nó. Trong bài viết này, chúng tôi sẽ giúp các bạn hiểu rõ về hàm Vlookup bằng các ví dụ thực tế, một hóa đơn mẫu của một công ty bất kỳ (công ty A).

• Đây là những hàm cơ bản nhất trong Excel mà bạn cần nắm rõ

Hàm Vlookup là gì?

Hàm Vlookup là một hàm trong Excel. Tôi giả định rằng bạn đã có kiến thức nền về Excel và có thể sử dụng những hàm cơ bản như SUM, AVERAGE, và TODAY. Một trong những chức năng thông dụng nhất của Vlookup là một hàm dữ liệu, nghĩa là nó sẽ hoạt động dựa trên những bảng cơ sở dữ liệu hoặc đơn giản hơn là danh sách các hạng mục. Danh sách có rất nhiều loại. Bạn có thể lập bảng về nhân sự trong công ty, các loại mặt hàng sản phẩm, danh sách khách hàng, hoặc bất kỳ cái gì cũng được. Và dưới đây là danh sách những sản phẩm của công ty A đang bán trên thị trường:

Bảng cơ sở dữ liệu thường có những dấu hiệu nhận biết cho từng sản phẩm. Ở bảng dữ liệu trên, dấu hiệu nhận biết đặc biệt là cột "Item Code" (Mã sản phẩm). Chú ý: Để có thể sử dụng hàm Vlookup thì bảng dữ liệu phải có cột chứa dấu hiệu nhận biết như mã hoặc ID, và buộc phải là cột đầu tiên như bảng trên.

Sử dụng hàm Vlookup để làm gì?

Trước tiên chúng ta cần tìm câu trả lời chính xác cho câu hỏi "chúng ta sử dụng hàm Vlookup để làm gì?".

Hàm Vlookup được sử dụng để hỗ trợ tra cứu thông tin trong một trường dữ liệu hoặc danh sách dựa vào những mã định danh có sẵn.

Ví dụ, chèn hàm Vlookup kèm theo mã sản phẩm vào một bảng tính khác, nó sẽ hiển thị thông tin của sản phẩm tương ứng với mã đó. Những thông tin đó có thể là mô tả, giá thành, số lượng tồn kho, tùy theo nội dung công thức mà bạn viết.

Lượng thông tin cần tìm càng nhỏ thì khi viết hàm Vlookup sẽ càng khó khăn hơn. Thông thường bạn sẽ sử dụng hàm này vào một bảng tính tái sử dụng như mẫu. Mỗi lần nhập mã sản phẩm thích hợp, hệ thống sẽ truy xuất tất cả những thông tin cần thiết về sản phẩm tương ứng.

Cách sử dụng hàm Vlookup

Công thức của hàm Vlookup

Hàm Vlookup trong Excel có công thức như sau:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Trong đó:

• VLOOKUP: Là tên hàm
• Các tham số in đậm bắt buộc phải có.
• lookup_value: Giá trị dùng để dò tìm
• table_array: Bảng chứa giá trị cần dò tìm, để ở dạng giá trị tuyệt đối với dấu $ đằng trước, ví dụ: $A$3:$E$40.
• col_index_num: Thứ tự của cột chứa giá trị dò tìm trên table_array. Ví dụ trong bảng $A$3:$E$40, cột B chứa giá trị cần dò tìm thì col_index_num ở đây sẽ là 2.
• rang_lookup: Là phạm vi tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối). Tham số này không bắt buộc phải luôn có trong công thức.

Dò tìm tương đối chỉ có thể áp dụng khi giá trị cần dò tìm trong table_array đã được sắp xếp theo thứ tự (tăng dần hoặc giảm dần hay theo bảng chữ cái). Với những bảng như vậy bạn có thể dùng dò tìm tương đối, khi đó nó tương tự như dùng hàm IF vô hạn vậy. Đối với các giá trị cần dò tìm không thể hoặc chưa được sắp xếp hãy dùng dò tìm tuyệt đối để tìm chính xác giá trị. 

Ví dụ về hàm Vlook up

Hãy lấy hóa đơn mẫu của công ty A làm ví dụ.

Đầu tiên, bật Excel và tạo ra một bảng hóa đơn trống:

"Hóa đơn này sử dụng như thế nào?" Người dùng sẽ điền mã sản phẩm vào cột A, sau đó hệ thống có nhiệm vụ truy tìm các thông tin của mục mô tả và giá thành theo từng mã sản phẩm trong bảng dữ liệu để tính ra tổng.

Đơn giản hơn, chúng ta sẽ đặt dữ liệu sản phẩm sang trang khác trong cùng workbook:

Thực tế, dù cơ sở dữ liệu và hóa đơn thường đặt ở hai workbook riêng biệt, việc này hầu như không ảnh hưởng tới chức năng của hàm Vlookup. Chúng ta có thể đặt cơ sở dữ liệu tùy ý, có thể là đặt cùng trang, khác trang hoặc thậm chí khác workbook.

Vì vậy, chúng tôi đã tạo ra bảng dữ liệu sản phẩm như sau:

Để lập công thức cho hàm Vlookup, đầu tiên nhập một mã sản phẩm vào A11 vào hóa đơn trống.

Bước thứ hai, di chuyển sang vị trí chúng ta muốn lưu thông tin bởi hàm Vlookup. Nên nhớ rằng hàm Vlookup truy tìm những miêu tả tương ứng với mã sản phẩm ở cột A11. Nhưng mục mô tả lại ở B11. Vậy sẽ phải viết công thức tại B11.

Chúng ta cần tìm vị trị chứa các hàm trong Excel để lựa chọn hàm Vlookup và hoàn thành công thức nhờ các gợi ý. Trước tiên, click vào Formulas trên thanh công cụ, tiếp theo chọn Insert Function.

Lập tức một cửa sổ mới hiện ra và cho phép chọn bất kỳ hàm nào có trong Excel.

Trong phần Search chúng ta gõ "lookup" bởi hàm chúng ta đang cần hàm có chức năng tìm kiếm. Hệ thống sẽ hiện những hàm có liên quan đến chức năng này. Lựa chọn hàm Vlookup ở dòng thứ 2 và ấn OK.

Cửa sổ Function Arguments sẽ xuất hiện giúp chúng ta biết những tham số sẽ cần cho hàm Vlookup.

Ba tham số đầu tiên được bôi đen là những tham số bắt buộc (công thức sẽ bị lỗi khi thiếu những tham số đó). Tham số cuối cùng không được bôi đen là tham số không bắt buộc.

Tham số đầu tiên chúng ta cần điền là Lookup_value. Tại đây, chúng ta sẽ điền mã danh tính (trong trường hợp này là mã sản phẩm) mà chúng ta cần truy xuất thông tin từ cơ sở dữ liệu . Chọn mã sản phẩm chúng ta vừa điền.

Click vào biểu tượng lựa chọn ở góc bên phải của tham số đầu tiên:

Tiếp theo, kích ô có chứa mã sản phẩm (A11), rồi enter.

Giá trị của "A11" đã được chèn vào tham số đầu tiên.

Tiếp tục, nhập giá trị cho tham số thứ hai Table_array. Tham số này sẽ tìm nơi chứa cơ sở dữ liệu. Click vào biểu tượng lựa chọn ở góc bên phải của tham số thứ hai:

Bây giờ xác định vị trí của dữ liệu và chọn toàn bộ danh sách ngoại trừ dòng đầu tiên. Trong trường hợp này, cơ sở dữ liệu được đặt ở một sheet khác, vì vậy chúng ta chọn vào tab worksheet.

Tiếp theo, chọn toàn bộ dữ liệu không bao gồm dòng đầu tiên và ấn enter.

Trình tự của dữ liệu sẽ được tự động nhập nhờ vào tham số thứ hai.

Tiếp sau đó, chúng ta chọn tham số thứ ba Col_index_num. Tham số này chỉ rõ những phần thông tin cần lọc từ mã sản phẩm ở ô A11 trong bảng dữ liệu. Ví dụ như, chúng ta muốn có miểu tả của sản phẩm cần tìm nó nằm ở cột thứ hai thì sẽ nhập giá trị "2" vào hộp Col_index_num.

Điều quan trọng cần chú ý là chúng ta điền "2" ở đây bởi nó nằm cột thứ hai trong Table_array chứ không phải do nó ở cột B trong worksheet.

Cuối cùng là nhập giá trị cho tham số Range_lookup. Tham số này yêu cầu giá trị đúng sai, hoặc có thể bỏ trống. Khi sử dụng hàm Vlookup cho các cơ sở dữ liệu (gần như sẽ đúng 90%), cách để quyết định lựa chọn tham số sẽ phải dựa theo quy tắc sau:

Nếu cột đầu tiền của dữa liệu (cột có chứa dấu hiệu nhận biết) là loại chữ hoặc số theo thứ, thì chúng ta có thể chọn giá trị đúng True cho tham số này.

Còn nếu ở cột đầu tiên không phân loại được, hoặc sắp xếp ngẫu nhiên, chúng ta phải để giá trị là sai False.

Sau khi đã nhập toàn bộ thông tin cho hàm Vlookup. Chọn Ok và chú ý mô tả tương ứng của mã sản phầm "R99245" sẽ được điền vào.

Công thức chúng ta có sẽ như sau:

Nếu nhập một mã sản phầm khác vào A11 thì hàm Vlookup tự động thay đổi mô tả tương đồng với mã sản phẩm mới đó.

Chúng ta có thể làm tương tự như vậy đối với cột giá trị ở E11. Công thức mới phải được tạo ở ô E11. Kết quả sẽ ra như sau:

Và công thức sẽ là:

 

Sự khác biệt giữa hai công thức là tham số Col_index_num cần sẽ điền 3 không phải 2 như trước nữa, bởi chúng ta muốn truy xuất vào cột thứ 3 trong bảng dữ liệu.

Nếu quyết định mua hai trong những sản phẩm, chúng ta sẽ điền 2 vào D11 (Số lượng). Sau đó nhập công thức đơn giản như sau =D11*E11 để có được kết quả của mục tổng.

Mục tổng sẽ kết quả:

Hoàn thành mẫu hóa đơn

Cho tới thời điểm này, bài báo đã bao quát được khá nhiều kiến thức về hàm Vlookup. Còn một điều đáng chú ý nữa là hàm Vlookup còn có thể sử dụng cho nhiều trường hợp khác.

Để hoàn thành mẫu hóa đơn, chúng ta cần phải sửa nó thành mẫu hóa đơn trắng.

1. Chúng ta xóa nội dung trong ô A11(Mã sản phẩm) và D11(số lượng). Việc này sẽ khiến công thức hàm Vlookup của chúng ta hiển thị thông báo lỗi. Nhưng đừng lo!

Chúng ta có thể sử dụng hàm IF và hàm ISBLANK của Excel để khắc phục. Chúng ta sửa hàm Vlookup từ: VLOOKUP(A11,'Product Database'!A2:D7,2,FALSE) thành IF(ISBLANK(A11),"",VLOOKUP(A11,'Product Database'!A2:D7,2,FALSE)).

2. Chúng ta sao chép công thức trong B11, E11, F11 xuống các ô tương ứng phía dưới. Lưu ý rằng nếu chúng ta làm như vậy, công thức sẽ bị lỗi khi tham chiếu với bảng cơ sở dữ liệu. Có một cách hay hơn đó là tạo một bảng tên sản phẩm (đặt tên là "Products" và sử dụng bảng đến đó thay vì các dòng tham chiếu. Công thức sẽ thay đổi: =IF(ISBLANK(A11),"",VLOOKUP(A11,'Product Database'!A2:D7,2,FALSE)) sang =IF(ISBLANK(A11),"",VLOOKUP(A11,Products,2,FALSE)).

3. Và sau cùng là copy công thức xuống các dòng tương ứng, chúng ta nên khóa các dòng có chứa những công thức Vlookup, và bật chế độ bảo vệ các bảng tính để tránh trường hợp các đồng nghiệp trong quá trình điền thông tin vào hóa đơn vô tình viết đè vào những hàm Vlookup mà chúng ta vừa "hì hục" thiết lập. Hơn nữa, lưu File này như một file mẫu để nhân viên trong công ty có thể tái sử dụng nó. Đơn giản hơn, chúng ta có thể tạo cơ sở dữ liệu của khách hàng sang một bảng tính khác. Sau đó sử dụng mã khách hàng ở ô F5 để hệ thống tự động truy xuất thông tin về khách hàng tương ứng vào các ô B6, B7, B8