Vlookup là một trong những hàm cơ bản thường dùng trong excel. Hàm vlookup cho phép người sử dụng tìm kiếm các giá trị theo cột.
Bài viết dưới đây, đội ngũ giảng viên tại lớp học kế toán thực tế tại Kế toán Lê Ánh sẽ hướng dẫn chi tiết các bạn cú pháp và các sử dụng hàm Vlookup trong excel
>>> Xem thêm: Cách copy dữ liệu từ Excel sang Word giữ nguyên định dạng
Hàm trong Excel được lập trình sẵn dùng tính toán hoặc thực hiện một chức năng nào đó. Việc sử dụng thành thạo các hàm sẽ giúp chúng ta tiết kiệm được rất nhiều thời gian so với tính toán thủ công không dùng hàm. Các hàm trong Excel rất đa dạng bao trùm nhiều lĩnh vực, có những hàm không yêu cầu đối số, có những hàm yêu cầu một hoặc nhiều đối số, và các đối số có thể là bắt buộc hoặc tự chọn.
1. Ý nghĩa của hàm Vlookup trong excel
- Hàm vlookup được dùng để tìm kiếm theo cột
- Hàm vlookup có thể dùng độc lập hoặc kết hợp với các hàm khác như: Sum; If…
2. Cú pháp của hàm Vlookup trong excel
=VLOOKUP[Lookup_value, Table_array, Col_index_num, [Range_lookup]]
Trong đó:
- Lookup_value: là giá trị dùng để tìm kiếm
- Table_array: là vùng điều kiện để dò tìm giá trị tìm kiếm, cột đầu tiên trong table_array là cột để tìm giá trị tìm kiếm. Table_array có thể cùng hoặc khác sheet với Lookup_value và cũng có thể cùng file hoặc khác file với Lookup_value. Thường để ở dạng địa chỉ tuyệt đối
- Col_index_num: Là thứ tự của cột cần lấy dữ liệu trên bảng giá trị cần tìm. Cột đầu tiên của vùng dữ liệu được tính là 1
- Range_lookup: Là kiểu tìm kiếm, gồm 2 kiểu TRUE và FALSE. [Có thể có hoặc không]
TRUE: Tương ứng với 1 là tìm kiếm tương đối
FALSE: Tương ứng với 0 là tìm kiếm tuyệt đối tức Hàm VLOOKUP sẽ tìm kiếm những giá trị trùng khớp nhau hoàn toàn
- Hàm Vlookup thuộc hàm tham chiếu và tìm kiếm. Các hàm tham chiếu sử dụng trong công thức giúp cho chúng ta khỏi tốn công sửa chữa các công thức khi các giá trị tính toán có sự thay đổi
Có 3 loại tham chiếu:
- Tham chiếu địa chỉ tương đối
- Tham chiếu địa chỉ tuyệt đối
- Tham chiếu hỗn hợp
Lưu ý:
- Giá trị bạn muốn tra cứu, còn được gọi là giá trị tra cứu
- Dải ô chứa giá trị tra cứu
- Hãy nhớ rằng giá trị tra cứu phải luôn nằm ở cột đầu tiên của dải ô để hàm VLOOKUP có thể hoạt động chính xác.
- Ví dụ: Nếu giá trị tra cứu của bạn nằm ở ô C2 thì dải ô của bạn sẽ bắt đầu ở C.
- Số cột chứa giá trị trả về trong dải ô.
Ví dụ, nếu bạn chỉ định B2: D11 với phạm vi, bạn nên đếm B là cột đầu tiên, C là thứ hai, v.v.
- Lựa chọn kết quả trả về
- Bạn có thể chỉ định TRUE nếu bạn muốn có một kết quả khớp tương đối hoặc FALSE nếu bạn muốn có một kết quả khớp chính xác ở giá trị trả về.
- Nếu bạn không chỉ định bất cứ giá trị nào thì giá trị mặc định sẽ luôn là TRUE hay kết quả khớp tương đối.
Khóa Học Kế Toán Online - Tương Tác Trực Tiếp Với Giảng Viên Kế Toán Trưởng Trên 15 Năm Kinh Nghiệm
3. Lưu ý khi sử dụng hàm Vlookup
Sử dụng F4 để cố định dòng, cột:
- F4 [1 lần]: để có giá trị tuyệt đối. Tuyệt đối được hiểu là cố định cột và cố định dòng ⇒ $cột$dòng
Ví dụ: $B$9 ⇒ cố định cột B và cố định dòng 9
- F4 [2 lần]: để có giá trị tương đối cột và tuyệt đối dòng – Được hiểu là cố định dòng , không cố định cột ⇒ cột$dòng
Ví dụ: B$9 ⇒ cố định dòng 9, không cố định cột B
- F4 [3 lần]: để có giá trị tương đối dòng và tuyệt đối cột- Được hiểu là cố định cột, không cố định dòng ⇒ $cộtdòng
Ví dụ: $B9 ⇒ cố định cột B, không cố định dòng 9
4. Ví dụ về hàm Vlookup trong Excel
Ta có 2 bảng excel như sau:
Yêu cầu: Thêm thông tin về Quê quán vào bảng bên trên
Cách thực hiện:
Bước 1: Click chuột vào ô E6
Bước 2: Đặt công thức: =VLOOKUP[A6,$D$12:$F$17,2,0]
Trong đó:
A6 là giá trị cần tìm kiếm [ở đây mã nhân viên là dữ liệu chung giữa 2 bảng nên ta tìm mã nhân viên]
$D$12:$F$17 là vùng dò tìm giá trị tìm kiếm [cần tìm mã nhân viên ở bảng 2 để lấy thông tin quê quán nên vùng dò tìm là toàn bộ bảng 2]
2 là số cột chứa thông tin cần tìm [đang muốn tìm thông tin quê quán. Quê quán là cột thứ 2 trong bảng 2]
0 là kiểu dò tìm chính xác
Bước 3: Sao chếp công thức xuống các dòng khác
Ta thu được kết quả:
Trên đây là cú pháp và hướng dẫn chi tiết cách sử dụng hàm Vlookup trong excel. Trong bài viết kế toán Lê Ánh đã đưa ra ví dụ minh họa chi tiết để các bạn dễ hình dung khi áp dụng hàm vlookup. Nếu các bạn vẫn còn vấn đề chưa nắm rõ về hàm Vlookup có thể để lại câu hỏi, SĐT bên dưới bài viết để được giải đáp
Tham khảo thêm >> Khóa Học Kế Toán Tổng Hợp Thực Hành
Tags: Hàm vlookup nâng cao, hàm hlookup trong excel, hàm vlookup có điều kiện, bai tap hàm vlookup trong excel, cách dùng hàm vlookup giữa 2 sheet, hàm tìm kiếm tên trong excel, hàm vlookup và hlookup, cách dùng hàm vlookup giữa 2 file
Trung tâm Lê Ánh hiện có đào tạo các khoá học kế toán và khoá học xuất nhập khẩu cho người mới bắt đầu và khoá học chuyên sâu, để biết thông tin chi tiết, bạn vui lòng liên hệ với chúng tôi theo số hotline: 0904.84.88.55 để được tư vấn trực tiếp về các khoá học này.
Cách sử dụng hàm Vlookup trong Excel, Google sheet chi tiết, đơn giản
Hằng Hoàng Thị Thúy 01/11/2021
Trong Excel hàm Vlookup được nhiều người quan tâm và khá phổ biến, thế nên hôm nay chúng ta sẽ cùng tìm hiểu chi tiết cách sử dụng hàm Vlookup, một số lỗi thường gặp và cách khắc phục của hàm này nhé. Nào cùng theo dõi bài viết!
Từ ngày 01/06/2022 - 30/06/2022, nhất định không thể bỏ lỡ chương trình khuyến mãi cực kì hấp dẫn tại Thế Giới Di Động: Tiệc Sale Lớn Nhất Năm - Xả Kho Giảm Hết.
Tiệc Sale Lớn Nhất Năm - Xả Kho Giảm Hết
Bài viết này được thực hiện trên laptop Asus, Windows 10.
Bạn có thể thực hiện tương tự trên các phiên bản Excel 2019, Excel 2013, Excel 2010, Excel 2007, Excel trên web, Excel cho Microsoft 365, Excel cho Microsoft 365 trên MacOS, Excel 2019 trên MacOS, Excel 2016 trên MacOS, Excel 2011 trên MacOS, Excel Starter 2010.
I. Hàm Vlookup để làm gì?
Hàm Vlookup là một hàm dùng để tìm kiếm thông tin cần tìm trong bảng, một phạm vi theo hàng hoặc cột, bảng nào đó.
Ví dụ: Tra cứu một số lượng iPhone 11 được bán trong tháng 8/2020 của công ty cổ phần Thế Giới Di Động, hoặc tra cứu tên cửa hàng trưởng của Thế Giới Di Động tại TPHCM.
Công thức của hàm Vlookup là: =VLOOKUP[lookup_value, table_array, col_index_num, [range_lookup]]
Trong đó:
- Lookup_value: là giá trị cần dò tìm
- table_array: là bảng cần dò tìm
- col_index_num: là vị trí cột cần lấy giá trị
- range_lookup: nhập vào số 0 để tìm chính xác và nhập vào số 1 để tìm gần đúng [thông thường nhập số 0]
II. Cấu trúc công thức hàm Vlookup
Trước khi vào tìm hiểu hàm Vlookup khô khan thì mình đi mua sắm tí nhé! Hôm nay cùng mình đi mua snack nghen!
Lấy ví dụ thực thế để minh họa hàm Vlookup
Khi bạn vào một cửa hàng để mua snack, việc đầu tiên là tìm được line [hàng] có snack trong khu vực nào của cửa hàng, vị trí chính xác chưng bày snack trong line đó, và quyết định mua, một là mua snack bất kì bạn gặp ngẫu nhiên, miễn là snack bạn sẽ mua [tương đối], hai là mua snack đúng loại bạn thích từ thương hiệu, loại, kích thước và hương vị [giá trị tuyệt đối].
Từ đó chúng ta có công thức mua snack như sau: Tìm được line có snack > Biết hàng đó trong khu vực nào của cửa hàng > Vị trí chính sát chưng bày snack trong line đó > Đưa ra quyết đinh mua snack theo hướng tương đối hay tuyệt đối.
1. Tìm được line có snack > Giá trị cần dò tìm [Lookup_value] => G6
Giá trị cần dò tìm trong hàm Vlookup
2. Trong khu vực nào có chứa snack của cửa hàng > Bảng chứa giá trị cần dò tìm [Table_array] => [B1:D4]
Bảng giá trị cần tìm trong hàm Vlookup
3. Vị trí chính xác chưng bày snack trong line đó > Vị trí cột cần lấy giá trị trong bảng chứa giá trị cần dò tìm [Col_index_num] => 2
Vị trí cần dò tìm trong hàm Vlookup
Lưu ý: Đếm số thứ tự cột trong bảng chứa giá trị cần dò tìm bạn phải đếm cột trái sang phải, vị trí đầu tiên bên trái của bảng bạn đặt số 1 và đếm theo thứ tự tăng dần đến hết bảng.
4. Đưa ra quyết định mua tương đối hay tuyệt đối > Tìm kiếm tương đối hoặc tuyệt đối [Col_index_num] =>1
Lưu ý:
Tìm kiếm tương đối hoặc tuyệt đối thể hiện bằng số 1 hoặc 0 hoặc chữ TRUE và FALSE [1=TRUE hoặc 0=FALSE]
Nói một cách dễ hiểu giá trị tương đối là khi ta đi mua snack là mua chứ không phải đúng thương hiệu mình thích, loại, vị, cỡ ,...! Còn nếu ngược bạn phải mua đúng snack thương hiệu mình thích, loại, vị, cỡ,... thì bạn hãy chọn tuyệt đối cho công thức của mình nè!
Chọn giá trị tuyệt đối, tương đối trong hàm Vlookup
Các bạn gõ dấu “=” sau đó nhập VLOOKUP[
Và ta có công thức hoàn chỉnh cho công cuộc đi mua snack: = Vlookup[G6,B1:D4,2,1]
Giá trị tìm được
Tại sao phải khóa bảng đối chiếu giá trị tìm kiếm?
Khóa bảng tìm kiếm để máy tính hiểu rằng chỉ tìm giá trị trong bảng đã khóa, không tìm giá trị ra ngoài.
Trong trường hợp này hàm công thức đang tự đối chiếu giá trị rỗng ngoài bảng nên sẽ báo lỗi ngay khi bạn Enter đấy!
Lỗi không khóa bản tìm kiếm
Bị lỗi #N/A vậy nên cần khóa bảng để tìm đúng giá trị và tránh mắc lỗi ở các trường hợp cơ bản. Cẩn thận bạn nhé!
lỗi #N/A khi không khóa bản đối chiếu dữ liệu
Cách thực hiện khóa bảng đối chiếu dữ liệu:
- Nhấn F4 hoặc Fn+ F4 [cho một số Laptop] để khóa bảng chứa giá trị cần dò tìm.
- Khóa bảng chứa giá trị cần dò tìm sẽ giúp cho công thức của bạn tìm đúng giá trị trong bảng đã khóa không đối chiếu ra ngoài ô khác. Và tránh lỗi #NA khi không tìm thấy giá trị cần tìm
- Khi khóa bảng sẽ có biểu thức hiển thị như sau: $G$2:$H$14.
III. Bài tập hàm Vlookup đơn giản, có đáp án
Ví dụ: Đề bài:
Hãy xếp loại học sinh theo thứ hạng yếu, trung bình, khá, giỏi,... dựa trên 2 bảng cho trước dưới đây và sử dụng hàm Vlookup để tìm kiếm.
Hãy sử dụng hàm Vlookup, để xếp loại thứ hạng trong 2 bảng sau.
Áp dụng công thức hàm Vlookup ta có: =VLOOKUP[B2,$E$2:$G$9,2,0]
Trong đó:
B2: Giá trị cần dò tìm giá trị
$E$2:$G$9: Bảng đối chiếu giá trị [đã khóa]
2: Vị trí cột cần dò tìm
0: Chọn giá trị tuyệt đối để dò tìm
Chọn giá trị tuyệt đối để dò tìm
Và kết quả là:
Và kết quả
Gặp lỗi #N/A vì giá trị so sánh lấy tuyệt đối tức giá trị cần tìm là các số nguyên dương từ 1 đến 10, không chấp nhận bất cứ số nào trong khoảng hoặc chứa dấu phẩy.
Vd: Khi nhập công thức chúng ta chọn 0 hoặc FALSE tức là kết quả trả về chỉ nhận và trả về giá trị 9 chứ không chấp nhận giá trị 9,5.
=> Vì vậy chúng ta cần lưu ý khi chọn điều kiện cho giá trị hiển thị, tuyệt đối hay tương đối để đảm bảo đúng sự tìm kiếm của mình.
Sửa lại công thức ta có công thức đúng như sau:
Sửa lại công thức ta có công thức đúng
Đây là link bài tập bạn có thể thực hành hàm Vlookup ngày tại đây
IV. Một số lỗi thường gặp và cách khắc phục
XEM NGAY các chương trình khuyến mãi hấp dẫn, các mã giảm giá, mã khuyến mãi HOT tại Thế Giới Di Động trong tháng 6:
- Tổng hợp tất cả mã giảm giá, mã khuyến mãi T06/2022 tại TGDĐ
- Tổng hợp khuyến mãi hấp dẫn tháng 6/2022 dành cho tất cả ngành hàng
1. Lỗi #NA
Tham khảo: Cách sửa lỗi #N/A trong Excel có ví dụ minh họa
- #NA: dò tìm không có giá trị.
- Cách khắc phục:
Nếu chưa phát hiện lỗi #NA thì bạn có thể khắc phục lỗi #NA bằng công thức sau.
= IFERROR [công thức gốc, giá trị sẽ hiển thị nếu công thức gốc có lỗi].
Lấy ví dụ trên minh họa:
= IFERROR [VLOOKUP[B5,$G$2:$H$14,2,0]”Tìm giá trị lỗi”]
2. Lỗi #REF
Tham khảo: Cách sửa lỗi #REF trong Excel có ví dụ minh họa
- #REF: cột dò tìm không có trong bảng chứa giá trị dò tìm.
- Cách khắc phục
Ví dụ lỗi #REF! xảy ra do việc xóa cột.
-> Bạn có thể bấm ngay nút Hoàn tác trên Thanh công cụ Truy nhập Nhanh [hoặc nhấn CTRL+Z] để khôi phục hàng hoặc cột đó.
Ví dụ: VLOOKUP với tham chiếu dải ô không chính xác.
-> Điều chỉnh phạm vi có lớn hơn hoặc giảm cột tra cứu giá trị để khớp với tham chiếu phạm vi. =VLOOKUP[A8,A2:E5,5,false] sẽ là một phạm vi tham chiếu hợp lệ, như làm =VLOOKUP[A8,A2:D5,4,FALSE].
3. Lỗi #VALUE
Tham khảo: Cách sửa lỗi #VALUE trong Excel có ví dụ minh họa
- #VALUE: lỗi với cách nhập công thức của bạn.
- Cách Khắc phục
a. Sự cố: Tham đối giá_trị_tra_cứu có nhiều hơn 255 ký tự.
Giải pháp: Rút ngắn giá trị hoặc sử dụng kết hợp hàm INDEX và MATCH như một giải pháp thay thế.
b. Sự cố: Số thứ tự cột giá trị hiển thị [Col_index_num] chứa văn bản hoặc nhỏ hơn 0.
- Sự cố này có thể xảy ra do lỗi đánh máy trong Số thứ tự cột giá trị hiển thị hoặc vô tình chỉ định một số nhỏ hơn 1 làm giá trị chỉ mục [hiện tượng phổ biến nếu một hàm Excel khác được lồng trong hàm VLOOKUP trả về một số, chẳng hạn như "0", làm số thứ tự cột giá trị hiển thị.
- Giá trị tối thiểu cho số thứ tự cột giá trị hiển thị là 1, trong đó 1 là cột tìm kiếm, 2 là cột đầu tiên ở bên phải của cột tìm kiếm, v.v.. Vì vậy, nếu bạn đang tìm kiếm trong cột A thì số 1 sẽ tham chiếu cột đó, 2 là cột B, 3 là cột C, v.v..
Bài viết này đã giới thiệu cũng như hướng dẫn chi tiết về hàm Vlookup cơ bản. Hy vọng bài viết này đã mang đến bạn những thông tin bổ ích. Đừng quên để lại nhận xét, like và share bài viết này nhé!
7.898 lượt xem