Trong Excel, hàm VLOOKUP có ý nghĩa rất quan trọng. Nó giúp người dùng tra cứu thông tin sản phẩm, thông tin nhân viên hoặc tra bất kỳ thông tin nào trong excel thông qua mã. Tuy nhiên nếu nếu không sử dụng nhiều hay lần đầu biết về VLOOKUP thì chắc chắn bạn sẽ còn nhiều băn khoăn. Bài viết dưới đây, Kí tự Chất sẽ giúp các bạn lý giải hàm VLOOKUP là gì? Cách sử dụng ra sao và cách khắc phục 1 số lỗi cơ bản như thế nào? Các bạn cùng tham khảo nhé!
Xem thêm: Hàm IF trong Excel là gì ?
Nội dung chính:
Hàm VLOOKUP là gì?
Hàm VLOOKUP là gì? Hàm VLOOKUP được hiểu đơn giản là hàm dò tìm dữ liệu trong Excel. Chức năng thông dụng nhất của VLOOKUP chính là một hàm dữ liệu, nghĩa là nó sẽ được 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 các hạng mục.
Và hiện nay danh sách có rất nhiều loại khác nhau. Ví dụ như bảng danh sách về nhân sự trong công ty, các loại mặt hàng sản phẩm của công ty, danh sách khách hàng, hoặc bất kỳ sản phẩm nào đó.
Mỗi bảng cơ sở dữ liệu sẽ có các dấu hiệu nhận biết cho từng sản phẩm. Ở bảng dữ liệu trên, dấu hiệu nhận biết đặc biệt là cột “Item Code” (Mã sản phẩm). Khi muốn sử dụng hàm VLOOKUP thì bảng dữ liệu phải có cột chứa dấu hiệu nhận biết như mã hoặc ID, và buộc phải là cột đầu tiên như bảng trên.
Xem thêm: Hàm COUNT là gì?
Chức năng của hàm VLOOKUP trong Excel
Hàm VLOOKUP là gì? Chức năng ra sao? Chức năng của hàm VLOOKUP trong Excel được sử dụng khi cần tìm kiếm một giá trị tương ứng nào đó thông qua một giá trị dữ liệu từ một cột khác đã có sẵn trong bảng. Ví dụ như: dùng hàm VLOOKUP để tham chiếu, phân loại học sinh thành các loại Giỏi, Khá, Trung bình, Yếu … thông qua giá trị điểm trung bình (đã có một bảng hoặc một vùng khác quy định giá trị điểm cho các phân loại Giỏi, Khá, Trung bình, …)
Chức năng phân loại này khá giống với khi ta sử dụng hàm IF. Tuy nhiên điểm khác là hàm IF trong trường hợp này có quá nhiều điều kiện để xét nên sẽ rất rắc rối. Khi áp dụng cách dùng VLOOKUP sẽ giúp bạn đơn giản hóa hơn rất nhiều.
Công thức của hàm VLOOKUP trong Excel
Khi muốn sử dụng dùng hàm VLOOKUP hiệu quả và thành thạo, bạn sẽ bắt đầu với bước tìm hiểu công thức hàm VLOOKUP trong Excel như sau:
Công thức hàm VLOOKUP:
=VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup)
Trong đó:
- Lookup_value: Giá trị cần dò tìm, có thể điền giá trị trực tiếp hoặc tham chiếu tới một ô trên bảng tính.
- Table_array: Bảng giới hạn để dò tìm.
- Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm, tính từ trái qua phải.
- Range_lookup: tìm kiếm chính xác hay tìm kiếm tương đối với bảng giới hạn, nếu bỏ qua thì mặc định là 1.
- Nếu Range_lookup = 1 (TRUE): dò tìm tương đối.
- Nếu Range_lookup = 0 (FALSE): dò tìm chính xác.
Nếu bạn copy công thức cho các ô dữ liệu khác, bạn cần sử dụng dấu $ để cố định Table_array đề giới hạn dò tìm bằng cách thêm trực tiếp trước khai báo (VD: $H$6:$J$13), cột hoặc sử dụng nút F4 sau khi chọn bảng.
Cách nhập công thức hàm Vlookup
Cách nhập công thức hàm VLOOKUP các bạn có thể thực hiện theo các bước sau:
- Bước 1: Gõ dấu = sau đó nhập Vlookup(
- Bước 2: Bấm vào ô mã sản phẩm ở trong ví dụ này là ô B5 chứa chữ MT
- Bước 3: Sau đó nhấn dấu (,) phẩy trên bàn phím
- Bước 3: Tiếp theo Bấm chuột và quét từ ô B2 đến Ô H14 để chọn bảng cần dò
- Bước 4: 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
- Bước 5: Tiếp tục nhấn dấu (,) phẩy
- Bước 6: Tiếp theo 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 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ị)
- Bước 7: Tiếp theo nhấn dấu (,) phẩy
- Bước 8: Sau đó nhập số 0
- Bước 9: Tiếp theo nhập dấu đóng ngoặc “)” và nhấn Enter
Top 4 Ví dụ về cách sử dụng hàm VLOOKUP
Dưới đây là một số ví dụ cho kết quả tìm kiếm của hàm Vlookup trong Excel mà bạn có thể tham khảo để hiểu kỹ hơn về hàm này nhé!
Ví dụ 1: Cách tính thuế nhập khẩu theo đối tượng
Ví dụ bạn đang muốn tính thuế nhập khấu theo Đối tượng của các mặt hàng dưới đây:
Trong ví dụ trên, tại ô G5 ta gõ công thức: =VLOOKUP(D5,$D$17:$F$20,2,0)*E5
Trong đó:
- Vlookup: là hàm dùng để tìm kiếm ra thuế nhập khẩu tại Bảng quy định thuế.
- D5: Giá trị là đối tượng cần tìm ở đây là các đối tượng từ 1,2,3,4.
- $D$17:$F$20: Bảng giới hạn dò tìm, chính là D17:F20 nhưng được F4 để Fix cố định địa chỉ ô để Copy công thức xuống các ô G6->G12 thì công thức sẽ không bị thay đổi.
- 2: Thứ tự cột giá trị cần lấy, trong trường hợp này chính là cột Thuế nhập khẩu
- 0: Trường hợp này chúng ta lấy giá trị tuyệt đối nên chọn là 0 hoặc False
- E5: Chính là đơn giá sản phẩm để tính ra thuế nhập khẩu.
Với công thức trên, kết quả ta được là:
Kết quả khi kéo công thức vlookup cho các ô còn lại. Copy công thức xuống các ô G6->G12 ta được kết quả như ảnh trên.
Xem thêm: Công thức lượng giác là gì ?
Ví dụ 2: Cách dùng hàm VLOOKUP để tìm kiếm gần đúng
Làm thế nào xác định được Range_lookup=1 hay Range_lookup=0?
Trong Microsoft Office thường quy định Range_lookup=1 khi chúng ta cần tìm giá trị tương đối nghĩa là gần đúng hay giá trị hợp lý nhất khi không thể tìm thấy một giá trị chính xác.
Ví dụ: Để xếp loại học sinh dựa trên điểm trung bình trong trường hợp dưới đây thì Range_lookup=1, vì để xếp loại theo bảng tham chiếu thì ta bắt buộc phải lấy giá trị tương đối nghĩa là gần đúng, 9.1 gần với 9, 5.3 gần với 5 … hay diễn đạt theo một cách khác, chúng ta có thể đưa ra tiêu chí xếp loại như sau:
- Từ 9: xếp loại giỏi ( lớn hơn hoặc bằng 9, 9 <= x)
- Từ 6.5 đến dưới 9: xếp loại khá (6.5 <= x < 9)
- Từ 5 đến dưới 6.5: xếp loại trung bình (5 <= x < 6.5)
Để điền vào cột Xếp loại (cột D), chúng ta có thể dùng hàm VLOOKUP như sau trong ô D4:
=VLOOKUP(C4,$B$10:$C$12,2,TRUE)
hoặc
=VLOOKUP(C4,$B$10:$C$12,2,1)
Lưu ý: Khi sử dụng hàm VLOOKUP trong Excel để thực hiện tra cứu gần đúng, bảng tra cứu cần được sắp xếp theo thứ tự từ nhỏ tới lớn. Nếu bạn không sắp xếp bảng tra cứu theo thứ tự này, kết quả tra cứu dùng hàm VLOOKUP sẽ bị sai.
Ví dụ 3: Tính phụ cấp theo chức vụ
Một công ty quyết định phụ cấp cho nhân viên theo chức vụ tương ứng như bảng 2 (B16:C21). Lúc này, dựa vào danh sách nhân viên cùng với chức vụ có sẵn ở bảng 1, ta sẽ xác định mức phụ cấp tương ứng.
Các bạn thực hiện bằng cách dò tìm giá trị của chức vụ của nhân viên tại bảng 1, sau đó dò tìm tại cột 1 trong bảng 2 từ trên xuống dưới. Khi bạn tìm thấy giá trị, bạn sẽ lấy giá trị tương ứng tại cột 2 của bảng 2 để điền vào bảng 1.
Với một danh sách nhân viên vài trăm, một ngàn người thì bạn không thể làm thủ công như vậy. Đó chính là cách hàm VLOOKUP phát huy tác dụng.
Tại ô E4, bạn điền công thức: =VLOOKUP(D4,$B$16:$C$21,2,0)
- Dấu $ được sử dụng để cố định các dòng, các cột của bảng 2 khi bạn copy công thức sang các ô khác.
- 2 là số thứ tự của cột dữ liệu.
- Range_lookup = 0 (FALSE) để dò tìm chính xác.
Sau đó, bạn chỉ cần copy công thức cho các ô khác hoặc sử dụng Flash Fill và bạn đã hoàn thành việc tính phụ cấp theo chức vụ nhanh chóng.
Ví dụ 4: Xếp loại học sinh theo điểm số
Sau kỳ kiểm tra, ta có kết quả bài thi tương ứng với học sinh như bảng 1. Ta cần xếp loại theo điểm số dựa trên bảng 2 (B11:C15).
Tại ô E4, bạn điền công thức: =VLOOKUP(D4,$B$11:$C$15,2,1)
- Dấu $ được sử dụng để cố định các dòng, các cột của bảng 2 khi bạn copy công thức sang các ô khác.
- 2 là số thứ tự của cột dữ liệu.
- Range_lookup = 1 (TRUE) để dò tìm điểm số có giá trị gần nhất.
Excel sẽ lấy điểm số ở cột D4 và dò trong bảng 2. Khi thấy giá trị gần nhất của D4 trong bảng (ở đây là 8.5), Excel sẽ trả về kết quả tương ứng ở cột 2 là Giỏi.
Sau đó, bạn chỉ cần copy công thức cho các ô khác hoặc sử dụng Flash Fill và bạn đã hoàn thành việc xếp loại học sinh theo điểm số nhanh chóng.
Một số lỗi khi sử dụng hàm Vlookup và cách khắc phục
Hàm VLOOKUP là gì? Trong quá trình sử dụng hàm Vlookup thì sẽ có khả năng gặp một số sự cố nho nhỏ và đây chính là những cách khắc phục nhưng lỗi nhỏ đó.
Lỗi trả về #N/A
Một hạn chế của hàm VLOOKUP là nó chỉ có thể tìm các giá trị trên cột ngoài cùng bên trái trong Table_array, nếu không sẽ xuất hiện lỗi #N/A. Khi đó bạn hãy cân nhắc sử dụng hàm INDEX kết hợp hàm MATCH.
Như ví dụ bên dưới, Table_array là A2:C10, nên hàm VLOOKUP sẽ tìm kiếm trong cột A. Để sửa chữa trường hợp này, bạn đổi Table_array thành B2:C10, hàm VLOOKUP sẽ tìm kiếm trong cột B.
Ngoài ra, nếu không tìm thấy kết quả khớp chính xác sẽ trả về hàm sẽ trả về lỗi #N/A do dữ liệu không có trong Table_array. Lúc này bạn có thể sử dụng hàm IFERROR để đổi #N/A thành giá trị khác.
Lỗi trả về #REF!
Nếu Col_index_num lớn hơn số cột trong Table_array, bạn sẽ nhận được giá trị lỗi #REF!. Lúc này, bạn hãy kiểm tra lại công thức để đảm bảo Col_index_num bằng hoặc nhỏ hơn số cột trong Table_array.
Như ví dụ bên dưới, Col_index_num là 3, trong khi Table_array là B2:C10 chỉ có 2 cột.
Lỗi tả về #VALUE!
Nếu Col_index_num nhỏ hơn 1 trong công thức, bạn sẽ nhận giá trị lỗi #VALUE!.
Trong Table_array, cột 1 là cột tìm kiếm, cột 2 là cột đầu tiên ở bên phải của cột tìm kiếm, v.v… Vì vậy khi xuất hiện lỗi này, hãy kiểm tra lại giá trị Col_index_number trong công thức.
Như ví dụ bên dưới, Col_index_num bằng 0 dẫn đến việc xuất hiện lỗi #VALUE!.
Lỗi tả về #NAME?
Lỗi #NAME? xuất hiện khi Lookup_value thiếu dấu ngoặc kép (“). Để tìm kiếm giá trị định dạng văn bản (Text), bạn dùng dấu ngoặc kép để Excel có thể hiểu công thức.
Như ví dụ bên dưới, Cải xoăn không bỏ vào dấu ngoặc kép (“) sẽ làm xuất hiện lỗi #NAME?. Bạn sửa lỗi bằng cách thay Cải xoăn thành “Cải xoăn”.
Một số chú ý khi sử dụng hàm VLOOKUP trong Excel
- Hàm VLOOKUP là hàm không thể trả về kết quả khi cột lấy kết quả nằm về phía bên trái của cột chứa dữ liệu cần tra cứu. Phương án xử lý: sử dụng kết hợp hàm Index và Match
- Hàm VLOOKUP khi tra cứu không phân biệt chữ hoa / chữ thường.
- Lưu ý tham số cuối cùng [range_lookup] để chọn đúng TRUE hay FALSE trong trường hợp tra cứu gần đúng hoặc tra cứu chính xác.
- Khi tra cứu gần đúng, chú ý dữ liệu trong cột tra cứu cần được sắp xếp theo thứ tự tăng dần
- Khi giá trị cần tra cứu không được tìm thấy, VLOOKUP sẽ trả về lỗi #NA.
Kết luận
Trên đây, Ký tự đặc biệt đã chia sẻ cho bạn các thông tin để trả lời câu hỏi hàm VLOOKUP là gì? Cách dùng hàm VLOOKUP trong Excel. Hy vọng những thông tin này đã giúp ích cho bạn trong việc hiểu và sử dụng hàm VLOOKUP cho công việc của mình. Chúc các bạn sử dụng hàm VLOOKUP thành thạo và dễ dàng.