Kiến Thức Chung

Hàm vlookup là gì? công thức tính hàm vlookup

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

Hàm Vlookup là gì

 

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

Xem Thêm :  VN-EU: Cần gỡ rối cho vụ Trịnh Xuân Thanh

 Ví dụ 1

Ví dụ cách sử dụng hàm Vlookup 1

Ví dụ 2

Ví dụ cách sử dụng hàm Vlookup 2

Ví dụ 3

Ví dụ cách sử dụng hàm Vlookup 3

Hàm Vlookup hai điều kiện

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.

Hàm Vlookup 2 điều kiện tạo cột phụ tính sản lượng

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.

Nhập công thức: =B6&C6 vào cột A

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.

Giá trị tương ứng ở cột A

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.

Nhập Mã sản phẩm và Ca

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.

Nhập công thức =Vlookup(G6&G7);$A$6:$D$10;4;0) 

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.

Kết quả sản lượ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.

Kết quả sản lượng 2100

Ư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

Lỗi #N/A của 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:

Xem Thêm :  Nước ép củ dền – [ top 10 công thức ] giảm cân, đẹp da

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

Cách tính số tiền E1

Ở 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

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.

Bảng giá trị minh họa MaHang, TenHang

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.

Công thức kết hợp 2 hàm Vlookup và Hlookup

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:

Bảng danh sách nhân viên 

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:

Công thức tách tên bộ phận làm việc của nhân viên

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)

Kết quả tên bộ phận làm việc của nhân viên

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

Đầ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.

Xem Thêm :  Kinh nghiệm chữa bệnh cho gà bằng thuốc nam

Bảng giá trị minh họa các bang

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

Nhập công thức =VLOOKUP(B3,B6:F14)

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.

Nhập công thức =VLOOKUP(B3,B6:F14, MATCH(

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.

Nhập công thức =VLOOKUP(B3,B6:F14, MATCH(E6

Bước 5: Đóng khung công thức MATCH và công thức VLOOKUP bằng dấu ngoặc đơn.

Nhập công thức =VLOOKUP(B3,B6:F14, MATCH(E6

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.


hàm vlookup trong excel 2010 để làm gì và cách sử dụng đúng


hàm vlookup trong excel 2010 để làm gì và cách sử dụng đúng, video giúp bạn hiểu cách dùng hàm vlookup để học excel 2010 online. https://youtu.be/VcsVUplSVhE
Hàm vlookup dùng để dò tìm một giá trị nào đó trong một bảng dữ liệu, nếu tìm thấy dữ liệu phù hợp thì vlookup sẽ đến một ô trong cùng dòng nhưng thuộc một cột được chỉ định dựa theo một phương pháp dò tìm cụ thể. Khi sử dụng vlookup bạn phải khai báo 4 đối số (argument)theo đúng cú pháp mà excel qui định để có kết quả chính xác nhất.
+ Đăng ký kênh:https://goo.gl/X1AF2l
+ Xem các video đã đăng trên kênh photoshop for everyone:https://goo.gl/yGCtlV
+ Xem các video hướng dẫn lý thuyết photoshop(QUAN TRỌNG):https://goo.gl/0i3qov
+ Xem các video về hướng dẫn thực hành photoshop: https://goo.gl/cHshu7
Xem thêm các video về photoshop:
+ Hướng dẫn sử dụng photoshop cs6 để thiết kế ánh sáng ảnh nội thất: https://goo.gl/2f3byX
+ Cách sử dụng photoshop để thay đổi màu sắc cho xe môt tô: https://goo.gl/nqcyub
+ Hướng dẫn ghép ảnh sử dụng photoshop tách nền mây: https://goo.gl/Ia4IpU
+ Video chỉnh sửa ảnh chuyển trắng đen thành màu: https://goo.gl/Dfc1U7
+ Chỉnh sửa ảnh đẹp chân dung bằng photoshop: https://goo.gl/0md9Bc
+ Ghép ảnh dùng pen tool trong photoshop cs6: https://goo.gl/FXkXrq
+ Ghép hình ảnh tách tóc bằng photoshop: https://goo.gl/UOgHdz
+ Chỉnh sửa ảnh khuôn mặt sử dụng photoshop cs6: https://goo.gl/ewaMzt
Trang kênh photoshop for everyone sẽ bổ sung thêm các bài chia sẻ, hướng dẫn về cách sử dụng một số phần mềm như excel, word và một số ứng dụng khác như internet…
Mọi vấn đề về quyền sử dụng video này hoặc trên kênh này xin liên hệ email: kenhtinhochtc@gmail.com

Xem thêm bài viết thuộc chuyên mục: Kiến Thức Chung

Related Articles

Back to top button