Cách sử dụng hàm VLOOKUP chi tiết có ví dụ minh họa

VLOOKUP là một trong những hàm Excel hữu ích nhất và thường xuyên được sử dụng với dân văn phòng kế toán chuyên nghiệp. Thế nhưng với người mới làm quen với Excel thì đây lại là một hàm khá khó sử dụng. Bạn cũng mới tiếp xúc với hàm VLOOKUP  và chưa biết cách sử dụng thành thạo thì đừng lo hãy theo dõi bài viết dưới đây nhé.  Bài viết này Thủ thuật 247 sẽ giới thiệu đến các bạn hàm VLOOKUP là gì và cách sử dụng hàm VLOOKUP chi tiết có ví dụ minh họa luôn

1. Hàm VLOOKUP là gì?

Hàm VLOOKUP là một hàm tìm kiếm trong Microsoft Excel. Hàm VLOOKUP sẽ tìm kiếm giá trị bạn đưa vào và trả về giá trị cùng dòng ở một cột khác trong bảng.

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 cùng dòng 6 (ví mã SP05 nằm ở dòng 6)

 

Hàm VLOOKUP có mặt trong các phiên bản Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2007 và các phiên bản cũ hơn.

2. 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 là 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 hoặc 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 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

3. Sử dụng hàm VLOOKUP trong thực tế

Như đã nói ở trên khi sử dụng hàm VLOOKUP trong thực tế công việc, một điều bạn luôn luôn nên chú ý đó chính 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.

Trong ví dụ này sử dụng VLOOKUP không có khóa tham chiếu. Sau khi copy công thức 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

3.1 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.

3.2 Sử dụng hàm VLOOKUP tra cứu ở 1 sheet khác

Ví dụ: Sheet Tra-Cuu chứa bangr 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ạ.

3.3 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 tra cứu 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.

Kết quả

Trong trường hợp wb khác.xlsx đó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)

3.4 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

=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)

3.5 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ề hàm VLOOKUP, cách 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é. Bọn mình sẽ giải đáp ngay. Cảm ơn các bạn đã theo dõi .