Hãy cùng Sforum tìm hiểu ngay hàm Vlookup là gì và cách sử dụng trong Excel từ cơ bản đến nâng cao nhé.
- Cách chuyển PDF sang Excel hiệu quả, nhanh chóng nhất
- Cách sử dụng hàm CHOOSE trong Excel đơn giản nhất
Vlookup là một trong những hàm dò tìm dữ liệu phổ biến nhất trong Excel. Tuy nhiên, vẫn còn một số bạn chưa thật sự hiểu rõ về lệnh này. Vì thế, đừng bỏ qua bài viết này nhé.
Hàm Vlookup là gì?
Chúng ta sử dụng hàm Vlookup trong trường hợp cần dò tìm dữ liệu, thông tin trong bảng hoặc dải ôtheo hàng.
Cách sử dụng hàm Vlookup từ cơ bản đến nâng cao
Công thức hàm Vlookup trong Excel
=VLOOKUP [lookup_value, table_array, col_index_num, [range_lookup]]
Trong đó:
- lookup_value: giá trị tra cứu [bắt buộc]. Giá trị bạn muốn tra cứu phải ở cột đầu tiên của phạm vi ô mà bạn chỉ định trong table_array đã chọn.
- table_array: phạm vi dải ô chứa giá trị tra cứu [bắt buộc] Cột đầu tiên trong phạm vi ô phải chứa giá trị lookup_value. Phạm vi ô cũng cần bao gồm giá trị trả về mà bạn muốn tìm.
- col_index_num: số cột trong dải ô chứa giá trị trả về [bắt buộc].
- range_lookup: Một giá trị logic xác định xem bạn muốn lên Vlookup tìm kết quả khớp tương đối [1/true] hay kết quả khớp chính xác [0/false].
Công thức hàm Vlookup nâng cao
Hàm Vlookup nâng cao nghĩa là bạn sẽ lồng các hàm khác vào trong lệnh Vlookup cơ bản, giúp giải quyết được nhiều vấn đề hơn. Một số nâng cao thường gặp:
- Vlookup lồng nhau [lấy dữ liệu từ nhiều bảng tính/dải ô, kết hợp nhiều bảng thành một bảng]
- Vlookup và IF [tra cứu dữ liệu trong bảng hoặc dải ô theo một hay nhiều điều kiện]
- Vlookup và Match [tham chiếu chéo hai trường trong dữ liệu]
- Vlookup và Indirect [tự động kéo dữ liệu từ các trang tính khác nhau]
- …
Các ví dụ cụ thể
Ví dụ 1: Điền Tên hàng dựa vào Mã SP tra trong Bảng phụ.
- Công thức sử dụng: =VLOOKUP[A2,$E$4:$F$6,2,0]
Phân tích công thức phía trên, ta có:
- A2: Giá trị “Mã SP” chúng ta cần tra cứu trong Bảng phụ để trả về kết quả “Tên hàng” trong Bảng chính.
- $E$4:$F$6: phạm vi dải ô chứa thông tin “Mã SP” [giá trị tra cứu] và “Tên hàng” [giá trị trả về], trong đó cột “Mã SP” nằm ở cột đầu tiên.
- 2: Cột thứ 2 trong phạm vi dải ô E4:F6 chứa giá trị trả về “Tên hàng”.
- 0: Kết quả khớp chính xác
Khi đó, kết quả “Tên hàng” trả về như hình minh họa.
Ví dụ 2: Tra cứu giá trị Đơn giá trong Bảng Tổng kết bán hàng dựa vào Mã hàng tra trong Bảng 1 và tùy theo Loại để lấy giá trị tương ứng.
- Công thức sử dụng hàm Vlookup: =VLOOKUP[B3,$A$15:$D$17,IF[A3=1,3,4],0]
Phân tích công thức hàm Vlookup trên, ta có:
- B3: Giá trị “Mã hàng” cần tra cứu trong Bảng 1 để trả về kết quả “Đơn giá” trong Bảng Tổng kết bán hàng.
- $A$15:$D$17: phạm vi dải ô chứa thông tin “Mã hàng” [giá trị tra cứu] và “Đơn giá” bao gồm Loại 1 và Loại 2 [giá trị trả về], trong đó cột “Mã SP” nằm ở cột đầu tiên.
- IF[A3=1,3,4]: Vì tùy theo Loại 1 hay Loại 2 mà sẽ có Đơn giá khác nhau nên chúng ta cần so sánh trong Bảng Tổng kết bán hàng, nếu Loại 1 thì sẽ lấy giá trị ở cột thứ 3, còn lại thì lấy giá trị ở cột thứ 4 và chúng đều nằm trong phạm vi dải ô A15:D17 chứa giá trị trả về “Đơn giá”.
- 0:Kết quả khớp chính xác
Khi đó, kết quả “Đơn giá” trả về như hình minh họa.
Kết luận về sử dụng hàm Vlookup trong Excel
Hi vọng qua bài viết này, bạn sẽ hiểu rõ hơn về hàm Vlookup cũng như cách sử dụng hàm này để tra cứu, lấy dữ liệu dễ dàng hơn nhé. Chúc bạn thành công.
- Xem thêm: Mẹo excel
NOTE: Tải Ngay 70+ bài tập excel cơ bản và nâng cao
Cách thực hiện này giúp bạn:- Áp dụng hàm Vlookup trong công việc- Kết hợp hàm Vlookup với các hàm Excel khác
- Sửa lỗi hàm VLOOKUP khi gặp
Hàm VLOOKUP trong Excel là hàm tìm kiếm giá trị theo cột và trả về phương thức hàng dọc, đây có thể nói là hàm excel khá thông dụng và tiện lợi với những người hay sử dụng Excel để thống kê, dò tìm dữ liệu theo cột chính xác. Trong khuôn khổ thủ thuật học excel, Taimienphi.vn sẽ trình bày tới bạn đọc 2 cách dò tìm cơ bản của hàm này.
Ví dụ hàm Vlookup, bài tập hàm Vlookup trong Excel.
Mục Lục bài viết:
I. Cú pháp hàm Vlookup.
II. Ví dụ về hàm Vlookup trong Excel.
III. Hàm Vlookup kết hợp hàm Hlookup, Left, Right, Match.
IV. Sửa lỗi #NA hàm Vlookup.
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].
1. Cách 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. Cách 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
Cách sử dụng dò tuyệt đối với hàm Vlookup
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ả dùng hàm Vlookup 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é.
III. Hàm Vlookup kết hợp hàm Hlookup, Left, Right, Match
Ở nội dung trên Taimienphi.vn đã hướng dẫn bạn tìm hiểu cơ bản về hàm Vlookup, để giúp các bạn hiểu hơn và áp dụng thực tế hơn, bây giờ chúng tôi sẽ đưa ra một tình huống bài tập cụ thể như sau. Bài toán này là sự kết hợp giữa hàm Vlookup và các hàm trong Excel như hàm Hlookup, 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 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ự 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
IV. Sửa lỗi #NA hàm Vlookup
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 hàm 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 Vlookup
Cách sửa lỗi #NA trong hàm Vlookup:
Tham khảo: Cách sửa lỗi #NA trong hàm Vlookup
Trong Excel, hàm Vlookup được sử dụng phổ biến giúp bạn có thể thực hiện hàm tìm kiếm giá trị, trả về phương thức hàng dọc tốt, nhanh chóng. Hiện nay, bạn có thể áp dụng một trong hai cách dò hàm Vlookup.
Với hai cách dò tìm tương đối và tuyệt đối của hàm Vlookup, hàm dò tìm giá trị theo cột bạn dễ dàng thống kê dữ liệu trên bảng tính Excel. Vlookup giúp cho 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. Đây là hàm được dùng khá nhiều trong quá trình thao tác trên bảng tính.
Để học tốt cũng như hiểu rõ hơn về hàm tìm kiếm bạn có thể tham khảo hàm Hlookup trong Excel đã được Taimienphi.vn giới thiệu. Hàm Hlookup giúp bạn tìm kiếm và tham chiếu theo cột, hoặc theo dòng, Khi bạn sử dụng hàm này, nếu thỏa mãn điều kiện đặt ra.
Bên cạnh hàm tìm kiếm Vlookup trong Excel, bạn có thể tham khảo Hàm COUNTIF trong Excel, Hàm COUNTIF là một hàm đếm khá tiện lợi, với những điều kiện ban đầu, Hàm COUNTIF sẽ cho bạn những kết quả chính xác nhất.
Hàm tính toán cũng có khá nhiều hàm khác nhau trong Excel, và Hàm SUMIF là một trong những hàm được nhiều người sử dụng, Hàm SUMIF là hàm tính tổng có điều kiện, giúp bạn tính tổng các cột, các hàng riêng lẻ mà không phải tính tổng tất cả các dữ liệu trong file Excel
Sử dụng Excel không thể không nhắc tới Hàm MID, đây là hàm cơ bản trong Excel hỗ trợ cắt những chuỗi ký tự trong một dãy ký tự cho trước trong bảng tính Excel, hàm cơ bản MID được sử dụng rất nhiều trong các trường hợp cần lấy ra những ký tự đặc biệt nhất trong các chuỗi ký tự dài có thể lên tới hàng trăm ký tự.
Trong Excel, hàm Vlookup là một hàm rất quan trọng, được sử dụng rất nhiều để hỗ trợ trong công việc. Nếu công việc của bạn thường xuyên sử dụng Excel thì bổ sung các hàm trong Excel, trong đó có hàm Vlookup là điều rất cần thiết.
Sử dụng 2 hàm VLOOKUP để tính toán nhanh hơn Sử dụng hàm VLOOKUP để tra cứu 2 chiều Hàm Vlookup với 2 điều kiện Sử dụng hàm VLOOKUP để lấy dữ liệu từ bảng tính này sang bảng tính khác Gộp bảng bằng VLOOKUP Sử dụng hàm VLOOKUP nhiều điều kiện