Hàm VLOOKUP trong Excel: Cú pháp, Cách dùng và ví dụ

 Hàm VLOOKUP trong Excel là gì? Tại sao lại được sử dụng phổ biến tại Excel? Hàm VLOOKUP có điểm hạn chế nào không? Bài viết hôm nay Sforum sẽ cùng bạn đọc tìm hiểu chi tiết về cách sử dụng hàm VLOOKUP trong Excel một cách chi tiết nhất. Hãy theo dõi bài viết để tìm ra lời giải chính xác nhé.

Xem thêm: Cách sử dụng hàm VLOOKUP kết hợp RIGHT và ví dụ chi tiết

Hàm VLOOKUP trong Excel là gì?

Hàm VLOOKUP trong Excel là hàm giúp tìm kiếm dữ liệu trong một bảng hoặc một dải ô theo cột.

Trong đó V là chữ viết tắt của từ Vertical (hàng dọc).Người dùng sử dụng hàm VLOOKUP sẽ dễ dàng tìm thấy dữ liệu, thông tin tại hàng dọc trong một bảng. Sau đó dữ liệu sẽ trả thông tin chính xác tại hàng ngang tương xứng.

Xem thêm: Cách sử dụng hàm VLOOKUP kết hợp hàm MATCH và ví dụ

Cú pháp hàm VLOOKUP trong Excel

Khi dùng hàm VLOOKUP, bạn cần đáp ứng một số yếu tố nhất định dựa theo công thức chuẩn. Cụ thể cú pháp của hàm là:

Trong đó:

• Lookup_value: Là giá trị cần dò tìm, có thể điền giá trị trực tiếp/tham chiếu một ô tại bảng tính Excel
• Table_array: Bảng giới hạn để dò tìm sản phẩm, tên nhân viên,…
• Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng người dùng cần dò tìm, tính thứ tự lần lượt từ trái qua phải.
• Range_lookup: Tìm chính xác hoặc tìm tương tại bảng giới hạn Table_array. Nếu người dùng bỏ qua thì được mặc định là 1. Trong đó Range_lookup = 1 (TRUE) là dò tìm tương đối và trường hợp Range_lookup = 0 (FALSE) là dò tìm chính xác.

Bạn nên lưu ý khi copy công thức hàm VLOOKUP vào các ô dữ liệu khác. Bạn hãy sử dụng dấu $ để cố định Table_array. Qua đó bạn sẽ thành công giới hạn sự dò tìm trước khi khai báo. Bạn có thể ấn nút F4 sau khi chọn bảng hoặc điền trực tiếp dấu $ (ví dụ $H$6:$J$13).

Lợi ích khi sử dụng hàm VLOOKUP

Nếu bạn biết cách sử dụng hàm VLOOKUP thành thạo, việc tính toán các số liệu lớn sẽ nhanh hơn. Bên cạnh đó, hàm còn được ứng dụng rộng rãi để thực hiện một số thao tác như:

• Tìm tên sản phẩm
• Tìm đơn giá
• Tìm số lượng dựa vào mã sản phẩm, mã vạch,…
• Tìm tên nhân viên
• Xếp, phân loại sản phẩm
• Phân chia nhân viên từng bộ phận

Nếu giá trị tính toán có sự thay đổi, loại hàm này sẽ hỗ trợ người dùng một cách tối đa. Nhìn chung, hàm VLOOKUP có tổng cộng 3 loại tham chiếu là:

• Tham chiếu địa chỉ tuyệt đối
• Tham chiếu địa chỉ tương đối
• Tham chiếu hỗn hợp

Xem thêm: Cách sử dụng hàm VLOOKLUP nhiều điều kiện trong Excel siêu dễ

Cách dùng hàm VLOOKUP với ví dụ cụ thể

Hàm VLOOKUP được ứng dụng đa dạng trong nhiều trường hợp khác nhau. Dưới đây, Sforum sẽ hướng dẫn bạn một số cách dùng hàm VLOOKUP:

Dùng hàm VLOOKUP giữa 2 sheet

