Vlookup là một hàm cơ bản; được sử dụng rất phổ biến trong Excel. Vậy hàm vlookup là gì? Cách dùng hàm vlookup như thế nào? Mời quý bạn đọc cùng tìm hiểu qua bài viết dưới đây!
Contents
Hàm vlookup là gì?
Theo đó, V là viết tắt của từ tiếng Anh “Vertical”, có nghĩa là theo chiều dọc. Còn “Lookup” được hiểu là tìm kiếm. Vì vậy, hàm Vlookup trong Excel được hiểu là hàm tìm kiếm giá trị và trả kết quả theo phương thức hàng dọc. Không chỉ vậy, nó còn được sử dụng để thống kê, dò tìm dữ liệu theo cột nhanh chóng.
Hiện nay, Vlookup được sử dụng rất phổ biến.
Ý nghĩa hàm Vlookup
Theo định nghĩ, ta có thể thấy rõ nhất là hàm Vlookup được dùng để tìm kiếm dữ liệu theo cột. Nó có thể sử dụng độc lập hoặc kết hợp với nhiều hàm khác như: if, sum,….
Bài viết tham khảo: Cách viết mail xin việc giúp gây ấn tượng với nhà tuyển dụng
Cấu trúc hàm Vlookup và cách sử dụng
Cấu trúc của hàm như sau:
=Vlookup(Lookup_value, Table_array, Col_index_num, [Range_lookup]).
Trong đó:
- Lookup_value: Giá trị cần dò tìm
- Table_array: Là bảng dò tìm. Sau khi đã nhập xong công thức, người dùng cần phải nhấn F4 để thực hiện cố định các giá trị, phục vụ cho mục đích copy công thức tự động.
- Col_index_num: Số thứ tự của cột được lấy dữ liệu trong bảng dò tìm; sẽ được tính từ trái qua phải.
- Range_lookup: Đây là giá trị logic (thường quy ước là TRUE = 1 VÀ FALSE = 0). Nếu Range_lookup = 1 (TRUE) là tìm kiếm tương đối. Còn Range_lookup = 0 (FALSE) là tìm kiếm chính xác. Khi làm, nếu bỏ qua phần này thì Excel sẽ tự hiểu là Range_lookup = 1.
Tìm kiếm chính xác được áp dụng trong trường hợp cần tìm kết quả khớp chính xác. Nếu không có giá trị nào khớp chính xác với giá trị cần tìm kiếm thì hàm sẽ báo lỗi. Thông thường, cách tìm kiếm này được sử dụng để tìm kiếm các thông tin về quê quán, họ tên, tuổi tác, mã nhân viên,…
Ngược lại, tìm kiếm tương đối (Range_lookup = TRUE) được sử dụng để tìm kiếm theo khoảng giá trị kết quả khớp tương đối. Tức là nếu kết quả khớp chính xác không tìm thấy, hàm sẽ tự động trả về giá trị lớn nhất kế tiếp, nhỏ hơn so với look_up value. Cách tìm kiếm này được áp dụng để xếp loại học lực học sinh, tính tiền lương, thưởng,…
Để hiểu rõ hơn về cách dùng hàm Vlookup cơ bản, chúng ta cùng tham khảo ví dụ sau:
Ví dụ: Điền trường thông tin quê quán cho từng nhân viên theo bảng dưới đây?
Cách làm như sau:
+ Tại ô G4, ta nhập công thức dò tìm như sau:
= VLOOKUP(F4,$B$4:$D$10,3,0).
Sau đó nhấn Enter.
+ Tiếp đó, kéo copy công thức xuống cho các nhân viên còn lại, ta sẽ thu được kết quả như hình dưới đây.
Lưu ý: Bảng dò tìm “$B$4:$D$10” đã được cố định. Nếu không cố định địa chỉ này, khi copy công thức xuống, hàm Vlookup sẽ báo lỗi.
Hướng dẫn sử dụng hàm Vlookup nâng cao
Như chúng ta biết, Vlookup có thể dùng độc lập hoặc kết hợp với các hàm khác. Dưới đây là một số cách tính hàm Vlookup nâng cao, mời các bạn cùng tham khảo:
Cách dùng hàm If kết hợp Vlookup
Thông thường, hàm If sẽ kết hợp cùng hàm Vlookup để:
- So sánh các giá trị.
- Sửa lỗi
- Hàm If tùy biến một vị trí cột tham chiếu trong Vlookup.
Để hiểu rõ hơn về cách dùng này, chúng ta cùng tham khảo ví dụ sau:
Ví dụ: Nhập thưởng cho nhân viên theo bảng mẫu dưới đây:
Cách làm:
Bước 1: Tại ô E6, nhập công thức:
=IF(D6>=VLOOKUP(C6;$C$17:$E$20;2;0);VLOOKUP(C6;$C$17:$E$20;3;0);”0″)
Sau đó nhấn phím “Enter”.
Lưu ý: Bảng thưởng cần phải được cố định nếu bạn muốn sao chép công thức để tính cho các nhân viên khác. Để cố định, bạn chỉ cần khoanh vùng bảng thưởng “C17:E20” rồi nhấn phím F4 là được.
Bước 2: Sao chép công thức xuống các ô bên dưới để tính phần trăm thưởng cho nhân viên.
Lưu ý: Nhân viên không đạt đủ điều kiện sẽ không được nhận thưởng. Khi đó, kết quả trả về sẽ bằng “0”.
Hàm Vlookup nhiều điều kiện
Hàm Vlookup nâng cao có thể có 1 điều kiện, 2 điều kiện hoặc nhiều điều kiện. Để giải quyết bài toán này, người ta thường sử dụng thêm các cột phụ để tối giản số điều kiện xuống mức thấp nhất.
Để hiểu rõ hơn về cách sử dụng, bạn đọc có thể cách sử dụng hàm Vlookup có 2 điều kiện dưới đây:
Ví dụ: Dò tìm điểm tổng kết theo họ tên của các bạn học sinh theo bảng mẫu sau:
Bước 1: Tạo một cột phụ.
Tại ô E6, nhập công thức: =C6&” “&D6 hoặc =CONCATENATE(C6;” “;D6) để nối chuỗi họ và tên lại với nhau.
Lưu ý: Cột phụ phải được đặt phía bên trái của cột “Điểm tổng kết”.
Bước 2: Sao chép công thức xuống các ô còn lại. Ta thu được kết quả như sau:
Bước 3: Để dò tìm điểm tổng kết của bạn Trần Anh Tuấn, tại ô C15 nhập công thức sau:
=VLOOKUP(C15&” “&D15;E6:F12;2;0).
Trong đó:
- C15&” “&D15: Là điều kiện dò tìm được ghép bởi 2 ô tạo thành ‘Trần Tuấn Anh”.
- E6:F12: Chính là bảng dò tìm.
- 2: Có nghĩa là giá trị sẽ được trả về cột 2.
- 0: Kiểu dò tìm tương đối.
Kết hợp hàm Vlookup và Hlookup
Trong thực tế, hàm Vlookup và Hlookup thường xuyên được kết hợp với nhau. Nhờ sự kết hợp này mà công việc tính toán trở nên đơn giản và nhanh gọn hơn. Dưới đây là bài tập hàm Vlookup minh họa:
Ví dụ: Tính tổng tiền mà khách hàng phải trả.
Phân tích đề bài:
- Mã số khách hàng sẽ bao gồm 2 thông tin chính: 3 ký tự đầu tiên là loại phòng và 2 ký tự còn lại là đơn giá tiền ăn được tính theo ngày. Tổng số tiền phải trả được tính bằng tiền ăn + tiền ở.
- Tiền ăn được tính dựa trên thông tin của bảng “Biểu giá suất ăn theo ngày”. Ta sử dụng hàm Hlookup để tham chiếu thông tin cột của Mã số và Loại suất ăn rồi đem nhân với số ngày ở.
- Tiền ở được tính dựa trên trên “Biểu giá phòng”. Ta sẽ sử dụng hàm Vlookup để tham chiếu Mã số + Loại phòng thì sẽ tính được tiền ở.
Bước 1: Tại ô G6, nhập công thức:
=VLOOKUP(LEFT(D6,3),$A$15:$B$19,2,0)*E6 + VLOOKUP(LEFT(D6,3),$A$15:$C$19,3,0)*F6 + HLOOKUP(RIGHT(D6,2),$E$14:$J$15,2,0)*(C6-B6).
Lưu ý: Bảng ‘Biểu giá phòng” và “Biểu suất ăn theo ngày” cần được cố định bằng phím F4.
Bước 2: Nhấn Enter cho ra kết quả rồi copy công thức xuống những dòng bên dưới để hoàn thành. Ta được kết quả như sau:
Hàm Sumif kết hợp Vlookup
Ví dụ: Hãy tính tổng số lượng bán của mặt hàng theo điều kiện dưới đây:
Theo đề bài, ta thấy có 3 điều kiện cần phải thỏa mãn:
- Thời gian tính bắt đầu từ ngày 1/6/2018
- Thời gian kết thúc là ngày 30/6/2018.
- Mặt hàng cần phải tính là Mần Hà Giang loại 2 (có mã là M102).
Như vậy, để giải quyết đề bài trên, chúng ta phải kết hợp hàm Sumifs và Vlookup.
Tại ô F15, bạn nhập theo công thức:
=SUMIFS(C2:C15,B2:B15,”>=”&F11,B2:B15,”<=”&F12,A2:A15,VLOOKUP(F13,E2:F8,2,0)).
Sau đó nhấn Enter là được.
Cách dùng hàm Vlookup tìm dữ liệu trong nhiều sheet
Ví dụ: Tính lương cho nhân viên với: Sheet 1 – Danh sách nhân viên và Sheet 2 – Bảng lương.
Bài làm:
Bước 1: Tại ô E4 của sheet 1, bạn nhập công thức sau:
=VLOOKUP(Sheet1!D4, Sheet2!$A$4:$B$8,2,0).
Trong đó:
- Lookup_value: Là ô D4, nhưng vì nằm trong sheet 1 nên phải chọn là Sheet1!D4.
- Table_array: Là bảng dò tìm, phần này nằm ở sheet 2 nên nhập giá trị Sheet2!$A$4:$B$8. Nhớ cố định bảng dò tìm lại nhé!
- Col_index_num: Là cột thứ 2 của bảng lương nên nhập giá trị là 2.
- Range_lookup: Ở đây chúng ta tìm kiếm chính xác với bảng dò tìm nên nhập số 0.
Bước 2: Copy công thức để tính cho các nhân viên khác.
Tìm hiểu về lỗi #n/a trong hàm Vlookup
Lỗi #n/a được hiểu là lỗi không khả dụng, thường xuất hiện khi không thể tìm thấy được giá trị tra cứu. Lỗi hàm Vlookup này thường do các nguyên nhân sau:
- Đánh máy sai hoặc sắp xếp không chính xác các giá trị tra cứu: Nguyên nhân này thường xuất hiện khi bạn phải làm việc với khối lượng dữ liệu lớn hoặc giá trị tra cứu được gõ trực tiếp.
Cách khắc phục: Kiểm tra lại để tìm ra chỗ sai và sửa.
- Tìm kiếm gần đúng trong hàm Vlookup: Lỗi này thường xuất hiện trong trường hợp giá trị tra cứu nhỏ hơn so với giá trị nhỏ nhất trong bảng tra cứu hoặc cột tra cứu không được sắp xếp theo thứ tự tăng dần.
Cách khắc phục: Sắp xếp lại cột tra cứu theo thứ tự tăng dần.
- Cột tra cứu không phải là cột bên trái trong bảng dữ liệu: Vlookup không thể dò tìm được phía bên trái của cột tra cứu. Do vậy, cột tra cứu luôn phải là cột bên trái, phía ngoài cùng của bảng dò tìm.
Cách khắc phục: Nếu không thể tái cấu trúc để cột tra cứu là cột ở bên trái, bạn có thể sử dụng linh hoạt hàm Match và Index để thay thế cho Vlookup.
- Số được định dạng theo kiểu văn bản: Thường xuất hiện khi nhập dữ liệu từ cơ sở dữ liệu bên ngoài hoặc gõ dấu nháy đơn trước khi gõ số.
Cách khắc phục: Nhấp vào biểu tượng lỗi rồi chọn mục “Convert to number” để sửa. Nếu nhiều con số bị ảnh hưởng, hãy chọn tất cả rồi nhấn chuột phải, chọn Format Cells => Number tab => Number. Cuối cùng nhấn OK là được!
Bài viết tham khảo:
Tổng hợp các cách xuống dòng trong excel mà bạn nên biết
Tham khảo ngay cách chạy spin coin master |nhận spin free mỗi ngày
Mong rằng với những thông tin chia sẻ trên sẽ giúp bạn đọc phần nào hiểu rõ về cấu trúc và cách dùng hàm Vlookup. Nếu bạn có thắc mắc hay góp ý cho bài viết, hãy để comment vào bình luận bên dưới cho chúng tôi biết nhé!
