Hàm Vlookup là một hàm quan trọng và có tính ứng dụng cao trong Excel. Tuy nhiên, không phải ai cũng hiểu rõ hàm Vlookup là gì và dùng để làm gì? Bài viết dưới đây sẽ giải thích chi tiết về hàm Vlookup.
Tóm tắt
Hàm VLOOKUP là gì?
Hàm Vlookup là một hàm dò tìm dữ liệu thường dùng trong Excel. Bạn có thể thấy hàm Vlookup Excel 2003 và các phiên bản nâng cấp như: 2007, 2010, 2013, 2016.
Hàm này hoạt động dựa trên các bảng cơ sở dữ liệu hoặc danh sách các hạng mục như: lập bảng về nhân sự, các loại sản phẩm, danh sách khách hàng… Bảng cơ sở dữ liệu thường có dấu hiệu nhận biết của từng sản phẩm như mã hoặc ID thường là cột đầu tiên. Theo ví dụ minh họa dưới đây, dấu hiệu nhận biết là cột “Item Code” (Mã sản phẩm).
Ý nghĩa của hàm Vlookup trong Excel
Hàm Vlookup dùng để tìm kiếm giá trị 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. Hàm Vlookup làm phiếu lương, xếp loại học sinh, thông tin sản phẩm… là các ứng dụng thực tế phổ biến của hàm này.
Ví dụ, chèn hàm VLOOKUP kèm mã sản phẩm vào một bảng tính khác, kết quả trả về là thông tin sản phẩm tương ứng với mã đó. Bao gồm mô tả sản phẩm, giá thành, số lượng tồn kho, tùy theo nội dung công thức của bạn.
Lượng thông tin cần tìm càng nhỏ thì quá trình viết hàm VLOOKUP sẽ càng khó hơn. Thông thường bạn áp dụng hàm này vào một bảng tính tái sử dụng. Mỗi lần nhập mã sản phẩm bất kỳ, hệ thống sẽ truy xuất tất cả thông tin cần thiết về sản phẩm đó.
Cú pháp của hàm Vlookup
Hàm Vlookup sử dụng như thế nào? Theo Microsoft thì cấu trúc hàm Vlookup như sau:
=VLOOKUP(Lookup_value, Table_array, Col_index_ num, [Range_lookup])
Trong đó:
- Lookup_value: Giá trị bạn cần tìm kiếm.
- Table_array: Bảng giới hạn tìm kiếm, bạn cần F4 Fix cố định giá trị để copy công thức tự động.
- Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng tìm kiếm.
- Range_lookup: Là phạm vi tìm kiếm, giá trị Logic (TRUE=1, FALSE=0) quyết định dò tìm chính xác hay dò tìm tương đối với bảng giới hạn.
Nếu Range_lookup = 1 (TRUE): Dò tìm tương đối.
Nếu Range_lookup = 0 (FALSE): Dò tìm chính xác.
Nếu bỏ qua tham số này thì Excel mặc định là Range_lookup = 1. Đây là tham số không bắt buộc phải có trong công thức.
Dò tìm tương đối chỉ áp dụng khi giá trị cần tìm kiếm trong table_array đã được sắp xếp theo thứ tự. Đối với các giá trị cần tìm kiế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 giá trị chính xác.
Ví dụ về hàm Vlookup trong Excel
Giải thích hàm Vlookup bằng các ví dụ minh họa dễ hiểu dưới đây.
Ví dụ 1
Ví dụ 2
Ví dụ 3
Hàm Vlookup hai điều kiện
Hàm Vlookup 2 Điều Kiện Trường Hợp Tạo Cột Phụ
Ví dụ về cách tính sản lượng của 1 sản phẩm bất kỳ trong từng ca. Nếu áp dụng hàm Vlookup có 2 điều kiện để tìm kiếm, hãy thử tạo thêm 1 cột phụ để việc tính toán dễ dàng hơn.
Bước 1: Sử dụng hàm Vlookup 2 điều kiện để tính toán và tạo cột phụ. Theo ví dụ minh họa dưới đây, cột phụ để cột A có giá trị liên kết từ cột B – mã sản phẩm và cột C – Ca.
Nhập công thức: =B6&C6 vào cột A, sau đó Enter để lệnh thực thi.
Bước 2: Hiển thị kết quả như hình bên dưới, kết hợp 2 cột sản phẩm và ca, sau đó bạn áp dụng tương tự cho các dòng bên dưới.
Bước 3: Để tìm kiếm sản lượng của một mã sản phẩm làm ở một ca, ta tạo một bảng truy vấn bên cạnh. Lúc này, bạn phải nhập Mã sản phẩm và ca, chúng ta nhập công thức tại phần sản lượng.
Bước 4: Nhập công thức =Vlookup(G6&G7);$A$6:$D$10;4;0) nếu áp dụng đúng theo số hàng và cột như trong hình dưới đây.
Giải thích
- G6&G7: Giá trị để truy vấn trên bảng và đưa ra kết quả tương ứng
- ;$A$6:$D$10: Khoanh vùng tìm kiếm từ Cột A6 đến D10 (Nhấn F4 để hiện biểu tượng $)
- 4;0: 4 là trả về giá trị theo cột thứ mấy, số 4 tương ứng cột sản lượng và số 0 là giá trị logic True or False.
Số 0 tương ứng với False cho kết quả giá trị tuyệt đối. Còn số 1 thì tương ứng với True cho kết quả tương đối.
Bước 5: Kết quả nhận được là sản lượng 1000 tương ứng với mã sản phẩm là SA làm Ca 1. Bạn có thể đối chiếu bằng cách nhập mã sản phẩm khác và ca khác để so sánh.
Hàm Vlookup có 2 Điều Kiện Trường Hợp Sử Dụng Công Thức Mảng
Công thức mảng là một cách tính nâng cao khá phức tạp trong Excel. Tuy nhiên, bạn có thể áp dụng với hàm Vlookup 2 điều kiện hay hàm Vlookup có nhiều điều kiện.
Bước 1: Tiếp tục sử dụng bảng tính đó nhưng xóa bỏ cột phụ đi. Nhập trực tiếp công thức sau vào phần sản lượng:
=VLOOKUP(G6&G7;CHOOSE({1\2};(B6:B10)&(C6:C10);D6:D10);2;0)
Trong đó:
- G6&G7 là điều kiện tìm kiếm.
- Choose là hàm tìm kiếm giá trị thỏa mãn điều kiện trong một chuỗi.
- 2 là giá trị trả về ở cột kết quả sản lượng và 0 là giá trị tuyệt đối False.
Nhập công thức vào mục Sản lượng
Bước 2: Sau khi bạn nhập công thức và nhấn tổ hợp phím Ctrl + Shift + Enter để tính công thức mảng, kết quả hiển thị như hình bên dưới.
Ưu điểm của việc sử dụng hàm Vlookup có điều kiện tính công thức mảng là không làm tăng lượng dữ liệu và tìm kiếm không cần cột phụ. Nhược điểm là khó viết, dễ gây nhầm lẫn với người sử dụng.
Hàm Vlookup có điều kiện If
Cách kết hợp hàm Vlookup với hàm If
Ví dụ 1: Dùng If để giải quyết lỗi cho hàm Vlookup
Ở hình trên, chúng ta thấy công thức ở ô E2 báo lỗi #N/A. Bởi vì, ô D2 không có giá trị, tức là Lookup_Value không có nội dung.
Để khắc phục lỗi này, chúng ta sử dụng kết hợp hàm If theo công thức sau:
=IF(D2=””,””,VLOOKUP(D2,$A$2:$B$7,2,0))
Nếu ô D2 (lookup_value) của hàm vlookup rỗng thì không có nội dung.
Nếu ô D2 không rỗng thì sử dụng hàm Vlookup.
Ví dụ 2: Dùng If để tùy biến vị trí cột tham chiếu trong hàm Vlookup
Ở ví dụ này, chúng ta mở rộng bảng tham chiếu bao gồm cả cột C. Mục đích là khi thay đổi điều kiện ở ô E1 để xét kết quả của hàm Vlookup tương ứng với điều kiện này.
Nếu E1 là Số tiền, Cột Giới tính: Cột 3
Chúng ta kết hợp hàm IF theo công thức sau:
=IF(D2=””,””,VLOOKUP(D2,$A$2:$C$7,IF(E1=”Số tiền”,2,3),0))
Hàm Vlookup kết hợp hàm Hlookup, Left, Right, Match
Hàm Vlookup kết hợp hàm Hlookup
Ta có thể kết hợp 2 hàm Vlookup và Hlookup trong ví dụ sau đây.
Ta cần tính giá trị ThanhTien, trong đó, ThanhTien = SoLuong * Gia, với SoLuong trong bảng 2 và Gia trong bảng 3.
Ta sẽ sử dụng hàm HLOOKUP để dò trong bảng 2 lấy TenHang, dựa trên MaHang trong bảng 1.
Sau đó, dùng kết quả trả về TenHang để dò tìm trong bảng 3 để lấy Gia bằng cách dùng hàm VLOOKUP. Sau cùng là nhân với SoLuong để ra ThanhTien.
Công thức kết hợp hàm VLOOKUP và HLOOKUP tại ô E3:
=D3*VLOOKUP(HLOOKUP(B3,$C$9:$F$10,2,0),$B$14:$D$17,3,0)
Theo đó, giá trị trả về của hàm HLOOKUP là giá trị tìm kiếm của hàm VLOOKUP.
Hàm Vlookup kết hợp hàm Left
Ví dụ về xác định bộ phận làm việc theo mã nhân viên
Chúng ta có 1 bảng danh sách nhân viên với 3 cột: Mã nhân viên, Họ tên, Bộ phận tương ứng như sau:
Trong đó, mã nhân viên gồm 5 ký tự, 2 ký tự đầu chỉ bộ phận làm việc.
Bảng E1:F5 bao gồm tên các bộ phận.
Vậy chúng ta tra cứu Bộ phận của từng nhân viên căn cứ vào Mã của họ như thế nào?
Cách thực hiện:
Bước 1: Tách 2 ký tự đầu trong mã nhân viên dùng hàm LEFT
Bước 2: Sử dụng kết quả của hàm LEFT để tìm bộ phận tương ứng trong bảng E1:F5.
Công thức hàm LEFT:
=LEFT(text, [num_chars])
Trong đó:
- text: đoạn văn bản cần tách ký tự
- num_chars: số ký tự cần tách. Nếu không nhập thì giá trị mặc định là 1
Trong trường hợp này, số ký tự cần tách là 2, ta có công thức tương ứng:
Tại ô C2 sử dụng hàm =LEFT(A2,2), kết quả nhận được là 2 ký tự đầu của ô A2.
Tiếp theo, tham chiếu kết quả của hàm LEFT tới vùng bảng E1:F5 để tìm tên bộ phận.
Viết hàm VLOOKUP lồng với hàm LEFT theo công thức sau:
=VLOOKUP(LEFT(A2,2),E1:F5,2,0)
Theo đó, kết quả cần tìm là cột thứ 2 trong vùng bảng E1:F5. Sử dụng số 0 vì phương thức tham chiếu là tìm chính xác theo ký hiệu.
Hàm Vlookup kết hợp hàm Right
Hàm LEFT để tách chuỗi ký tự bên trái, ngược lại hàm RIGHT tách chuỗi ký tự từ bên phải trong một dãy ký tự.
Cú pháp như sau:
= RIGHT(text, num_chars)
Trong đó:
- Text: đoạn văn bản cần tách ký tự
- Num_chars: số ký tự cần tách tính từ bên phải. Nếu không nhập vào đây thì giá trị mặc định sẽ là 1.
Hàm Vlookup kết hợp hàm Match
Đầu tiên là công thức hàm VLOOKUP:
=VLOOKUP ( lookup value , table_array , col_index_num , [range_lookup] )
Bạn cần một giá trị tra cứu và một mảng bảng để áp dụng công thức này.
Tiếp theo là công thức MATCH:
=MATCH ( lookup value , lookup_array , [match _type] )
Công thức Match trả về kết quả số vị trí dựa trên vị trí giá trị tra cứu trong mảng đã chọn. Để áp dụng công thức này, bạn cần giá trị tra cứu và mảng tra cứu.
Kết hợp 2 hàm với nhau:
Bước 1: Đầu tiên, nhập công thức VLOOKUP, nhập giá trị tra cứu và mảng bảng tra cứu. Theo ví dụ này, giá trị tra cứu là ID số “5” và mảng bảng là khung màu xanh lá cây ở các ô B6: F14.
Bước 2: Tiếp theo, nhập công thức MATCH khi bạn nhập số chỉ cột
Bước 3: Đối với giá trị tra cứu của công thức MATCH, hãy chọn ô chứa tên cột mà bạn muốn trả về. Theo ví dụ này, chúng ta nhấp vào một Tiểu bang.
Bước 4: Đối với hàm MATCH, hãy chọn tiêu đề hàng cho mảng bảng cần tìm kiếm. Theo ví dụ này, đó là khung màu cam bao quanh các ô B6: F6.
Bước 5: Đóng khung công thức MATCH và công thức VLOOKUP bằng dấu ngoặc đơn.
Trên đây là bài viết tổng hợp về hàm vlookup là gì và ý nghĩa hàm Vlookup với các ví dụ minh họa chi tiết nhất. Hy vọng qua bài viết này bạn sẽ nắm được cách thao tác và ứng dụng hàm Vlookup hiệu quả trong công việc của mình.
