Excel là một trong số những công cụ quan trọng của tin học văn phòng. Và một trong số những hàm quan trọng trong Excel chính là hàm Vlookup. Trong bài viết dưới đây, chúng ta sẽ cùng xem những đặc điểm về hàm tính quan trọng này nhé!
Để trở thành một dân chuyên nghiệp trong tin học văn phòng, bạn không thể không biết tới hàm Vlookup - được cho là trợ thủ đắc lực trong việc sắp xếp những thông tin quan trọng bằng công cụ Excel. Vậy cách dùng hàm vlookup trong Excel ra sao? Hàm vlookup nâng cao hay hàm vlookup có điều kiện là gì? Lỗi #n/a trong hàm vlookup được sử lý thế nào? Bạn có thể tìm kiếm những khóa học excel cơ bản ở đâu?
I. Hàm Vlookup là gì?
1. Khái niệm
Hàm VLOOKUP là một trong số hàm dò tìm dữ liệu trong Excel. Một trong những chức năng thông dụng nhất của hàm tính VLOOKUP là một hàm dữ liệu, nghĩa là nó sẽ thực sự hoạt động dựa trên những bảng cơ sở dữ liệu hoặc đơn giản hơn là danh sách liệt kê của các hạng mục. Danh sách thì lại hoàn toàn có rất nhiều loại. Bạn có thể lập bảng về nhân sự phụ trách trong công ty, các loại mặt hàng sản phẩm, danh sách toàn bộ khách hàng, hoặc bất kỳ cái gì liên quan cũng được. Vậy cụ thể Cách dùng hàm vlookup trong excel như thế nào? Hãy cùng học Excel cơ bản trong phần thông tin tiếp theo sau nhé!
2. Công thức
Hàm VLOOKUP trong Excel sở hữu những công thức như sau:
Hàm VLOOKUP trong Excel
Trong đó:
- VLOOKUP: Là biểu thị tên hàm
- Các tham số in đậm là các trường bắt buộc phải có.
- lookup_value: thể hiện giá trị dùng để dò tìm
- table_array: Bảng chứa giá trị chúng ta cần dò tìm, để ở dạng giá trị tuyệt đối với dấu $ được đứng đằng trước, ví dụ: $A$3:$E$40.
- col_index_num: Thứ tự chính của cột chứa các giá trị dò tìm trên table_array. Ví dụ trong bảng $A$3:$E$40, cột B chứa giá trị cần dò tìm thì col_index_num ở đây sẽ là 2; bảng $C$3:$F$40, cột E chứa giá trị dò tìm, thì col_index_num ở đây là 3.
- range_lookup: Là phạm vi mà dữ liệu tìm kiếm, TRUE tương đương với giá trị 1 (dò tìm tương đối), FALSE tương đương với giá trị 0 (dò tìm tuyệt đối). Tham số này không bắt buộc phải luôn tồn tại trong công thức.
Dò tìm tương đối chỉ có thể áp dụng trong trường hợp giá trị cần dò tìm trong table_array đã được sắp xếp sẵn và cân bằng theo thứ tự (tăng dần hoặc giảm dần hay theo bảng chữ cái). Với những bảng như vậy bạn có thể sử dụng cách dùng hàm vlookup với cách thức dò tìm tương đối, khi đó nó sẽ hoàn toàn tương tự như dùng hàm IF vô hạn vậy. Đối với các giá trị mà chúng ta cần dò tìm không thể hoặc chưa được sắp xếp thì bạn hãy dùng dò tìm tuyệt đối để tìm chính xác giá trị.
3. Chức năng
Chúng ta áp dụng cách dùng hàm vlookup trong excel khi cần tìm mọi thứ chi tiết trong bảng hoặc một phạm vi theo cột trong một bảng dò tìm xác định đã được định nghĩa trước. Như vậy, chức năng quan trọng nhất của hàm này chính là dùng để tìm kiếm giá trị cụ thể trong một bảng giá trị cho trước. Đây có thể nói là các hàm excel cực kỳ thông dụng và tiện lợi với những người hay sử dụng Excel để có thể thống kê, dò tìm dữ liệu theo cột một cách chính xác.
4. Sử dụng hàm VLOOKUP để làm gì?
Hàm VLOOKUP là hàm thường được sử dụng để hỗ trợ tra cứu thông tin trong một trường dữ liệu cụ thể hoặc cả một danh sách dựa vào những mã định danh có sẵn.
Ví dụ, chènhàm VLOOKUP kèm theo mã sản phẩm vào một bảng tính khác, nó sẽ hiển thị thông tin cụ thể nhất của sản phẩm tương ứng với mã đó. Những thông tin đó có thể chính là là mô tả, giá thành, số lượng tồn kho, tùy theo nội dung yêu cầu của công thức mà bạn viết.
Lượng thông tin cần tìm càng nhỏ thì trong quá trình viết hàm VLOOKUP sẽ càng khó khăn hơn. Thông thường bạn sẽ sử dụng hàm này vào một số những bảng tính tái sử dụng như mẫu. Mỗi lần nhập mã sản phẩm cụ thể và thích hợp, hệ thống sẽ truy xuất tất cả những thông tin cần thiết về sản phẩm một cách tương ứng.
II. Cách nhập hàm Vlookup
- Gõ dấu = sau đó thực hiện nhập “Vlookup(”
- Bấm vào ô mã sản phẩm ở trong ví dụ này là ô B5 chứa chữ MT
- Sau đó bạn nhập dấu (,) phẩy trên bàn phím
- Tiếp theo Bấm chuột và quét từ ô B2 đến Ô H14 để chọn bảng cần dò
- Tiếp theo nhấn Phím F4 trên bàn phím để cố định bảng Danh mục sản phẩm lại
- Tiếp tục nhấn dấu (,) phẩy
- Tiếp theo thực hiện nhập số 2 để chọn cột tên sản phẩm (số cột được đếm từ bên trái qua của bản mang tiêu đề Danh mục sản phẩm, nếu có nhiều cột hơn thì cứ thế đếm lên cột cần lấy giá trị)
- Tiếp theo nhấn dấu (,) phẩy
- Sau đó nhập số 0
- Tiếp theo nhập dấu đóng ngoặc ")" và nhấn Enter
III. Các ví dụ về hàm vlookup
1. Sử dụng hàm VLOOKUP để đánh giá xếp loại
Giả sử, bạn có bảng điểm các học sinh trong lớp như sau:
Bảng điểm các học sinh trong lớp
Và bảng thể hiện giá trị quy định xếp loại như sau:
Bảng thể hiện giá trị quy định xếp loại
Giờ ta sẽ sử dụng cách dùng hàm VLOOKUP trong excel để nhập xếp loại cho các học sinh. Bạn để ý thấy rằng bảng quy định xếp loại cụ thể đã được sắp xếp theo thứ tự từ thấp đến cao (từ yếu đến giỏi) trong bảng nên trong trường hợp này ta hoàn toàn có thể dùng dò tìm tương đối trong hàm vlookup có điều kiện.
Trong Excel 2 bảng này sẽ lần lượt được trình bày như sau:
2 bảng này sẽ lần lượt được trình bày
Ở đây, giá trị cần dò tìm sẽ là thông tin nằm ở cột C, bắt đầu từ dòng thứ 6. Phạm vi tìm kiếm là $A$18:$B$21, thứ tự cột chứa giá trị dò tìm là 2.
Tại ô D6, bạn nhập vào công thức: =VLOOKUP($C6,$A$18:$B$21,2,1). Đây là công thức dò tìm tương đối, nhưng bạn hoàn toàn có thể thực hiện dò tìm tuyệt đối nếu muốn (hoặc do bảng xếp loại chưa được sắp xếp theo thứ tự) bằng cách thêm 0 vào công thức như thế này:
=VLOOKUP($C6,$A$18:$B$21,2,0). Nhấn Enter.
Thêm 0 vào công thức
Nhấp chuột vào ô D6, bạn sẽ thấy xuất hiện ô vuông nhỏ ở góc dưới bên phải, bạn tiếp tục nhấp chuột vào đó và kéo dọc xuống hết bảng để sao chép công thức xếp loại cụ thể cho những học sinh còn lại.
Sao chép công thức xếp loại cụ thể
Khi đó, ta có kết quả dùng hàm VLOOKUP trong excel để xếp loại học lực học sinh như sau:
Xếp loại học lực học sinh
Ký hiệu $ sẽ giúp cố định cột C đối với hàm vlookup có điều kiện, chỉ thay đổi các hàng khi bạn thực hiện kéo công thức xuống toàn bảng. Còn $A$18:$B$21 để giúp cố định bảng quy định xếp loại, khiến nó không bị có sự sai khác thay đổi khi bạn kéo công thức.
2. Sử dụng VLOOKUP dò tìm tuyệt đối để lấy dữ liệu
Giả sử, bạn có một bảng dữ liệu liệt kê thông tin của nhân viên, lưu trữ mã nhân viên, họ tên, chức vụ. Một bảng khác lưu trữ tất cả những mã nhân viên, quê quán, trình độ học vấn. Giờ bạn muốn điền một số thông tin như quê quán, trình độ học vấn cho từng nhân viên thì phải làm cách dùng hàm vlookup trong excel như thế nào?
Giả sử bạn có bảng nhân viên và quê quán nhân viên trình bày cụ thể như sau:
Bảng nhân viên và quê quán nhân viên trình bày cụ thể
Giờ bạn muốn điền thông tin cụ thể về quê quán cho nhân viên. Tại ô D4, bạn nhập trực tiếp vào công thức dò tìm tuyệt đối như sau:
=Vlookup($A4,$A$16:$C$25,2,0)
Trực tiếp vào công thức dò tìm tuyệt đối
Rồi nhấn Enter. Nhấp vào ô vuông nhỏ đã trực tiếp xuất hiện dưới góc ô D4 và kéo xuống toàn bảng để thực hiện sao chép hàng loạt công thức cho các nhân viên khác.
Chép hàng loạt công thức cho các nhân viên khác
Để điền thông tin thể hiện trình độ cho các nhân viên, tại ô E4 bạn nhập vào công thức mang ý nghĩa là dò tìm tuyệt đối:
=VLOOKUP($A4,$A$16:$C$25,3,0)
Tại ô E4 bạn nhập vào công thức mang ý nghĩa là dò tìm tuyệt đối
Bạn tiếp tục nhấn vào phím Enter và kéo xuống để thực hiện sao chép công thức cho những nhân viên còn lại, ngay lập tức ta sẽ được kết quả như sau:
Sao chép công thức cho những nhân viên còn lại
3. Dùng VLOOKUP để trích xuất dữ liệu
Tiếp tục với bộ dữ liệu đã được trình bày của ví dụ 2, giờ ta sẽ đi tìm quê quán của 3 nhân viên cụ thể là Nguyễn Hoàng Anh, Trần Vân Anh và Nguyễn Quang Vinh. Mình đã trích xuất ra thành một bảng thông tin mới F15:G18.
Việc dò tìm này mình sẽ thực hiện trực tiếp trên bảng A3:E13, sau khi đã được điền những thông tin như quê quán và trình độ. Lúc này, bạn thực hiện nhập công thức dò tìm tuyệt đối sau vào ô G16 như sau:
=VLOOKUP($F16,$B$3:$E$13,3,0) > nhấn Enter.
Công thức dò tìm tuyệt đối sau vào ô G16
Sao chép công thức này một cách tương ứng cho 2 nhân viên còn lại ta được kết quả như sau:
Sao chép công thức này một cách tương ứng cho 2 nhân viên
Lưu ý rằng trong ví dụ này, giá trị dò là thông tin tìm nằm ở cột B, vì thế vùng dò tìm ta sẽ thực hiện tính từ cột B mà không tính từ cột A.
4. Dùng hàm VLOOKUP trên 2 sheet Excel khác nhau
Quay lại với bộ dữ liệu đã được cho ở ví dụ 2 sau khi nhân viên được điền xong những thông tin như trình độ và quê quán, ta đặt tên cho sheet cái tên để phân biệt là QTM.
Đặt tên cho sheet cái tên để phân biệt là QTM
Ở một sheet khác cũng thuộc cùng bảng tính, đặt tên là QTM1, bạn cần lấy thông tin quan trọng về trình độ và chức vụ của nhân viên với thứ tự sắp xếp của nhân viên đã hoàn toàn thay đổi. Đây mới là lúc bạn thấy được sức mạnh thực sự của hàm VLOOKUP nâng cao hay hàm VLOOKUP có điều kiện.
Hàm VLOOKUP có điều kiện
Để dò tìm dữ liệu cụ thể về "Trình độ" của nhân viên, bạn trực tiếp nhập vào công thức:
=VLOOKUP($B4,QTM!$B$3:$E$13,4,0) vào ô C4 của sheet QTM1.
Dò tìm dữ liệu cụ thể về "Trình độ"
Trong đó:
- B4 là cột chứa giá trị thông tin dùng để dò tìm.
- QTM! là tên sheet chứa bảng có giá trị cụ thể cần dò tìm, sau tên sheet bạn cần thêm dấu !
- $B$3:$E$13 là bảng chứa giá trị dò tìm và cùng với sheet chứa bảng (QTM).
- 4 là số thứ tự của riêng cột "Trình độ", tính từ cột mang tên "Họ và tên" trên sheet QTM.
- 0 là giá trị mang nghĩa dò tìm tuyệt đối.
Nhấn Enter, rồi sao chép công thức hàm vlookup nâng cao hàng loạt cho toàn bộ nhân viên còn lại trong bảng, ta được kết quả cụ thể như sau:
Sao chép công thức hàm vlookup nâng cao
Để dò tìm dữ liệu mang nghĩa "Chức vụ" của nhân viên, tại ô D4 của sheet QTM1, bạn trực tiếp nhập vào công thức:
=VLOOKUP($B4,QTM!$B$3:$E$13,2,0), nhấn Enter.
Dò tìm dữ liệu mang nghĩa "Chức vụ"
Sao chép công thức tương ứng cho các nhân viên còn lại, ta được như sau:
Sao chép công thức tương ứng cho các nhân viên còn lại
5. Hàm vlookup nâng cao kết hợp hàm Hlookup, Left, Right, Match
Để học Excel cơ bản - hàm vlookup, hãy đến với ví dụ sau. Cho dữ liệu như bảng dưới đây với những quy ước cụ thể như sau:
Bảng 1: BẢNG THỐNG KÊ LƯỢNG TIÊU THỤ GẠO
Bảng 2: BẢNG TRA THÔNG TIN TÊN HÀNG, TÊN HÃNG SẢN XUẤT VÀ ĐƠN GIÁ
Học Excel cơ bản - hàm vlookup
- Căn cứ vào vị trí 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 soát thông tin trong Bảng 2 đề điền giá trị cho cột mang thông tin Tên Hàng - Tên Hãng Sản xuất (Tính dữ liệu cho cột C5:C10) cần làm như sau:
Ví dụ, tại C5, bạn trực tiếp nhập vào công thức hàm vlookup có điều kiện:
C5=+VLOOKUP(LEFT(A5,2),$A$15:$B$20,2,FALSE)&"-"&HLOOKUP(RIGHT(A5,2),$C$16:$E$20,2,FALSE)
Bấm Enter để thực hiện xem kết quả, nếu ra chuẩn đáp án đúng nhất thì sẽ phải là "Khang Dân - Nam Định"
Kết quả đã đúng, thì ở ô tiếp theo là C6:C10, bạn chỉ cần trỏ chuột vào cạnh công thức ở ô C5 và lần lượt 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)
VD: KD-NĐ Tức là ý nghĩa gạo Khang Dân - Nam Định.
- Tiếp theo, hãy điền thông tin về Đơn Giá cho mỗi mặt hàng dựa vào Mã SP ở Bảng 1 và thực hiện tra ở Bảng 2. (Tính dữ liệu cho cột D5:D10):
Công thức sẽ được nhập như sau: Tại ô D5 các bạn trực tiếp nhập vào công thức
D5=+VLOOKUP(LEFT(A5,2),$A$16:$E$20,MATCH(RIGHT(A5,2),$A$16:$E$16,0),FALSE)
- Tính thành tiền = Số lượng * Đơn giá
Công thức tính thành tiền sẽ được thể hiện như sau, Tại ô F5 các bạn nhập E5=+D5*E5
IV. Sửa lỗi #NA hàm Vlookup
Cách 1
Học Excel cơ bản về hàm VLOOKUP cũng thường xảy ra lỗi. Để sửa lỗi #n/a trong hàm vlookup, giải pháp đầu tiên chính là việc chúng ta sử dụng hàm IFERROR để thực hiện bẫy lỗi. Hàm IFERROR có thể bắt bất kỳ lỗi nào và sau đó trả về kết quả thay thế.
Trong ví dụ dưới đây, lỗi #n/a trong hàm vlookup đã bị xuất hiện trong ô F5 vì "ice cream" không phải giá trị tồn tại trong bảng tra cứu.
=VLOOKUP(E5,data,2,0) // "ice cream" is not found
Sửa lỗi #NA hàm Vlookup
Để sửa lỗi này, ta cần sử dụng hàm IFERROR để bọc công thức VLOOKUP như ví dụ dưới đây:
=IFERROR(VLOOKUP(E7,data,2,0),"Not found")
Nếu hàm VLOOKUP lúc đầu trả về lỗi, hàm IFERROR sẽ bắt lỗi đó và trả về kết quả mang tên "Not found".
Cách 2
Trong học Excel cơ bản, hàm IFNA cũng có thể bẫy và thực hiện xử lý các lỗi #n/a trong hàm vlookup cụ thể. Cú pháp sử dụng hàm IFNA tương tự như hàm IFERROR:
=IFERROR(VLOOKUP(A1,table,column,0),"Not found")
=IFNA(VLOOKUP(A1,table,column,0),"Not found")
Ưu điểm lớn nhất của hàm IFNA chính là nó chỉ nhắm mục tiêu lỗi #n/a trong hàm vlookup. Ngược lại hàm IFERROR sẽ bắt bất kỳ lỗi nào mà hàm này phát hiện trong học Excel cơ bản. Chẳng hạn ngay cả khi nhập tên hàm VLOOKUP không viết hoa, hàm IFERROR cũng sẽ trả trực tiếp về lỗi "Not found".
Không hiển thị bất kỳ một thông báo nào (No Message): Nếu bạn vì lý do nào đó không muốn hiển thị bất kỳ thông báo nào khi bẫy lỗi #n/a trong hàm vlookup (tức là hiển thị ô trống), chúng ta có thể thay thế bằng việc sử dụng chuỗi trống ("") như dưới đây:
=IFERROR(VLOOKUP(E7,data,2,0),"")
V. Kết luận
Học Excel cơ bản hay việc sử dụng hàm vlookup nâng cao, hàm vlookup có điều kiện rất quan trọng nhất là trong những công việc văn phòng. Điều này sẽ giúp cơ hội phát triển sự nghiệp của bạn tăng lên đáng kể, chưa nói tới năng suất làm việc và hiệu quả công việc cũng sẽ được cải thiện rõ rệt nhanh chóng.
Trang chủ 
Mẫu CV 
Nhắn tin 
Tài khoản 
Thêm