Hàm Vlookup trong Excel: cách dùng, ví dụ và cách khắc phục lỗi

Hàm VLOOKUP trong Excel là từ viết tắt của Vertical Lookup có nghĩa là tìm kiếm, dò tìm giá trị theo chiều dọc. Đây là một trong những hàm Excel được sử dụng phổ biến trong nhiều loại công việc hiện nay.

Nếu bạn chưa biết hàm VLOOKUP để làm gì và trong quá trình sử dụng thường hay gặp phải lỗi nào thì bài viết dưới đây của Gitiho sẽ giải thích chi tiết cho bạn.

Hàm VLOOKUP là hàm gì? 

Hàm VLOOKUP trong Excel dùng để dò tìm dữ liệu trong một bảng, một phạm vi theo hàng dọc và nó trả về dữ liệu tương ứng theo hàng ngang tương ứng. 

Cú pháp VLOOKUP trong Excel

=VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup)

Trong đó: 

• Lookup_value: là giá trị mà bạn 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: giới hạn bảng tính để bạn dò tìm dữ liệu
• Col_index_num: là 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. Bạn điền là 1 (mặc định) thì nó sẽ tìm kiếm giá trị trong khoảng và cho ra kết quả tương đối, nếu điền 0 thì sẽ cho ra kết quả dò tìm chính xác. 

Ví dụ đơn giản về hàm VLOOKUP trong Excel

Để hiểu hơn về hàm VLOOKUP, bạn có thể theo dõi ví dụ sau đây:

Đề bài: Hãy dựa vào bảng tra cứu thông tin (cột H:I) để điền nội dung vào cột nhóm (E). Có nghĩa là bạn phải dò tìm xem bộ phận kinh doanh, lập trình viên, sale, marketing… đang thuộc nhóm nào dựa vào bảng tra cứu thông tin kia. 

Bạn sẽ sử dụng công thức VLOOKUP tại ô E3 như sau: 

=VLOOKUP(D3,$H$2:$I$6,2,0)

• D3: là giá trị mà bạn cần dò tìm, tức là bạn muốn xem bộ phận kinh doanh thuộc nhóm nào?
• H2:I6: là bảng dữ liệu để dò tìm, tức là bảng đó chứa thông tin mà bạn muốn dò tìm.
• 2: là cột trả về kết quả dò tìm. Như trong ví dụ này cột I là cột 2 (trong bảng dò tìm)
• 0: sẽ trả về kết quả dò tìm tuyệt đối.

Và kết quả sau khi sử dụng hàm tại cột E như sau:

Lưu ý khi sử dụng hàm VLOOKUP

• Hàm VLOOKUP chỉ tìm kiếm từ trái qua phải. 
• Cần phải cố định bảng dữ liệu bằng F4. 
• Số cột tìm kiếm phải nhỏ hơn hoặc bằng số cột trong bảng tìm kiếm. 
• Cột chứa giá trị tìm kiếm luôn đứng đầu.

Cách sử dụng hàm VLOOKUP cực dễ

Có 2 cách dùng hàm VLOOKUP trong Excel là bạn có thể nhập trực tiếp vào ô hoặc truy cập thông qua thanh menu. 

Nhập trực tiếp công thức hàm VLOOKUP xcel vào ô

Ví dụ ở trên chính là cách sử dụng hàm VLOOKUP theo cách nhập trực tiếp. Cách này đơn giản và nhanh chóng nên được hầu hết người dùng áp dụng. 

Sử dụng thanh menu

Để có thể nhập hàm bằng thanh menu các bạn thao tác như sau

Chọn Formulas -> Lookup & Reference -> VLOOKUP. 

Sau đó bạn điền các giá trị vào từng ô. Nhấp OK là xong.

Hướng dẫn dùng hàm VLOOKUP ngược (từ phải sang trái)

Thông thường hàm VLOOKUP trong Excel chỉ có thể dò tìm các giá trị theo chiều từ trái qua phải:

Và điều gì sẽ xảy ra nếu tệp dữ liệu của bạn được thay đổi và bạn cần phải tra cứu các giá trị từ phải sang trái:

Trong trường hợp này, bạn không thể dùng hàm VLOOKUP mà phải dùng hàm XLOOKUP trong Excel để thực hiện. 

Cách dùng hàm XLOOKUP dò tìm ngược thay thế VLOOKUP

Chúng ta có thể sử dụng hàm XLOOKUP thay thế hàm VLOOKUP để dò tìm ngược dữ liệu. Đây là một hàm khắc phục hoàn hảo các nhược điểm mà hạn chế của VLOOKUP. Tuy nhiên, hàm này chỉ có trên phiên bản Office 365.

=XLOOKUP("lookup_value", lookup_array, return_array, "[if_not_found]", [match_mode], [search_mode])

Trong đó:

lookup_value: là giá trị mà bạn muốn tra cứu. 

lookup_array: là bảng dữ liệu chứa thông tin mà bạn muốn tìm kiếm. 

return_array: là bảng dữ liệu để trả về giá trị phù hợp.

if_not_found:  dùng để trả về một văn bản đã chỉ định nếu giá trị “lookup_value” chưa được tìm thấy. 

match_mode: là tham số tùy chọn bao gồm:

• 0: trả về kết quả chính xác (mặc định)
• -1: nếu không thể tìm thấy kết quả khớp chính xác thì mục nhỏ nhất tiếp theo sẽ được trả về. 
• 1: nếu không tìm thấy kết quả khớp chính xác, mục lớn nhất tiếp theo sẽ được trả về. 
• 2: một sự kết hợp ký tự đại diện trong đó *, ?, ~ có ý nghĩa đặc biệt. 