Hàm VLOOKUP không chỉ có khả năng truy xuất dữ liệu trên cùng một sheet mà còn có thể thực hiện giữa hai hoặc nhiều sheet khác nhau. Nếu bạn chưa biết cách sử dụng hàm VLOOKUP giữa 2 sheet như thế nào, hãy xem ví dụ dưới đây ngay.

Ví dụ: Cách dùng hàm VLOOKUP giữa 2 sheet để tính lương cho nhân viên theo chức vụ. Trong đó, danh sách nhân viên ở sheet 1, mã chức vụ và lương ở sheet 2 như sau:

Để sử dụng hàm VLOOKUP trong Excel để tính lương theo chức vụ của từng nhân viên, bạn thực hiện các bước sau:

Bước 1: Tại sheet 1, nhập công thức hàm VLOOKUP như sau: =VLOOKUP(Sheet1!D3)

Bước 2: Chuyển sang sheet 2, nhập công thức hàm VLOOKUP như sau: =VLOOKUP(Sheet1!D3,Sheet2!$A$3:$B$7,2)

Bước 3: Sao chép hàm VLOOKUP đã nhập ở Sheet 2 sang ô E3 và nhấn Enter. Lúc này ở ô Lương sẽ hiển thị kết quả là 5000. So sánh với BẢNG LƯƠNG ở sheet 2, đây là kết quả chính xác.

Bước 4: Nhấp chuột vào ô E3, khi thấy hình vuông nhỏ ở góc phải dưới cùng, nhấp vào biểu tượng đó và kéo chuột đến ô E8. Kết quả lương tương ứng với chức vụ của từng người sẽ được cập nhật đầy đủ như trong hình.

Cách sử dụng hàm VLOOKUP giữa 2 file

Sau khi đã nắm cách dùng hàm VLOOKUP giữa 2 sheet, bạn cũng có thể lấy dữ liệu từ một workbook Excel khác. Khi tham chiếu ngoài, công thức sẽ chứa tên file nguồn trong dấu ngoặc vuông, tên sheet và vùng dữ liệu cần dò. Microsoft gọi dạng liên kết này là workbook link.

Ví dụ, file hiện tại cần tìm tên sản phẩm theo mã ở ô B3, trong khi bảng dữ liệu nằm tại Sheet “2” của file Book2.xlsx. Công thức có thể viết như sau:

