Hàm VLOOKUP trong Excel giúp người dùng tra cứu dữ liệu nhanh chóng theo cột, đặc biệt hữu ích khi làm việc với bảng lớn. Để sử dụng hiệu quả, bạn cần hiểu cú pháp, cách áp dụng và khắc phục lỗi thường gặp.
- NOTE: Tải Ngay 70+ bài tập excel cơ bản và nâng cao
Nếu chưa quen thuộc với Excel, làm việc với dữ liệu có thể khiến bạn cảm thấy khó khăn. Hãy cùng Tải Miễn Phí tìm hiểu cách sử dụng hàm VLOOKUP để cải thiện kỹ năng của bạn.
Vlookup là gì? Công thức tính và ví dụ minh họa
I. Hàm Vlookup là gì?
VLOOKUP là hàm cơ bản và phổ biến trong Excel, cho phép người dùng dò tìm dữ liệu theo cột. Bạn có thể nhanh chóng tra cứu và nhận về kết quả mong muốn chỉ với vài thao tác.
II. Cú pháp sử dụng
- Lưu ý cú pháp hàm:
- VLOOKUP(lookup_value; table_array,col_index_num; [range_lookup])
Trong đó:
- lookup_value: Giá trị dùng để dò tìm.
- table_array: Bảng giá trị dò, để ở dạng địa chỉ Tuyệt đối (có dấu $ phía trước bằng cách nhấn F4).
- col_index_num: Thứ tự của 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 tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối).
III. Ví dụ minh họa
Với cách dò tìm tương đối và tuyệt đối của Vlookup, bạn sẽ dễ dàng thống kê dữ liệu trên bảng tính Excel, giúp việc thống kê các chi tiết để làm báo cáo lọc ra các danh sách cần thiết, công việc của bạn sẽ chính xác và rút ngắn thời gian hơn.
1. Dò tìm tương đối
VD: Căn cứ vào bảng quy định xếp loại tương ứng với điểm đã cho, tiến hành xếp loại học lực cho các sinh viên có tên trong danh sách dưới:
Ví dụ về hàm Vlookup excel
Ta sử dụng công thức ở cột D6 là: =VLOOKUP(C6,$C$16:$D$20,2,1)
Kết quả thu được:
Kết quả khi dùng hàm vlookup
2. Dò tìm tuyệt đối
Với cách dò tìm tuyệt đối thì bạn sẽ tìm được chi tiết hơn cách dò tìm tương đối.
VD: Điền thông tin Quê quán và trình độ của các nhân viên vào bảng căn cứ vào mã nhân viên đã cho tương ứng phía dưới.
Ví dụ dò tìm tuyệt đối khi dùng hàm vlookup
Để điền thông tin quê quán của nhân viên ta dùng công thức Vlookup cho ô E6 như sau: =VLOOKUP(A6,$D$12:$F$17,2,0)
A6 là giá trị đem dò tìm
$D$12:$F$17 là bảng dò tìm
2 : số thứ tự cột dữ liệu trên bảng dò
0 : Kiểu dò tìm chính xác
Tương tự để điền vào ô trình độ của nhân viên ta làm như sau:
Với ô F6 có công thức : =VLOOKUP(A6,$D$12:$F$17,3,0)
Kết quả khi dò giá trị tuyệt đối
3. Giá trị tương đối và giá trị tuyệt đối
Với giá trị tuyệt đối bạn sẽ tìm kiếm được kết quả chính xác nhất, các giá trị tuyệt đối sẽ không bị thay đổi địa chỉ dù xuất hiện ở các cột, ô mới. Do đó chúng ta sử dụng giá trị tuyệt đối trong trường hợp cần tìm chính xác nhất thông tin và sử dụng giá trị tương đối trong trường hợp tìm kiếm các kết quả chính xác, gần chính xác nhất. Để tìm hiểu thêm, bạn đọc có thể tham khảo thêm bài viết về tham chiếu các hàm trong Excel nhé.
IV. Cách kết hợp Vlookup với Hlookup, Left, Right, Match
Bài toán này là sự kết hợp giữa hàm dò tìm và các hàm Hlookup hay hàm Left, hàm Right và hàm Match.
Cho dữ liệu như bảng dưới đây với 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á
Đáp á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 Tải Miễn Phí 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ự thế,
C6=+VLOOKUP(LEFT(A6,2),$A$15:$B$20,2,FALSE)&" - "&HLOOKUP(RIGHT(A6,2),$C$16:$E$20,2,FALSE)
Thế là đã xong Câu 1 của yêu cầu bài toán đưa ra rồi, tiếp tục chúng ta giải đáp Câu 2 các bạn nhé
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 thường xuất hiện trong những bài toán tính toán dữ liệu như bảng lương, chi phí, tổng thành tiền, ... Khá là dễ rồi phải không các bạ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
V. Các lỗi thường gặp khi sử dụng
1. Lỗi #N/A
Lỗi #NA là gì? Lỗi #NA được trả về trong công thức excel khi không tìm thấy giá trị thích hợp. Khi sử dụng Vlookup, chúng ta gặp lỗi #NA khi không tìm thấy điều kiện tìm kiếm trong vùng điều khiển, chính xác ở đây là cột đầu tiên trong vùng điều kiện của hàm này.
- Tham khảo: Cách sửa lỗi #N/A trong Excel
2. Lỗi #REF!
Lỗi #REF! xảy ra khi cột được chỉ không xác định ví dụ như trong bài dưới đây Col_index_num là 3, trong khi Table_array là B2:C10 chỉ có 2 cột. Do đó hệ thống sẽ báo lỗi #REF!
- Tham khảo: Cách sửa lỗi #REF! trong Excel
3. Lỗi #VALUE!
Lỗi #VALUE! xảy ra khi cột Col_index_num nhỏ hơn 1 trong công thức. Ví dụ trong bài dưới đây Col_index_num bằng 0 dẫn đến việc xuất hiện lỗi #VALUE!.
- Tham khảo: Cách sửa lỗi #VALUE! trong Excel
4. Lỗi #NAME?
Lỗi #NAME? xuất hiện khi Lookup_value thiếu dấu ngoặc kép ("") (dấu "" để định dạng văn bản giúp Excel hiểu rõ công thức). Ví dụ bên dưới đây Cải xoăn thiếu ngoặc ké ("") làm Excel không hiểu được công thức. Sửa lỗi bằng cách thay Cải xoăn thành "Cải xoăn".
- Tham khảo: Cách sửa lỗi #NAME? trong Excel
VI. Một số lưu ý khi dùng hàm VLOOKUP
1. Sử dụng tham chiếu tuyệt đối
Sử dụng tham chiếu tuyệt đối giúp bạn copy công thức từ cột này sang cột khác sẽ không bị thay đổi.
Như ví dụ dưới, ta có công thức tại ô C13 là =VLOOKUP(B13,$B$2:$C$10,2,0). Khi copy công thức cho ô C14 Table_array sẽ giữ nguyên.
2. Không lưu giá trị số dưới dạng văn bản
Nếu dữ liệu số trong bảng dữ liệu đang để dạng văn bản như trong ví dụ dưới đây của cột A. Khi bạn nhập công thức =VLOOKUP(A8$A$2:$B$5,2,0) trong cột doanh thu dữ liệu sẽ trả về lỗi #N/A. Để chuyển dữ liệu text về số bạn chỉ cần chọn Home => Chọn Wrap Text => Chọn Number.
3. Bảng dò tìm chứa những giá trị bị trùng
Nếu bảng biểu của bạn chứa nhiều giá trị trùng nhau, hàm VLOOKUP sẽ trả về kết quả đầu tiên mà nó tìm thấy từ trên xuống dưới. Ví dụ trong bảng trả về kết quả Táo bằng 97 thay vì 23 ở dưới.
- Giải pháp 1: Nếu bạn muốn loại bỏ giá trị trùng lặp, bạn bôi đen bảng dò tìm và chọn Data => Chọn Remove Duplicates
- Giải pháp 2: Sử dụng Pivot Table để lọc ra danh sách kết quả
https://thuthuat.taimienphi.vn/huong-dan-su-dung-ham-vlookup-trong-excel-633n.aspx
Hàm VLOOKUP trong Excel giúp tra cứu và trả về giá trị phù hợp từ một bảng dữ liệu, hỗ trợ tìm kiếm theo cột một cách nhanh chóng. Hàm này có hai chế độ: dò tìm tương đối khi cần tìm giá trị gần đúng và dò tìm tuyệt đối khi yêu cầu khớp chính xác. Để nâng cao hiệu quả tra cứu, bạn có thể kết hợp VLOOKUP với MATCH nhằm linh hoạt hơn trong việc xác định cột dữ liệu cần lấy kết quả. Nếu muốn tra cứu theo hàng thay vì cột, hàm HLOOKUP sẽ là lựa chọn phù hợp. Việc hiểu rõ cách hoạt động và kết hợp linh hoạt các hàm này giúp bạn xử lý dữ liệu nhanh chóng, chính xác hơn trong Excel.