search_mode: là tham số tùy chọn để tìm chế độ tìm kiếm

• 1: tìm kiếm bắt đầu từ mục đầu tiên. 
• -1: tìm kiếm bắt đầu từ mục cuối cùng. 
• 2: tìm kiếm nhị phân dựa trên “lookup_array” được sắp xếp theo thứ tự tăng dần. Nếu không được sắp xếp, kết quả không hợp lệ sẽ được trả về. 
• -2: tìm kiếm nhị phân dựa trên “lookup_array” được sắp xếp theo thứ tự giảm dần. Nếu không được sắp xếp, kết quả không hợp lệ sẽ được trả về. 

Ví dụ về hàm XLOOKUP

Chúng ta sẽ áp dụng công thức XLOOKUP trong trường hợp dưới đây.

=XLOOKUP("Lab Only",H2:H12,E2:E12,"Not found")

• “Lab Only”: giá trị tra cứu. 
• H2:H12: mảng dữ liệu để tìm kiếm giá trị tra cứu
• E2:E12: mảng dữ liệu trả về giá trị phù hợp
• “Not found”: văn bản để trả lại nếu giá trị tra cứu chưa được tìm thấy.

Cách khắc phục lỗi hàm VLOOKUP

Trong quá trình áp dụng hàm VLOOKUP, sẽ có một số lỗi xuất hiện như #N/A. Vậy làm thế nào để xử lý và khắc phục. Gitiho sẽ đưa ra 5 lý do phổ biến nhất và cách giải quyết nhanh gọn nhé!

Không trả về chính xác kết quả cần dò tìm

Khi sử dụng VLOOKUP, kết quả trả về không chính xác. Bộ phận kinh doanh thuộc nhóm A nhưng kết quả trả về lại là nhóm D.

Trong trường hợp này, bạn có thể nhập FALSE hoặc 0 là tham số để tìm kiếm một kết quả chính xác.

=VLOOKUP(D3,$H$2:$I$6,2,FALSE)

Không cố định vùng dữ liệu

Khi bạn kéo công thức từ ô thứ nhất để áp dụng cho các ô phía dưới, kết quả trả về là #N/A, tức là bị lỗi. 

Như đã đề cập ở trên, bạn cần cố định công thức VLOOKUP bằng cách nhấn F4. Hoặc ví dụ công thức ở dạng có định sẽ trông như này: $H$6:$I$9

Xem thêm: CÁCH SỬA LỖI #N/A HÀM VLOOKUP TRONG EXCEL

Chèn thêm cột vào bảng khiến kết quả bị sai lệch

Thông thường, cột hay col_index_num trong VLOOKUP thường linh động nhưng nếu chèn thêm cột vào bảng thì cột mới sẽ gây ảnh hưởng đến kết quả, dẫn đến sai lệch. 

Ví dụ cột “Quantity” là cột thứ 3 trong bảng, nhưng khi chèn thêm một cột mới, nó trở thành cột thứ 4. Trong trường hợp này, bạn cần phải kết hợp hàm MATCH vào trong hàm VLOOKUP. 

Khi dùng hàm MATCH thì giá trị của col_index_number luôn đúng, cho dù bạn có thêm cột vào bảng tính cũng không ảnh hưởng đến kết quả của hàm.

Công thức như sau: 

=VLOOKUP(I3,B3:G11, MATCH(J2,B2:G2,0),FALSE)

Bảng của bạn được mở rộng

Khi chèn thêm các hàng vào bảng dữ liệu, bạn cần phải sửa lại hàm VLOOKUP để đảm bảo rằng những hàng mới cũng được thêm vào các giá trị cần dò tìm. 

Như ở bảng dưới đây, hàm VLOOKUP không thể dò tìm hết các giá trị trong toàn bộ bảng:

Vậy làm như thế nào?

Bạn có thể đặt tên cho bảng đó (như FruitList trong hình trên) để khi dùng hàm chúng ta chỉ việc viết tên bảng thay vì cập nhật liên tục cho hàm các hàng được thêm mới vào bảng.

Chọn bảng bạn muốn đặt tên > chọn Home -> Format as Table rồi chọn mẫu mà bạn thích.

Trong hộp thoại Format as table, kiểm tra lại lần nữa xem vùng dữ liệu bảng mà bạn muốn định dạng đã đúng chưa, sau đó nhấn OK.

Nhấp chọn thẻ Design dưới thẻ Table Tools và thay đổi tên bảng. 

Hàm VLOOKUP trong Excel và Google Sheet có sự khác biệt nào không?

Về cơ bản, hàm VLOOKUP Excel và Google Sheet có cùng logic và cú pháp giống nhau. Vì vậy, nếu chuyển Excel từ Google Sheet thì bạn sẽ không gặp bất kỳ rắc rối nào. 

Xem thêm: Cách sử dụng hàm VLOOKUP trong Google Sheets và Excel với các ví dụ cụ thể

Kết luận

Trên đây là những thông tin về cách dùng hàm VLOOKUP trong Excel cũng như một cách khắc phục nếu gặp lỗi dùng hàm, hy vọng sẽ hữu ích với bạn đọc trong quá trình làm bài tập hàm VLOOKUP. Nếu muốn nâng cao kỹ năng tin học văn phòng, đừng bỏ qua các khóa học Excel online tại nền tảng giáo dục trực tuyến hàng đầu Gitiho bạn nhé!