Hàm Vlookup trong excel là một hàm rất thường sử dụng trong tính toán và đây cũng là hàm khó sử dụng. Trong tính toán bảng tính excel chắc chắn bạn sẽ phải sử dụng hàm Vlookup. Cùng tìm hiểu cú pháp cũng như cách sử dụng hàm Vlookup trong Excel trong hướng dẫn dưới đây.
Contents
Cú pháp và cách sử dụng hàm Vlookup
Khái niệm
Hàm VLOOKUP dò tìm hàng (row) chứa giá trị ở cột đầu tiên (bên trái) của một bảng dữ liệu. Nếu tìm ra sẽ tìm trong hàng và lấy giá trị cột bạn chỉ định.
Cú pháp chi tiết: =VLOOKUP(lookup_value, table_array, col_index_num [, range_lookup])
lookup_value: Giá trị dò tìm.
table_array: Bảng để dò tìm (vùng tham chiếu hoặc là tên (name) một vùng).
col_index_num: Thứ tự cột cần lấy dữ liệu trên bảng giá trị dò.
range_lookup:phạm vi tìm kiếm.
+ TRUE
+ FALSE (hoặc 0)
Dò tương đối
Ví dụ về dò tìm tương đối sẽ hiển thị dưới bảng sau đây.
Áp dụng công thức tại cột D6 là: =VLOOKUP(C6,$C$16:$D$20,2,1)
Sau khi dùng hàm vlookup kết quả sẽ trả về như bảng bên dưới:
Dò tuyệt đối
Dò tìm tuyệt đối trong vlook up hay còn có tên gọi là dò tìm chính xác.
VD: Hãy điền thông tin Quê quán và trình độ nhân viên vào bảng căn cứ vào mã nhân viên.
Nhập về thông tin quê quán của nhân viên tại ô E6: =VLOOKUP(A6,$D$12:$F$17,2,0).
Giải thích:
A6: giá trị đem dò tìm
$D$12:$F$17: bảng dò tìm
2: số thứ tự cột dữ liệu bảng dò
0: Kiểu dò tìm chính xác
Nhập tại trình độ như sau: ô F6 bạn sẽ nhập : =VLOOKUP(A6,$D$12:$F$17,3,0)
Hàm vlook up còn có thể kết hợp với các hàm khác như hlook up, left, right, match để tính toán.
Định nghĩa các hàm hlookup, left, right, match:
- Hàm HLOOKUP là hàm giúp người dùng tìm kiếm một giá trị ở dòng đầu tiên của một bảng dữ liệu. Nếu tìm thấy sẽ trả về giá trị ở cùng trên cột với giá trị tìm thấy trên hàng mà chúng ta chỉ định.
- Hàm LEFT là hàm cắt chuỗi ký tự bên trái
- Hàm RIGHT là hàm cắt chuỗi ký tự bên phải
- Hàm MATCH là tìm kiếm một mục xác định trước trong một phạm vi ô, và trả về vị trí tương đối (hay chính là số thứ tự) của mục trong phạm vi đó.
Cho bảng có dữ liệu dưới đây và quy ước như sau:
– Bảng 1: BẢNG THỐNG KÊ TIÊU THỤ GẠO
– Bảng 2: BẢNG TRA TÊN HÀNG, TÊN HÃNG SẢN XUẤT VÀ ĐƠN GIÁ
Yêu Cầu Cần Giải Quyết:
Câu 1: Căn cứ vào 2 ký tự bên trái và 2 ký tự bên phải của Mã SP trong Bảng 1, hãy tra trong Bảng 2 đề điền giá trị cho cột Tên Hàng – Tên Hãng Sản xuất (Tính dữ liệu cho cột C5:C10).
VD: KD-NĐ Tức là gạo Khang Dân – Nam Định.
Câu 2: Hãy điền Đơn Giá cho mỗi mặt hàng đựa vào Mã SP ở Bảng 1 và tra ở Bảng 2. (Tính dữ liệu cho cột D5:D10).
Câu 3: Tính thành tiền = Số lượng * Đơn giá.
Giải bài toán:
Câu 1:
– Giải thích: Chúng ta cần đưa ra được công thức lấy dữ liệu Tên Hàng và Tên Tỉnh Sản xuất, sau đó ghép 2 công thức tính toán này lại thì sẽ ra được đáp án Câu 1.
+ Lấy Tên hàng: Lấy 2 ký tự bên trái trong cột Mã SP ở Bảng 1 (A5:A10) so với 2 ký tự trong cột Mã Hàng ở Bảng 2, nhưng vùng dữ liệu ta lấy sang cả cột Tên Hàng ở Bảng 2 (A15:B20) hoặc (A15:E20).
>> Ta dùng Hàm Vlookup: Tại một ô nào đó chưa có dữ liệu, bạn nhập thử công thức sau: =VLOOKUP(LEFT(A5,2),$A$15:$B$20,2,FALSE)
Sau khi nhập xong, bấm Enter, nếu ra từ “Khang Dân”, tức là bạn đã hoàn thiện được 40% đáp án Câu 1 này rồi đó, đơn giản phải không nào, tiếp tục nhé.
+ Lấy Tên Tỉnh Sản xuất: Lấy 2 ký tự bên phải trong cột Mã SP(A5:A10) ở Bảng 1 so với 2 ký tự trong “Hàng” Mã gạo – Tên tỉnh sản xuất ở Bảng 2, vùng dữ liệu lấy sẽ (A16:E20).
>> Lấy dữ liệu so theo hàng nên ta dùng hàm Hlookup. Cũng tại 1 ô chưa có dữ liệu, bạn nhập thử công thức sau: =HLOOKUP(RIGHT(A5,2),$C$16:$E$20,2,FALSE)
Sau khi nhập xong, bạn cũng tiếp tục bấm Enter, nếu ra từ “Nam Định”, tức là bạn đã hoàn thiện được thêm 40% đáp án Câu 1 rồi. Lúc này bạn đã hình dung ra đáp án rồi đúng không nào. Công việc tiếp theo ghép 2 hàm này lại để tính toán được dữ liệu trong cột Tên Hàng – Tên Tỉnh Sản xuất
+ Ghép 2 công thức: Có nhiều công thức để ghép hay nối chuỗi lại với nhau, ở bài toán này Taimienphi.vn hướng dẫn các bạn cách nối chuỗi sử dụng hàm &. Trong bài toán này, chúng tôi sẽ sử dụng dấu gạch ngang (có dấu cách trống) để ngăn cách 2 công thức đã tính toán ra kết quả ở trước, tức là Tên Hàng và Tên Tỉnh Sản Xuất, cụ thể là: ” – ”
Tổng quan lại thì hàm sẽ như sau =+vlookup&” – “&hlookup (Không còn dấu = ở đầu hàm Hlookup nữa nhé)
Lúc đó, tại C5, bạn nhập công thức:
C5=+VLOOKUP(LEFT(A5,2),$A$15:$B$20,2,FALSE)&” – “&HLOOKUP(RIGHT(A5,2),$C$16:$E$20,2,FALSE)
Bấm Enter để xem kết quả, Nếu ra chuẩn đáp án thì phải là “Khang Dân – Nam Định”
Kết quả đã ok, ở ô tiếp theo là C6:C10, bạn chỉ cần trỏ chuột vào cạnh công thức ở ô C5 và kéo xuống C10, là xong
Tương tự như vậy,
C6=+VLOOKUP(LEFT(A6,2),$A$15:$B$20,2,FALSE)&” – “&HLOOKUP(RIGHT(A6,2),$C$16:$E$20,2,FALSE)
Câu 2:Tính Đơn giá
Công thức sẽ như sau: Tại ô D5 các bạn nhập công thức
D5=+VLOOKUP(LEFT(A5,2),$A$16:$E$20, MATCH(RIGHT(A5,2), $A$16:$E$16,0),FALSE)
Câu 3:Tính thành tiền
Câu hỏi này sẽ thường xuyên xuất hiện trong các bài toán tính toán dữ liệu như bảng lương, chi phí, tổng thành tiền, …
Công thức tính thành tiền sẽ như sau, Tại ô F5 các bạn nhập E5=+D5*E5
Một số lỗi Vlookup thường gặp
1) Lỗi #NA trong excel khi không tìm thấy, chưa xác định được giá trị.
– Nguyên nhân dẫn đến lỗi:
- Một là bạn viết sai hàm: Bảng tìm kiếm không cố định, không có chứa cột chứa dữ liệu, thứ tự cột tìm kiếm sai…
- Hai là điều kiện tìm kiếm không tồn tại trong bảng dữ liệu
- Ba là điều kiện tìm kiếm sai
Khi dùng hàm Vlookup gặp lỗi #NA khi không tìm thấy giá trị ở cột đầu tiên trong vùng điều kiện hàm Vlookup. Từ lỗi này bạn có thể biết cách sửa lỗi #NA.
2) Lỗi #REF là một lỗi ít khi gặp phải khi sử dụng hàm tìm kiếm theo cột hoặc theo hàng (hlookup). Tuy nhiên nếu gặp phải thì rất nhiều người không biết cách xử lý ra sao.
– Nguyên nhân dẫn đến lỗi:
- Do số thứ tự cột/ dòng chứa giá trị tìm kiếm lớn hơn tổng số cột vùng tìm kiếm. Số cột đó tính từ cột điều kiện tới cột/dòng cuối cùng của vùng tìm kiếm
– Cách giải quyết vấn đề:
- Mở rộng vùng tìm kiếm của hàm vlookup (Tương tự cho hàm hlookup)
- Trong trường hợp vùng tìm kiếm của bạn bị giới hạn từ cột A -> D: Tổng số cột trong vùng là 4. Và trong hàm vlookup bạn chỉ định cột chứa giá trị tìm kiếm có số thứ tự là 5 thì excel không thể hiểu được. Vì 5 lớn hơn cả tổng số cột của vùng tìm kiếm rồi nên không thể cho kết quả được.
Những lưu ý khi sử dụng hàm Vlookup
– Điều kiện tìm kiếm không phân biệt chữ in hoa hay thường:
- tindep.com hoặc tindep.com đều cho kết quả giống nhau
– Kiểu tìm kiếm chính xác được sử dụng nhiều hơn kiểu tìm kiếm tương đối. Vì mình dễ kiểm soát được kết quả theo ý hơn và dễ sử dụng hơn.
– Hàm Vlookup chỉ tìm kiếm từ trái qua phải vì thế cột đầu tiên bên trái của vùng tìm kiếm phải là cột chứa điều kiện tìm kiếm. Để tìm ngược thì có 2 cách là dùng công thức mảng hoặc kết hợp index & match.
Hướng dẫn trên là kiến thức về khái niệm hàm vlook up cũng như cách sử dụng hàm vlook up trong tính toán. Đây là hàm khó vì vậy bạn nên tìm hiểu thêm nhiều kiến thức để tính toán chính xác trong các bảng tính Excel.