Với dân văn phòng hay kế toán chuyên nghiệp thường xuyên phải làm việc với bảng tính Excel thì VLOOKUP là một trong những hàm Excel hữu ích và thường xuyên được sử dụng nhất . Tuy nhiên VLOOKUP vẫn là cái gì đó khó hiểu đối với những người mới làm quen với Excel. Trong bài học Excel này chúng tôi sẽ hướng dẫn các bạn kiến thức về hàm VLOOKUP là gì và cách sử dụng hàm VLOOKUP chi tiết nhất
Hàm VLOOKUP là gì?
Hàm VLOOKUP cơ bản là một hàm tìm kiếm trong Microsoft Excel. Hàm VLOOKUP sẽ tìm kiếm giá trị người dùng đưa vào sau đó trả về giá trị cùng dòng ở một cột khác trong bảng thang chiếu.
Ví dụ chúng ta cần tìm Đơn giá cho mã sản phẩm SP05 trong bảng thì giá trị trả về sẽ nằm trên cùng dòng 6 (ví mã SP05 nằm ở dòng 6)
Hàm VLOOKUP có mặt từ rất sớm và khả dụng với tất cả các phiên bản Excel như Excel 2003, 2007, 2010, 2013, 2016, 2019 và cả trong Office 365
Cú pháp của hàm VLOOKUP
Cú pháp hàm VLOOKUP như sau
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Hàm VLOOKUP có 4 tham số trong đó 3 tham số đầu tiên là bắt buộc : lookup_value, table_array, col_index_num, tham số thứ 4 ở trong ngoặc vuông [range_lookup] là không bắt buộc.
- lookup_value : Giá trị cần tìm kiếm, giá trị này có thể thuộc kiểu dữ liệu số, kiểu chuỗi, kiểu ngày tháng, kiểu tham chiếu hay một giá trị được tính toán ra bởi một hàm khác.
- table_array: Vùng dữ liệu tìm kiếm gồm ít nhất 2 cột. Cột đầu tiên chứa giá trị tìm kiếm và trả về giá trị cùng dòng trong 1 cột còn lại Sử dụng F4 để tham chiếu tuyệt đối cho vùng dữ liệu này
- col_index_num là thứ tự cột chứa giá trị trả về trong bảng tìm kiếm.
- [range_lookup]tham số không bắt buộc, nhận giá trị TRUE hoặc 1 (tìm kiếm gần đúng) hoặc FALSE hoặc 0 (tìm kiếm chính xác)
- FALSE tham số range_lookup khi nhận giá trị FALSE sẽ thực hiện tìm kiếm chính xác cho lookup_value.
- TRUE tham số range_lookup khi nhận giá trị TRUE sẽ thực hiện tìm kiếm gần đúng
Nói lý thuyết thì hơi khó hiểu chúng ta sẽ đi tìm hiểu các tham số qua ví dụ dưới đây sẽ dễ hơn rất nhiều các bạn nhé
Ví dụ 1:
Cho bảng sau yêu cầu tìm đơn giá của Mã SP có mã là SP05.
Công thức chúng ta sẽ dùng ở trường hợp này là:
=VLOOKUP("SP05",$A$2:$C$10,3,FALSE) hoặc =VLOOKUP(E1,$A$2:$C$10,3,FALSE)
Chúng ta nên sử dụng công thức =VLOOKUP(E1,$A$2:$C$10,3,FALSE) vì dễ dàng sửa đổi sản phẩm tìm kiếm hơn hoặc dễ tham chiếu hơn.
Phân tích công thức =VLOOKUP(E1,$A$2:$C$10,3,FALSE)
- lookup_value: Ở đây chính là giá trị của ô E1 tức là chuỗi "SP05"
- table_array: Vùng dữ liệu tìm kiếm là khoảng dữ liệu từ A2 đến C10 bao gồm 3 cột, hàm VLOOKUP sẽ tìm kiếm chuỗi “SP05” trong cột đầu tiên là cột A. Sở dĩ có biểu tượng $A$2:$C$10 là chúng ta sử dụng khóa tham chiếu (tham chiếu tuyệt đối)
- col_index_num: 3 . table_array là vùng $A$2:$C$10, cột C là cột thứ 3 trong bảng này
- [range_lookup] : FALSE vì chúng ta cần tìm giá trị chính xác
Nếu sử dụng công thức =VLOOKUP("SP05",$A$2:$C$10,3,FALSE) sẽ rất bất tiện khi muốn tìm kiểm sản phẩm khác hoặc chúng ta sử dụng Data Validation List . Vì vậy lời khuyên là sử giá trị tìm kiếm thì nên tham chiếu đến 1 ô cụ thể.
Ví dụ sử dụng VLOOKUP với Data Validation List.
Ở đây chúng ta dùng công thức sau
=VLOOKUP(D2,$A$2:$C$10,3,0)
Với Data Validation List. mỗi lần chúng ta thay mã SP sẽ có kết quả khác
Sử dụng hàm VLOOKUP trong thực tế
Một điều bạn luôn cần nhớ khi sử dụng VLOOKUP trong thực tế đó là là khoá tham chiếu đối với bảng tra cứu. Hãy cùng nhau tìm hiểu ví dụ sau đây.
Ví dụ sử dụng VLOOKUP không có khóa tham chiếu.
Sau khi copy công thức =VLOOKUP(D2,$A$2:$C$10,3,0) cho các sản phẩm còn lại thì như bạn thấy đấy tuy nhiều ô có kết quả đúng nhưng nhiều ô bị lỗi #NA do vùng tham chiếu đã bị lệch đi.
Vì vậy nhắc lại 1 lần nữa khi sử dụng VLOOKUP phải sử dụng tham chiếu tuyệt đối với khóa tham chiếu. ( Chọn vùng dữ liệu và nhấn F4)
Sau khi sửa lại tham chiếu tuyệt đối với khóa tham chiếu F4 kết quả như sau
5 điều cần chú ý khi sử dụng hàm VLOOKUP
- Hàm VLOOKUP không thể trả về kết quả khi cột lấy kết quả nằm về phía bên trái của cột chứa dữ liệu cần tra cứu. Phương án xử lý: sử dụng kết hợp hàm Index và Match
- Hàm VLOOKUP tra cứu không phân biệt chữ hoa / chữ thường.
- Chú ý tham số cuối cùng [range_lookup] để chọn đúng TRUE hay FALSE trong trường hợp tra cứu gần đúng hoặc tra cứu chính xác.
- Khi tra cứu gần đúng, chú ý dữ liệu trong cột tra cứu cần được sắp xếp theo thứ tự tăng dần
- Khi giá trị cần tra cứu không được tìm thấy, VLOOKUP sẽ trả về lỗi #NA.
Sử dụng hàm VLOOKUP tra cứu ở 1 sheet khác
Ví dụ: Sheet Tra-Cuu chứa bảng tham chiếu, ta cần dùng hàm VLOOKUP ở sheet1
Ta sử dụng công thức sau
=VLOOKUP(A2,'TRA-CUU'!$A$1:$C$10,3,0)
Dĩ nhiên công thức này chúng ta không phải gõ vào mà chúng ta thao tác rồi Excel tự điền cho chúng ta.
Trước tiên bạn gõ công thức =VLOOKUP như bình thường khi cần chọn bảng tham chiếu chúng ta chỉ cần bấm sang sheet Tra-Cuu chọn vùng dữ liệu, nhấn tiếp F4 để tham chiếu tuyệt đối sau đó nhấn Enter. Lúc này Excel sẽ có thông báo như hình dưới chỉ cần chọn OK là chúng ta sẽ được đưa trở lại Sheet 1 để gõ tiếp công thức.
Các bạn thấy đấy Excel rất thông minh để có thể hỗ trợ chúng ta viết công thức VLOOKUP tự động đúng như trên hình minh hoạ.
Sử dụng hàm VLOOKUP tra cứu ở 1 file Excel khác
Dùng hàm sau:
=VLOOKUP(A2,'[VLOOKUP.xlsx]TRA-CUU'!A$1:C$10,3,0)
Cách làm tương tự với sử dụng VLOOKUP khác sheet, chúng ta cũng gõ công thức =VLOOKUP như bình thường, đến phần chọn vùng dữ liệu tham chiếu chuyển sang file Excel chứa dữ liệu tham chiếu chọn vùng và nhấn F4 để tham chiếu tuyệt đối => Ấn Enter => Excel hiện bảng thông báo chúng ta chỉ việc nhấn OK là Excel tự điền công thức phần tham chiếu này và chuyển chúng ta trở về file chứa công thức, gõ hoàn thiện nốt công thức là xong.
Trong trường hợp file Excel tham chiếu đóng, thì công thức VLOOKUP của chúng ta vẫn sẽ hoạt động như bình thường, nhưng Excel sẽ tự động “viết lại” công thức VLOOKUP với đường dẫn tới file dữ liệu như sau:
=VLOOKUP(A4,'F:\THUTHUAT\[VLOOKUP.xlsx]TRA-CUU'!A$1:C$10,3,0)
Sử dụng hàm VLOOKUP và ký tự thay thế *, ?
Giống như nhiều hàm Excel khác, hàm VLOOKUP hỗ trợ bạn sử dụng ký tự thay thế như
- Dấu * để thay thế cho bất kì số lượng ký tự nào trong tham số lookup_value
- Dấu ? để thay thế cho 1 và chỉ 1 ký tự trong tham số lookup_value
Ví dụ tìm kiếm mã sản phẩm bắt đầu hay kết thúc bởi một chuỗi
Sử dụng công thức sau :
=VLOOKUP("*XX",$A$1:$C$10,3,0)
Trong ví dụ này, dấu * thay thế cho cả đoạn “SP01-BCD-” trong dữ liệu của chúng ta.
Nếu bạn muốn tra cứu chuỗi có chứa chữ “XXX” ở khoảng giữa, thì công thức VLOOKUP chúng ta có thể viết công thức VLOOKUP như sau:
=VLOOKUP("*XXX*",$A$1:$C$10,3,0)
Để công thức của chúng ta linh hoạt hơn, bạn có thể tách phần “XXX” ra 1 ô riêng – ô D5 chẳng hạn, thì công thức VLOOKUP của chúng ta sẽ như sau:
=VLOOKUP("*" & D5 & "*",$A$1:$C$10,3,0)
Trong ví dụ này, chúng ta sử dụng “XYZ*” để tra cứu và tìm kiếm ô dữ liệu bắt đầu bằng “XYZ”
=VLOOKUP("XYZ*",$A$1:$C$10,3,0)
Tra cứu gần đúng với hàm VLOOKUP trong Excel
Bây giờ sẽ là lúc chúng ta nhìn kỹ hơn vào tham số thứ 4 trong hàm VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
[range_lookup] nhận giá trị TRUE hoặc FALSE.
- FALSE cho trường hợp tra cứu chính xác mà chúng ta đã có nhiều trường hợp ví dụ trong bài viết này, nếu bạn ngại viết FALSE hơi dài thì ta có thể thay bằng số 0 với tác dụng tương tự.
- TRUE cho trường hợp tra cứu gần đúng, thay vì gõ TRUE, chúng ta có thể gõ số 1.
Ví dụ tra cứu gần đúng với VLOOKUP như sau
Bạn làm quản lý và đưa ra KPI cho các nhân viên bán hàng, tới cuối tháng, bạn sẽ cần tính hoa hồng dựa trên từng bậc doanh thu cho các bạn nhân viên Sales này, rõ ràng việc sử dụng tra cứu chính xác với VLOOKUP sẽ không giải quyết được vấn đề bởi vì doanh thu có từng bậc thôi
Như chúng đã nói ở trên khi sử dụng VLOOKUP tra cứu gần đúng thì cột tham chiếu phải sắp xếp theo thứ tự từ nhỏ đến lớn ( ở đây là cột A) Bởi vì, VLOOKUP sẽ trả về giá trị gần nhất và nhỏ hơn lookup_value (giá trị cần tìm kiếm).
Như vậy kết quả #N/A chúng ta đang gặp phải trong ví dụ này là hoàn toàn đúng với cách hoạt động của VLOOKUP, bởi vì trong cột A – Doanh thu không có giá trị nào nhỏ hơn 50 cả.
Để xử lý lỗi #N/A trong trường hợp này, bạn có thể kết hợp thêm hàm IFNA hoặc IFERROR như sau:
=IFNA(VLOOKUP(E2,$A$1:$B$5,2,1),"Ko có hoa hồng")
Hoặc
=IFERROR (VLOOKUP(E2,$A$1:$B$5,2,1),"Ko có hoa hồng")
Ví dụ khác sử dụng Tra cứu gần đúng TRUE.
Nếu bảng tham chiếu có cột tham chiếu không sắp xếp theo thứ tự từ bé đến lớn chúng ta sẽ cho ra kết quả sai
Kết quả sai
Bảng tham chiếu đúng
Trên đây là toàn tập về cách sử dụng hàm VLOOKUP, ví dụ sử dụng VLOOKUP chi tiết từ A - Z. Có gì chưa hiểu hay thắc mắc các bạn comment bên dưới nhé. Chúc các bạn học tốt.