=VLOOKUP(B3,'[Book2]2’!$B$2:$F$7,2,0)

Thực hiện theo các bước sau:

• Bước 1: Mở file cần nhận kết quả và file chứa dữ liệu nguồn trong Excel.

• Bước 2: Chọn ô cần trả kết quả, nhập =VLOOKUP(B3, rồi chuyển sang file nguồn để chọn vùng dữ liệu.

• Bước 3: Hoàn thiện cột trả kết quả và kiểu dò, chẳng hạn ,2,0) nếu cần tìm khớp chính xác.

• Bước 4: Nhấn Enter và sao chép công thức xuống các dòng còn lại, sau đó kiểm tra dữ liệu trả về.

Khi file nguồn đóng, Excel có thể hiển thị thêm đường dẫn đầy đủ đến workbook trong công thức. Nếu file bị đổi tên hoặc di chuyển, liên kết có thể cần cập nhật lại nguồn.

Cách cố định vùng dữ liệu khi dùng hàm VLOOKUP

Cố định vùng dò giúp table_array không thay đổi khi kéo công thức xuống nhiều dòng. Microsoft cũng khuyến nghị dùng tham chiếu tuyệt đối để công thức tiếp tục tra cứu trên cùng một vùng dữ liệu.

Ví dụ, thay vì:

=VLOOKUP(I6,C6:E10,2,0)

Bạn nên dùng:

=VLOOKUP(I6,$C$6:$E$10,2,0)

• Bước 1: Chọn ô chứa công thức VLOOKUP cần chỉnh sửa.

• Bước 2: Chọn vùng table_array trong công thức và chuyển địa chỉ thành dạng tuyệt đối như $C$6:$E$10

• Bước 3: Nhấn Enter rồi kéo công thức xuống, vùng dữ liệu lúc này vẫn được giữ cố định.

Cách này đặc biệt hữu ích khi bảng có hàng chục hoặc hàng trăm dòng cần áp dụng cùng một công thức.

Hàm VLOOKUP dò tìm chính xác và dò tìm tương đối

Đối số cuối của hàm VLOOKUP quyết định kiểu dò. FALSE hoặc 0 yêu cầu Excel tìm giá trị khớp chính xác, còn TRUE hoặc 1 dùng kiểu khớp gần đúng. Với kiểu gần đúng, Microsoft lưu ý cột dò đầu tiên cần được sắp xếp để tránh trả về kết quả ngoài mong muốn.

Ví dụ dò cáp chính xác:

=VLOOKUP(E2,A2:C100,3,0)

Ví dụ dò mức chiết khấu dựa trên các ngưỡng đã được sắp tăng dần:

=VLOOKUP(J3,$B$3:$D$7,2,1)

• Bước 1: Xác định dữ liệu cần khớp chính xác hay theo khoảng.

• Bước 2: Nhập 0 cho mã, tên hoặc dữ liệu cần trùng khớp, hoặc nhập 1 khi tra cứu theo ngưỡng.

• Bước 3: Nếu dùng 1, sắp xếp cột dò đầu tiên theo thứ tự tăng dần và kiểm tra lại các giá trị ở ranh giới.

Trong phần lớn trường hợp dò mã sản phẩm, mã nhân viên hoặc mã đơn hàng, FALSE thường dễ kiểm soát kết quả hơn.

Cách dùng hàm VLOOKUP với 2 điều kiện

Hàm VLOOKUP 2 điều kiện trong Excel có thể thực hiện bằng cách tạo một cột khóa phụ kết hợp hai tiêu chí. Đây là cách dễ theo dõi vì VLOOKUP vẫn tìm kiếm trên một giá trị nằm ở cột đầu của vùng dò.

Ví dụ bảng có cột G là tên sản phẩm, cột H là xuất xứ và cột I là Giá. Tạo cột F làm khóa phụ với công thức:

=G15&"_"&H15

Sau khi có công thức gộp, copy qua bảng riêng, Nếu ô F5 chứa mã trái cây và G5 sản phẩm cần tìm, công thức tra cứu Giá là:

=VLOOKUP(F5,$F$14:$I$21,4,0)

• Bước 1: Tạo cột khóa phụ bằng cách ghép hai điều kiện cần tra cứu.

• Bước 2: Tạo giá trị dò bằng đúng cấu trúc ghép đã dùng ở cột khóa.

• Bước 3: Nhập công thức VLOOKUP với FALSE(0) để yêu cầu khớp chính xác hai tiêu chí.

• Bước 4: Sao chép công thức xuống các dòng cần thiết và kiểm tra các trường hợp có dữ liệu trùng.

Nên thêm ký tự phân cách như _ khi ghép điều kiện để giảm nguy cơ hai tổ hợp dữ liệu khác nhau tạo thành cùng một chuỗi.

[Product_Listing categoryid="1217" propertyid="" customlink="https://cellphones.com.vn/laptop/van-phong.html" title="Danh sách Laptop văn phòng nổi bật tại CellphoneS!"]

Các lỗi thường gặp khi sử dụng hàm VLOOKUP

Khi dùng hàm VLOOKUP, bạn sẽ khó tránh khỏi việc gặp phải một số lỗi nhất định. Để bạn đọc có thể sử dụng thành thạo hàm, dưới đây chúng tôi đã tổng hợp lại một số lỗi như sau:

Lỗi #N/A

Trường hợp bạn không tìm giá trị trên cột ngoài cùng bên trái (trong Table_array), hàm VLOOKUP sẽ xuất hiện lỗi #N/A. Lúc này bạn cần dụng hàm MATCH kết hợp cùng hàm INDEX.

Nếu bạn không tìm thấy kết quả bởi lỗi #N/A thì khả năng cao là trong Table_array không có dữ liệu. Người dùng lúc này hãy sử dụng IFERROR để đổi #N/A thành một giá trị khác.

Đối với trường hợp trong Table_array chắc chắn có dữ liệu mà hàm VLOOKUP không tìm được. Bạn hãy kiểm tra các ô dữ liệu tham chiếu của mình một lượt. Qua đó xác định chính xác các ô không chứa ký tự không in hoặc khoảng trắng ẩn là được. Đồng thời nên đảm bảo mọi ô dữ liệu đều tuân thủ đúng định dạng.

Lỗi #REF!

Đây là lỗi xuất hiện phổ biến không kém khi sử dụng hàm VLOOKUP. Hầu hết lỗi #REF thường xảy ra khi Col_index_num lớn hơn số cột trong Table_array. Để khắc phục lỗi, bạn cần kiểm tra công thức để Col_index_num bằng hoặc nhỏ hơn số cột trong Table_array.

Lỗi #VALUE!

Lỗi #VALUE! thường xuất hiện khi Col_index_num trong công thức nhỏ hơn 1. Hầu hết cột 1 là cột tìm kiếm, trong đó cột 2 là cột đầu tiên ở bên phải. Do đó khi sử dụng hàm VLOOKUP, bạn hãy kiểm tra kỹ giá trị Col_index_number có trong công thức.

Lỗi #NAME?

Chắc chắn trong quá trình sử dụng hàm VLOOKUP, bạn sẽ ít nhất một lần gặp lỗi #NAME?. Đa phần lỗi này sẽ xuất hiện nếu Lookup_value thiếu dấu ngoặc kép (“). Khi tìm kiếm giá trị định dạng văn bản, bạn cần sử dụng dấu (“) để Excel hiểu rõ công thức.

Kết hợp hàm VLOOKUP với các hàm khác

Hàm VLOOKUP trong thực tế thường được dùng chung với nhiều hàm khác. Một số cách dùng hàm VLOOKUP trong Excel với các hàm khác như sau:

Hàm VLOOKUP và hàm LEFT/RIGHT

Hàm LEFT/RIGHT thường dùng để lấy một số lượng ký tự từ trái hoặc hoặc phải qua. Cú pháp của hai hàm này là: 

=LEFT(a;b)

=RIGHT(a;b). 

Trong đó: 

• a: Chuỗi văn bản hoặc địa chỉ ô muốn lấy ký tự
• b: Số ký tự muốn lấy tính từ bên trái (với hàm LEFT) hoặc tính từ bên phải (với hàm RIGHT). 

Thường thì kh sử dụng hàm VLOOKUP kết hợp RIGHT hay LEFT, vị trí của hàm này sẽ nằm ở chỗ giá trị cần dò tìm. Cụ thể là:

=VLOOKUP(LEFT(a;b); Table_array; Col_index_ num; Range_lookup)

Ví dụ: Cho thông tin như trong hình, hãy tạo công thức để lấy được tên ngành học của sinh viên. Lưu ý, 4 ký tự đầu của mã sinh viên là mã ngành.

Đáp án đúng cho ô C2 sẽ là =VLOOKUP(LEFT(A2;4);$A$8:$B$10;2;0), kết quả sẽ như hình dưới. Tương tự, nếu cần dùng hàm RIGHT thì cũng theo công thức và nguyên lý này.

Hàm VLOOKUP và hàm CHOOSE

Hàm CHOOSE kết hợp VLOOKUP thường sẽ đặt ở vị trí chọn bảng dò và sẽ hay áp dụng với mảng. Cụ thể là:

=VLOOKUP(Lookup_value;CHOOSE({1;2};vùng giá trị 1;vùng giá trị 2); Col_index_ num; Range_lookup)

Ví dụ: Cho thông tin như hình dưới, hãy xác định mức giá sản phẩm ở ô B14. Lưu ý, tên sản phẩm và hãng sẽ được cho trước.

Kết quả cho ô B14 là =VLOOKUP(B12&B13;CHOOSE({1;2};$A$2:$A$10&$B$2:$B$10;$C$2:$C$10);2;0).

Hàm VLOOKUP và hàm IF

Hàm IF kết hợp với VLOOKUP theo rất nhiều kiểu. Khi lồng vào trong VLOOKUP, hàm IF có thể đặt ở chỗ giá trị cần dò bảng hoặc bảng giới hạn. Nếu lồng VLOOKUP vào IF, VLOOKUP có thể lồng ở chỗ điều kiện hoặc kết quả. Tóm lại, việc lồng hàm vào vị trí nào tùy thuộc vào mục đích dùng.

Ví dụ: Cho điều kiện như bên dưới, hãy điền học phí của sinh viên. Lưu ý, tùy vào diện đóng học phí mà mỗi ngành học sẽ có học phí khác nhau.

Công thức đáp án ở ô E2 như sau:

Hàm VLOOKUP và hàm SUM

VLOOKUP và SUM cũng có thể kết hợp với nhau theo nhiều kiểu. Nếu SUM lồng vào trong VLOOKUP thường sẽ nằm ở phần giá trị cần dò. Cụ thể là:

=VLOOKUP(SUM(a;b;...);Table_array; Col_index_ num; Range_lookup)

Ví dụ: Cho thông tin như hình, hãy tính số tiền thưởng của nhân viên. Lưu ý là số hàng làm được bằng tổng số hàng của quý 1 và quý 2.

Đáp án đúng cho ô D2 là =VLOOKUP(SUM(B2;C2);$A$9:$B$11;2;0).

Hàm VLOOKUP và hàm MID

Hàm MID cũng tương tự như hàm LEFT và RIGHT, chỉ cần thêm một thành phần là vị trí ký tự bắt đầu lấy từ giữa chuỗi. Cụ thể là:

=MID(a;b;c)

Trong đó: 

• a: Chuỗi hoặc vị trí ô cần lấy ký tự.
• b: Thứ tự của ký tự bắt đầu lấy tính từ trái qua.
• c: Số ký tự muốn lấy.

Nếu kết hợp với VLOOKUP, công thức sẽ như sau:

=VLOOKUP(MID(a;b;c); Table_array; Col_index_ num; Range_lookup)

Ví dụ: Cho thông tin như hình dưới, hãy điền ngành học của sinh viên. Ngành học sẽ dựa vào mã ngành ở bảng dưới.

Hàm MID lúc này sẽ dùng để lấy mã ngành học, từ đó dùng VLOOKUP dò ngành học ở bảng dưới. Đáp án đúng cho ô C2 là =VLOOKUP(MID(A2;4;4); $A$8:$B$10; 2; 0).

Hàm VLOOKUP và hàm IFERROR

Tên hàm đúng trong Excel là IFERROR. Hàm này cho phép chỉ định giá trị trả về khi công thức phát sinh các lỗi như #N/A, #VALUE!, #REF! hoặc #NAME?.

Cú pháp kết hợp phổ biến ví dụ:

=IFERROR(VLOOKUP(E3,A3:C100,3,FALSE),"Không tìm thấy")

Trong đó, VLOOKUP thực hiện việc dò dữ liệu trước. Nếu công thức trả về lỗi, IFERROR thay lỗi bằng nội dung "Không tìm thấy", nhờ đó bảng tính dễ đọc hơn.

Tuy nhiên, không nên dùng IFERROR để che mọi lỗi mà chưa kiểm tra nguyên nhân. Chẳng hạn, #REF! có thể xuất hiện khi chỉ số cột trả về vượt quá số cột của vùng dữ liệu.

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

Khi sử dụng hàm VLOOKUP, bạn nên nhớ giá trị tra cứu cần nằm ở cột đầu tiên ở dải ô Excel. Nếu đáp ứng được yếu tố này, hàm mới hoạt động chính xác. Người dùng cần chỉ định TRUE nếu muốn nhận được kết quả khớp tương đối. Còn kết quả khớp chính xác thì cần sử dụng FALSE.

Lượng thông tin cần tìm kiếm càng nhỏ thì quá trình sử dụng hàm VLOOKUP lại càng khó. Hầu hết người dùng thường dùng loại hàm này tại một bảng tính tái sử dụng như mẫu mã. Mỗi lần nhập mã sản phẩm, hệ thống sẽ truy xuất toàn bộ thông tin về sản phẩm tương ứng.

Bên cạnh đó, còn có một số lưu ý như:

• Khi range_lookup bị bỏ qua, loại hàm này sẽ cho phép đối sánh không chính xác, người dùng có thể sử dụng đối sánh chính xác (nếu có)
• Nếu cột tra cứu chứa giá trị trùng lặp, hàm VLOOKUP lúc này chỉ khớp với giá trị đầu tiên
• Hàm chỉ lấy dữ liệu từ các cột bên phải ở vị trí đầu tiên trong bảng
• Hàm VLOOKUP không phân biệt chữ hoa chữ thường
• Hàm VLOOKUP cho phép sử dụng người dùng các ký tự đại diện

Những hạn chế lớn của hàm VLOOKUP

Hàm VLOOKUP trong Excel dễ áp dụng nhưng cấu trúc của hàm tạo ra một số giới hạn cần lưu ý. Giá trị cần dò phải nằm ở cột đầu tiên của table_array, còn dữ liệu trả về phải nằm về phía bên phải của cột này.

Một số hạn chế thường ảnh hưởng đến bảng tính gồm:

• Chỉ dò từ trái sang phải: VLOOKUP không thuận tiện khi cột kết quả nằm bên trái cột chứa khóa.
• Phụ thuộc chỉ số cột: Đối số col_index_num dùng số thứ tự cột, vì vậy việc thay đổi cấu trúc bảng có thể khiến công thức cần kiểm tra lại.
• Dò gần đúng yêu cầu dữ liệu phù hợp: Khi dùng TRUE, cột đầu tiên cần được sắp xếp đúng để hạn chế kết quả sai.
• Không được thiết kế để lọc toàn bộ bản ghi trùng: Nếu cần lấy nhiều dòng thỏa cùng điều kiện, các hàm mảng như FILTER có thể phù hợp hơn trên phiên bản Excel hỗ trợ.

Microsoft hiện gợi ý XLOOKUP như một lựa chọn linh hoạt hơn vì hàm này có thể dò theo nhiều hướng và mặc định tìm khớp chính xác.

So sánh VLOOKUP, HLOOKUP và XLOOKUP

VLOOKUP, HLOOKUP và XLOOKUP đều hỗ trợ tra cứu dữ liệu nhưng khác nhau về hướng dò và cấu trúc công thức. HLOOKUP tìm giá trị ở hàng đầu của bảng rồi trả dữ liệu từ một hàng được chỉ định, trong khi VLOOKUP tìm ở cột đầu. XLOOKUP tách vùng dò và vùng trả kết quả nên linh hoạt hơn về vị trí dữ liệu.

Tiêu chí	VLOOKUP	HLOOKUP	XLOOKUP
Hướng tổ chức dữ liệu	Theo cột	Theo hàng	Theo hàng hoặc cột
Vị trí vùng dò	Cột đầu của bảng	Hàng đầu của bảng	Chọn riêng vùng cần dò
Hướng trả dữ liệu	Chủ yếu từ trái sang phải	Từ trên xuống dưới	Có thể trả dữ liệu ở nhiều hướng
Khớp chính xác	Dùng FALSE	Dùng FALSE	Mặc định khớp chính xác
Khớp gần đúng	Có	Có	Có, tùy match_mode
Cách chọn vùng trả về	Số thứ tự cột	Số thứ tự hàng	Chọn trực tiếp return_array
Khả năng tương thích	Phù hợp nhiều phiên bản Excel	Phù hợp nhiều phiên bản Excel	Không có trên Excel 2016 và Excel 2019

Nếu bảng dữ liệu được bố trí dọc và cần tương thích với nhiều phiên bản Excel, VLOOKUP vẫn dễ triển khai. HLOOKUP phù hợp khi giá trị dò nằm theo hàng ngang. Với phiên bản Excel hỗ trợ, XLOOKUP giúp giảm phụ thuộc vào vị trí cột và số thứ tự cột.

Hy vọng sau khi theo dõi bài viết này, bạn đã nắm được một số thông tin về hàm VLOOKUP. Nếu bạn muốn sử dụng Excel khi làm việc thì loại hàm này sẽ giúp ích rất nhiều. Truy cập Sforum thêm để xem thông tin về Tin học văn phòng và hướng dẫn Thủ thuật excel hiệu quả hơn.

Nguồn ảnh: Internet và tạo bằng AI