Nhiều bạn hỏi về cách sử dụng hàm Vlookup và hàm Hlookup trong excel, đặc biệt là cách phân biệt khi nào thì sử dụng hàm Vlookup khi nào thì dùng hàm Hlookup, hôm nay Học Excel Online chi tiết cách sử dụng hàm Vlookup, cách phân biệt đơn giản và dễ nhớ và có ví dụ minh họa cụ thể. Đừng bỏ qua bạn nhé!
Hướng dẫn cách sử dụng hàm Vlookup trong Excel
Theo Micorsoft thì hàm Vlookup có cấu trúc cú pháp như sau:=VLOOKUP(Lookup_value, Table_array, Col_index_ num,
Giải thích:
– Lookup_value: Giá trị cần dò tìm. Bạn có thể điền giá trị trực tiếp vào công thức hoặc tham chiếu tới một ô trên bảng tính Excel.
Bạn đang xem: Cách sử dụng vlookup trong excel
– Table_array: Bảng giới hạn để dò tìm, bạn cần F4 để Fix cố định giá trị cho mục đích copy công thức tự động, nếu bạn sử dụng laptop, có thể bạn sẽ cần bấm phím FN+F4 để cố định tham chiếu cho vùng bảng tính này.
– Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm, tính từ trái qua phải.
– Range_lookup: Là giá trị Logic (TRUE=1, FALSE=0) quyết định so sánh, tìm kiếm chính xác hay so sánh, tìm kiếm tương đối với bảng giới hạn.
+ Nếu Range_lookup = 1 (TRUE): So sánh tương đối.+ Nếu Range_lookup = 0 (FALSE): So sánh chính xác.+ Nếu bỏ qua đối này thì Excel hiểu là Range_lookup = 1 một cách mặc định.
Vậy khi nào thì sử dụng hàm Vlookup và khi nào dùng hàm Hlookup?
Đơn giản thôi, nếu bảng giới hạn dò tìm xếp dọc như ví dụ dưới đây thì ta dùng hàm Vlookup, còn bảng giới hạn dò tìm là ngang thì ta dùng hàm Hlookup, vậy bạn chỉ cần nhớ V – dọc, H – ngang (Trong tiếng Anh, V – viết tắt của từ Vertical, còn H – viết tắt của từ Horizontal)
Ví dụ cách sử dụng hàm Vlookup trong Excel
Cách tính thuế nhập khẩu theo đối tượng
Ví dụ. Bạn hãy tính thuế nhập khấu theo Đối tượng của các mặt hàng dưới đây:
Trong ví dụ trên, tại ô G5 ta gõ công thức: =VLOOKUP(D5,$D$17:$F$20,2,0)*E5
Trong đó:
Vlookup: là hàm dùng để tìm kiếm ra thuế nhập khẩu tại Bảng quy định thuế.D5: Giá trị là đối tượng cần tìm ở đây là các đối tượng từ 1,2,3,4.$D$17:$F$20: Bảng giới hạn dò tìm, chính là D17:F20 nhưng được F4 để Fix cố định địa chỉ ô để Copy công thức xuống các ô G6->G12 thì công thức sẽ không bị thay đổi.2: Thứ tự cột giá trị cần lấy, trong trường hợp này chính là cột Thuế nhập khẩu0: Trường hợp này chúng ta lấy giá trị tuyệt đối nên chọn là 0 hoặc FalseE5: Chính là đơn giá sản phẩm để tính ra thuế nhập khẩu.
Với công thức trên, kết quả ta được là:
Copy công thức xuống các ô G6->G12 ta được kết quả như ảnh trên.
Cách dùng hàm VLOOKUP để tìm kiếm gần đúng
Sẽ có bạn hỏi mình, làm thế nào xác định được Range_lookup=1 hay Range_lookup=0?
Theo đúng định nghĩa của Microsoft Office Range_lookup=1 khi chúng ta cần tìm giá trị tương đối nghĩa là gần đúng hay giá trị hợp lý nhất khi không thể tìm thấy một giá trị chính xác.
Ví dụ: Để xếp loại học sinh dựa trên điểm trung bình trong trường hợp dưới đây thì Range_lookup=1, vì để xếp loại theo bảng tham chiếu thì ta bắt buộc phải lấy giá trị tương đối nghĩa là gần đúng, 9.1 gần với 9, 5.3 gần với 5 … hay diễn đạt theo một cách khác, chúng ta có thể đưa ra tiêu chí xếp loại như sau:
Từ 9: xếp loại giỏi ( lớn hơn hoặc bằng 9, 9 Từ 6.5 đến dưới 9: xếp loại khá (6.5 Từ 5 đến dưới 6.5: xếp loại trung bình (5Để điền vào cột Xếp loại (cột D), chúng ta có thể dùng hàm VLOOKUP như sau trong ô D4:=VLOOKUP(C4,$B$10:$C$12,2,TRUE)
hoặc
=VLOOKUP(C4,$B$10:$C$12,2,1)
Lưu ý: Khi sử dụng hàm VLOOKUP trong Excel để thực hiện tra cứu gần đúng, bảng tra cứu cần được sắp xếp theo thứ tự từ nhỏ tới lớn. Nếu bạn không sắp xếp bảng tra cứu theo thứ tự này, kết quả tra cứu dùng hàm VLOOKUP sẽ bị sai.
Cũng với ví dụ tra cứu xếp loại dựa trên điểm số trên sử dụng hàm VLOOKUP, khi bảng điểm có một bạn được 4 điểm, thì chúng ta sẽ nhận được lỗi #N/A khi sử dụng hàm VLOOKUP. Để khắc phục lỗi này của hàm VLOOKUP, bạn có thể thêm vào bảng tra cứu một dòng dữ liệu như hình bên dưới
Cách sử dụng hàm VLOOKUP kết hợp với Data Validation trong Excel
Trong ví dụ ứng dụng hàm VLOOKUP này, chúng ta sẽ tham chiếu tới ô có Data Validation, để khi thay đổi giá trị trong danh sách dropdown validation này, thì kết quả trả về từ hàm VLOOKUP cũng được thay đổi một cách tự động.
Bước đầu tiên, chúng ta sẽ đi chuẩn bị Data Validation bằng cách chọn ô C10, sau đó bấm vào thẻ Data / Data Validation:
Trong hộp thoại Data Validation, chúng ta sẽ chọn thẻ Settings (1), sau đó trong mục Allow, chọn List (2) và cuối cùng chọn Source (3) là vùng B4:B7
Sau đó, chúng ta sẽ hoàn thành hàm VLOOKUP tham chiếu đến ô chứa Data Validation như hình
Nội dung bài viết được cố vấn chuyên môn bởi TS. Nguyễn Thị Chinh Lam - Giảng viên khóa học kế toán tổng hợp thực hành tại Kế Toán Lê Ánh.
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
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,
Trong đó:
Lookup_value: là giá trị dùng để tìm kiếmTable_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 đốiCol_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à 1Range_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 đốiTham 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.
Xem thêm: Những Câu Chúc Đi Xa Bằng Tiếng Anh Mới Nhất 2023, 50 Cách Để Nói Chúc May Mắn Bằng Tiếng Anh
- 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