=VLOOKUP(Lookup_value, Table_array, Col_index_ num, [Range_lookup])
Giải thích:
– Lookup_value: Giá trị cần dò tìm. Bạn có thể điền giá trị trực tiếp vào công thức hoặc tham chiếu tới một ô trên bảng tính Excel.
– Table_array: Bảng giới hạn để dò tìm, bạn cần F4 để Fix cố định giá trị cho mục đích copy công thức tự động, nếu bạn sử dụng laptop, có thể bạn sẽ cần bấm phím FN+F4 để cố định tham chiếu cho vùng bảng tính này.
– 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: Là giá trị Logic (TRUE=1, FALSE=0) quyết định so sánh, tìm kiếm chính xác hay so sánh, tìm kiếm tương đối với bảng giới hạn.
+ Nếu Range_lookup = 1 (TRUE): So sánh tương đối. + Nếu Range_lookup = 0 (FALSE): So sánh chính xác. + Nếu bỏ qua đối này thì Excel hiểu là Range_lookup = 1 một cách mặc định.
Vậy khi nào thì sử dụng hàm Vlookup và khi nào dùng hàm Hlookup?
Đơn giản thôi, nếu bảng giới hạn dò tìm xếp dọc như ví dụ dưới đây thì ta dùng hàm Vlookup, còn bảng giới hạn dò tìm là ngang thì ta dùng hàm Hlookup, vậy bạn chỉ cần nhớ V – dọc, H – ngang (Trong tiếng Anh, V – viết tắt của từ Vertical, còn H – viết tắt của từ Horizontal)
Ví dụ cách sử dụng hàm Vlookup trong Excel
Cách tính thuế nhập khẩu theo đối tượng
Ví dụ. Bạn hãy 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à:
Copy công thức xuống các ô G6->G12 ta được kết quả như ảnh trên.
Cách dùng hàm VLOOKUP để tìm kiếm gần đúng
Sẽ có bạn hỏi mình, làm thế nào xác định được Range_lookup=1 hay Range_lookup=0?
Theo đúng định nghĩa của Microsoft Office 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.
Cũng với ví dụ tra cứu xếp loại dựa trên điểm số trên sử dụng hàm VLOOKUP, khi bảng điểm có một bạn được 4 điểm, thì chúng ta sẽ nhận được lỗi #N/A khi sử dụng hàm VLOOKUP. Để khắc phục lỗi này của hàm VLOOKUP, bạn có thể thêm vào bảng tra cứu một dòng dữ liệu như hình bên dưới
Cách sử dụng hàm VLOOKUP kết hợp với Data Validation trong Excel
Trong ví dụ ứng dụng hàm VLOOKUP này, chúng ta sẽ tham chiếu tới ô có Data Validation, để khi thay đổi giá trị trong danh sách dropdown validation này, thì kết quả trả về từ hàm VLOOKUP cũng được thay đổi một cách tự động.
Bước đầu tiên, chúng ta sẽ đi chuẩn bị Data Validation bằng cách chọn ô C10, sau đó bấm vào thẻ Data / Data Validation:
Trong hộp thoại Data Validation, chúng ta sẽ chọn thẻ Settings (1), sau đó trong mục Allow, chọn List (2) và cuối cùng chọn Source (3) là vùng B4:B7
Sau đó, chúng ta sẽ hoàn thành hàm VLOOKUP tham chiếu đến ô chứa Data Validation như hình
Tìm mã ngành học sử dụng hàm VLOOKUP
Yêu cầu của bài lần này của chúng ta là dựa vào bảng mã ngành để có thể tìm ra tên ngành học của sinh viên dựa trên ký tự đầu tiên của số báo danh
Trong trường hợp này, chúng ta có thể kết hợp hàm VLOOKUP với hàm LEFT như sau
Trong đó, LEFT(C10) sẽ lấy ra ký tự đầu tiên trong số báo danh được lưu trong ô C10, và hàm VLOOKUP sẽ sử dụng ký tự này cho việc tra cứu trong bảng mã ngành.
Sử dụng hàm VLOOKUP tra cứu ở 1 file Excel khác
Để sử dụng hàm VLOOKUP tra cứu dữ liệu từ một file Excel khác, chúng ta lại bắt đầu bằng việc viết hàm VLOOKUP ở file/Workbook chúng ta muốn lấy dữ liệu, trong ảnh minh hoạ là sheet Ví dụ 03 trong file Book1.xlsx. Sau khi viết giá trị muốn tìm kiếm là SP002 được lưu trong ô A2, lưu ý bạn cần bấm dấu phẩy, sau đó chọn vùng dữ liệu cần tra cứu trong file wb khác.xlsx. Excel sẽ viết công thức đúng cho chúng ta, và bạn chỉ cần hoàn thành công thức như sau:
=VLOOKUP(A2,'[wb khác.xlsx]Ví dụ 02′!$A$2:$C$7,2,FALSE)
Trong trường hợp wb khác.xlsx đóng, thì công thức VLOOKUP của chúng ta vẫn sẽ hoạt động, nhưng Excel sẽ tự động “viết lại” công thức VLOOKUP với đường dẫn tới file dữ liệu như sau:
=VLOOKUP(A2,’C:UsersthanhnguyenDesktop[wb khác.xlsx]Ví dụ 02′!$A$2:$C$7,2,FALSE)
Xem thêm: CÁCH DÙNG HÀM VLOOKUP GIỮA 2 SHEET TRONG EXCEL
Sử dụng hàm VLOOKUP trong excel và ký tự đại diện *
Giống như nhiều hàm Excel khác, hàm VLOOKUP hỗ trợ bạn sử dụng ký tự thay thế như
- Dấu * để thay thế cho bất kì số lượng ký tự nào trong tham số lookup_value
- Dấu ? để thay thế cho 1 và chỉ 1 ký tự trong tham số lookup_value
Ví dụ tìm kiếm mã sản phẩm bắt đầu hoặc kết thúc bởi một chuỗi
=VLOOKUP(“*AX”,A2:B7,2,FALSE)
Trong ví dụ này, dấu * thay thế cho cả đoạn “SP001-ABC-” trong dữ liệu của chúng ta.
Nếu bạn muốn tra cứu chuỗi có chứa chữ “GFC” ở khoảng giữa, thì công thức VLOOKUP chúng ta có thể viết công thức VLOOKUP như sau:
=VLOOKUP(“*GFC*”,A2:B7,2,FALSE)
Để công thức của chúng ta linh hoạt hơn, bạn có thể tách phần “GFC” ra 1 ô riêng – ô G1 chẳng hạn, thì công thức VLOOKUP của chúng ta sẽ như sau:
=VLOOKUP(“*” & G1 & “*”,A2:B7,2,FALSE)
=VLOOKUP(“ZZ*”,A2:B7,2,FALSE)
Trong ví dụ này, chúng ta sử dụng “ZZ*” để tra cứu và tìm kiếm ô dữ liệu bắt đầu bằng “ZZ”
Download file ví dụ (33KB)
5 chú ý khi sử dụng hàm VLOOKUP trong Excel
- Hàm VLOOKUP 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 tra cứu không phân biệt chữ hoa / chữ thường.
- Chú ý 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.
Nếu những ví dụ trên chưa đáp ứng hết những thắc mắc của bạn về hàm Vlookup thì đừng bỏ qua: VÍ DỤ HÀM VLOOKUP NÂNG CAO
Xử lý lỗi khi hàm VLOOKUP không tìm thấy giá trị cần tra cứu, lỗi #N/A
Trong trường hợp giá trị dùng để tra cứu không được tìm thấy trong bảng tra cứu, hàm VLOOKUP sẽ trả về lỗi #N/A.
Nếu bạn muốn đưa ra một thông báo rõ ràng hơn, có ý nghĩa hơn, ví dụ “Không tìm thấy số báo danh”, thì bạn có thể sử dụng hàm VLOOKUP kết hợp với hàm IFERROR trong ô C12 như sau:
Hàm IFERROR sẽ phát hiện lỗi #N/A trong trường hợp này và trả về giá trị chúng ta đưa ra là “Không tìm thấy SBĐ”.
Đây chỉ là một trường hợp lỗi phổ biến khi bạn sử dụng Hàm Vlookup ứng dụng trong công việc. Để tìm hiểu về cách sửa những lỗi khác khi sử dụng hàm Vlookup như lỗi Lỗi #REF!, Lỗi #VALUE!, Lỗi #NAME? thì đừng bỏ qua bài viết này bạn nhé! Các lỗi hàm Vlookup thường gặp nhất định bạn phải biết để tránh
5 điểm hạn chế của hàm Vlookup
Chỉ hỗ trợ tham chiếu từ trái sang phải
Phần nhiều người dùng cho rằng Vlookup chỉ hoạt động theo chiều từ trái sang phải, chứ không có chiều ngược lại. Một số khác cho rằng để có thể dùng Vlookup tham chiếu từ phải sang thì bắt buộc phải kết hợp nhiều công thức lại với nhau.
Thực tế mà nói, việc kết hợp công thức là một thao tác khá là rắc rối và không hề dễ dàng gì. Vì thế đây vẫn được coi là một hạn chế lớn của Vlookup
Giải pháp: Sử dụng công thức Match & Index
Chỉ hoạt động hiệu quả với các giá trị riêng biệt
Vlookup có xu hướng chỉ quan tâm đến giá trị tham chiếu đầu và bỏ quên các dòng khác chứa giá trị tương tự. Nếu như dữ liệu của bạn không có quá nhiều sự phân hóa thì bạn bắt buộc phải lách luật một chút để Vlookup có thể tham chiếu được giá trị bạn cần.
Giải pháp: Tạo một bảng Pivot chứa các giá trị riêng biệt và khi đó bạn có thể dùng hàm Vlookup một cách bình thường
Số thứ tự cột tham chiếu luôn cố định
Việc số thứ tự của cột tham chiếu được điền thủ công vào nội dung của công thức khiến cho thao tác sao chép sang ô tính khác gặp nhiều khó khăn và bất tiện.
Giải pháp: Bạn có thể sử dụng số thứ tự biến đổi liên tục thông qua hàm Match
Mặc định thiết lập “approximate match”
Nếu để ý kỹ, bạn có thể thấy rằng điều kiện kiểm duyệt tham chiếu ở trong nội dung hàm Vlookup là yếu tố tùy chọn (được ký hiệu bằng hai dấu ngoặc vuông []) nên nếu như bạn không điền nội dung gì vào đó thì công thức sẽ tự mặc định là “approximate match”
Bạn thật sự cần phải chú ý nếu như không muốn kết quả tham chiếu trở nên sai lệch
Giải pháp: Luôn chú ý phải thiết lập là “exact match” để có kết quả tham chiếu chính xác nhất, tuy nhiên nếu bạn vẫn muốn chọn thiết lập mặc định “approximate match” thì phải thật sự cẩn thận
Làm tốc độ phản hồi của bảng tính bị chậm đi
Một vài ý kiến cho rằng việc sử dụng quá nhiều hàm Vlookup để tham chiếu sẽ làm giảm hiệu năng của chương trình, thậm chí gây ra hiện tượng crash.
Giải pháp: Bạn không bị giới hạn số lượng hàm Vlookup cần dùng, tuy nhiên cố gắng thay thế chúng bằng lệnh Paste Special để thay thế các hàm bằng giá trị cố định, điều đó sẽ giúp chương trình hoạt động hiệu quả hơn.
Để tìm hiểu kĩ hơn về cách tra cứu gần đúng, tra cứu với kí tự đại diện dấu sao * trong hàm VLOOKUP, mời các bạn theo dõi bài viết cập nhật:
Tất cả về hàm VLOOKUP trong excel cơ bản cho người mới bắt đầu
Ngoài ra, nếu bạn thích xem video hướng dẫn về hàm VLOOKUP cũng như những hàm tra cứu khác như Index và Match, thì Thanh có chuẩn bị sẵn video sau đây để các bạn có thể tham khảo
Hi vọng với bài viết chia sẻ này các bạn sẽ nắm được chi tiết về hàm Vlookup trong excel và cách sử dụng. Trong thời gian tới Học Excel Online sẽ chia sẻ thêm về hàm Hlookup và các hàm kết hợp với hàm Vlookup và Hlookup hay được sử dụng trong công việc.
Bên cạnh đó, để giúp các bạn sử dụng Excel thành thạo, tiết kiệm thời gian tra cứu, ứng dụng linh hoạt, phát triển kỹ năng sử dụng hàm và lồng ghép hàm, Học Excel Online đã thiết kế ra chương trình Excel từ cơ bản tới nâng cao, đây là chương trình học trực tuyến đầy đủ có tính ứng dụng thực tế, có sự hỗ trợ giải đáp của giảng viên và chuyên gia Nguyễn Đức Thanh trong suốt quá trình học, học viên có thể học đi học lại nhiều lần, học linh hoạt bất kỳ thời điểm nào.
Chi tiết về khoá học, bạn vui lòng tham khảo: EX101 – Excel từ cơ bản tới chuyên gia
Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…
Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học trên.
