hàm vlookup

Hàm VLOOKUP tìm một giá trị theo cột đầu tiên trong một bảng được chỉ định và trả về giá trị ở cột theo thứ tự được chỉ ra. Điều này được thực hiện khi bạn đặt tham số cuối cùng trong hàm bằng 0. Tuy nhiên có những biến thể khá trừu tượng cho hàm. CongNghePlus sẽ cùng các bạn khám phá để hiểu rõ nó nhé!

Khi nói đến các hàm trong Excel, có thể nói hàm VLOOKUP là một “gã trùm”, với tần suất sử dụng và công năng của nó. VLOOKUP là hàm gì, cú pháp và các mở rộng của nó ra sao, hãy cùng CongNghePlus giải mã nó bạn nhé!

hàm vlookup

VLOOKUP là hàm gì?

Hàm VLOOKUP là hàm dò tìm theo cột và khi tìm được thì trả về giá trị theo hàng tương ứng. Chữ V có nghĩa là vertical – trục dọc hay cột. Ví dụ, bạn tìm tên một học sinh trong danh sách, nếu tìm thấy thì trả về giá trị là điểm của học sinh đó.

Cách sử dụng hàm VLOOKUP cơ bản

Công thức hàm VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

 Trong đó:

  • lookup_value: Nội dung bạn muốn dò tìm
  • table_array: phạm vi bạn muốn tìm nó, là một bảng trong Excel, chẳng hạn E2:G12
  • col_index_num: số thứ tự cột trong phạm vi chứa giá trị cần trả về,
  • [range_lookup]: xác định cơ chế tìm chính xác hoặc gần đúng. Nếu range_lookup có giá trị 0 (hoặc FALSE) thì tìm chính xác, nếu là 1 (hoặc TRUE) thì tìm gần đúng. Dấu [ ] hiểu là có thể không viết nó trong hàm, khi đó giá trị mặc định của nó là 0.

Thế nào là cơ chế dò tìm chính xác và gần đúng? Chúng ta sẽ có câu trả lời trong bài viết này!

Ví dụ hàm VLOOKUP

Giả sử chúng ta có danh sách học sinh:

Bài toán dùng hàm VLOOUP
Bài toán dùng hàm VLOOUP

Cần lấy điểm của các học sinh trong danh sách phía trên xuống. Tạm thời giả sử rằng các học sinh không trùng tên nhau

Để thực hiện thao tác này, về logic chúng ta làm như sau:

Với mỗi học sinh trong bảng thứ hai, hãy tìm trong bảng thứ nhất, nếu tìm thấy thì trả về giá trị ở cột thứ ba của bảng thứ nhất.

Hàm cụ thể cho ô D11 chúng ta có là:

=VLOOKUP(C11,C3:D8,2,0) – Chú ý rằng, chúng ta đã thiết lập bảng dò tìm là C3:D8, không phải là bảng trực quan trên hình vẽ.

Khi kéo con chuột để áp dụng cho các ô dưới, chúng ta cần điều chỉnh một chút ở ô D11 để ghim phạm vi dò tìm lại:

=VLOOKUP(C11,$C$3:$D$8,2,0) hoặc =VLOOKUP(C11,C$3:D$8,2,0)

Lưu ý:

  • Hàm dò tìm mặc định dò tìm trong cột đầu tiên của phạm vi dò tìm, do đó bạn chú ý chỉ định phạm vi dò tìm sao cho cột được dò tìm nằm đầu tiên trong phạm vi đó.
  • Do đó, nếu bạn viết hàm: hoặc =VLOOKUP(C11,B$3:D$8,3,0) thì kết quả dò tìm sẽ bị lỗi.

Tiếp theo chúng ta xét một ví dụ khác để hiểu cơ chế tìm gần đúng của hàm VLOOKUP là như thế nào:

Hàm VLOOKUP tìm gần đúng
Hàm VLOOKUP tìm gần đúng

Nhiệm vụ của chúng ta là xếp hạng cho các học sinh dựa trên điểm số.

Trong ví dụ này, chúng ta thấy có vấn đề là điểm cụ thể của các học sinh không tìm thấy trong bảng phân loại. Đừng lo lắng, trong ô T9 bạn hãy nhập cú pháp

=VLOOKUP(S9,P$3:Q$6,2,1)

Chạy hàm và kéo xuống cho các ô dưới, bạn sẽ thấy nó thực hiện đúng chức năng bạn cần. Điều làm bạn thắc mắc là logic của nó phải không?

Trong quá trình dò tìm, nếu không thấy kết quả chính xác của giá trị dò tìm, Excel sẽ lấy giá trị lớn nhất không vượt quá giá trị dò tìm, thay cho giá trị chính xác, để làm cơ sở cho việc trả về kết quả. Vì lý do này, trong phạm vi dò tìm, bạn hãy sắp xếp các giá trị trong cột đầu tiên tăng dần từ trên xuống dưới để tránh gặp lỗi!

Đến đây bạn đã hiểu rõ hàm VLOOKUP trong Excel dùng để làm gì chưa ạ? Nó có phải là hàm bạn đang cần không?

Nếu vẫn chưa thực hiện được chức năng bạn mong muốn, hãy cùng CongNghePlus tìm hiểu thêm các trường hợp mở rộng và kết hợp của nó nhé!

Cách sử dụng hàm VLOOKUP nâng cao

Hàm VLOOKUP nhiều điều kiện

Trong các ví dụ nêu trên, bạn để ý là, chúng ta chỉ cần tìm trong một cột. Trong nhiều trường hợp, bài toán đặt ra buộc chúng ta phải dò tìm theo nhiều cột.

Hàm VLOOKUP hai điều kiện
Hàm VLOOKUP hai điều kiện

Trở lại bài toán lấy điểm cho học sinh, nhưng vấn đề là có những học sinh khác lớp trùng tên. Khi đó chúng ta cần phải tìm trên cả hai cột: cột tên và cột lớp. (Nếu trong lớp có các bạn cùng tên thì về nguyên tắc chúng ta phải đặt thêm biệt danh để phân biệt).

Trước hết chúng ta hãy giải bài toán theo cách thêm một cột và gộp hai cột chứa thông số dò tìm lại.

Hàm VLOOKUP hai điều kiện
Hàm VLOOKUP hai điều kiện

Sau khi gộp, bài toán trở thành dò tìm với một cột: Tìm giá trị M11 trong bảng M3:N8. Bài toán coi như được giải xong.

Tuy vậy, bạn sẽ đặt câu hỏi: Làm thế nào để không cần thực hiện thao tác thủ công là gộp các cột với nhau? Câu trả lời là: Chúng ta sẽ lồng hàm VLOOKUP với hàm CHOOSE. Vậy hàm CHOOSE là gì?

  • Hàm CHOOSE với cú pháp: =CHOOSE(index_num, value1, value2, …) sẽ trả về giá trị thứ index_num trong chuối các giá trị value1, value2, …
  • Hàm CHOOSE với cú pháp: =CHOOSE({index1, index2…}, range1, rang2, …) sẽ trả về một bảng được chọn từ các bảng theo sự lựa chọn index1, index2…

Nó thực sự trừu tượng nếu chúng ta không nhanh chóng xem qua ví dụ sau:

=CHOOSE({1, 3}, A1:A10, D1:D10, G1:G10) sẽ trả một bảng gồm hai cột A1:A10 và G1:G10

Trở lại bài toán, hàm CHOOSE tạo ra một “bảng ảo”, thay vì tạo ra một “bảng thực” như cách chúng ta làm thủ công ở trên. Bạn thấy rằng tham số thứ hai của hàm VLOOKUP là một bảng, và đó chính là chỗ chúng ta chèn vào một hàm CHOOSE

Ở ô N11 bạn gõ công thức sau và sau đó ấn tổ hợp Ctrl + Shift + Enter (trừ khi bạn dùng Excel 365):

=VLOOKUP(O11&” “&P11,CHOOSE({1,2},$O$3:$O$8&” “&$P$3:$P$8,$R$3:$R$8),2,0)

Xin được nhắc lại một lần nữa, bạn hãy gõ tổ hợp Ctrl + Shift + Enter thay vì gõ Enter như thông thường!

Khi đó Excel sẽ thêm cặp dấu {} vào đầu cuối của hàm, và nó sẽ có dạng:

{=VLOOKUP(O11&” “&P11,CHOOSE({1,2},$O$3:$O$8&” “&$P$3:$P$8,$R$3:$R$8),2,0)}

Ở đây, CongNghePlus muốn giải thích một chút về cách hoạt động của hàm CHOOSE:

Thông thường hàm CHOOSE sẽ trả về một giá trị, ví dụ như khi bạn gõ

=CHOOSE(3,A1,A2,A3,A4,A5) thì Excel se trả về giá trị của ô A3

Tuy nhiên CHOOSE có thể trả về (tạo ra) một bảng ảo nếu bạn gõ:

CHOOSE({1,2},$O$3:$O$8,$R$3:$R$8) sẽ trả về bảng gồm cột $O$3:$O$8 và cột $R$3:$R$8

CHOOSE({1,2},$O$3:$O$8&$P$3:$P$8,$R$3:$R$8)sẽ trả về bảng gồm cột $O$3:$O$8 ghép với &$P$3:$P$8 và cột $R$3:$R$8

Như vậy hàm CHOOSE trả về một bảng, phù hợp với cấu trúc của hàm VLOOKUP. Ta cũng có thể coi rằng trong trường hợp này hàm VLOOKUP sử dụng hàm CHOOSE làm một tham số.

Hàm SUMIF kết hợp VLOOKUP

Hàm SUMIF là hàm lấy tổng có điều kiện, cụ thể nó có cú pháp và hoạt động như thế nào thì bạn đọc tham khảo thêm tại đây!

Trong trường dữ liệu của chúng ta bị tách thành nhiều bảng thì chúng ta phải dò tìm trên nhiều bảng trước khi cộng. Cụ thể, chúng ta xét ví dụ

Bài toán sử dụng SUMIF
Bài toán sử dụng SUMIF

Chúng ta cần tính tổng doanh số của các nhân viên vào trong bảng C

Nếu như ở bảng A, để tính tổng doanh số theo nhân viên ta chỉ cần gõ:

=SUMIF(G2:G17,C2,H2:H7)

Và copy sang các ô phía dưới

Còn ở bảng C? ở ô D11, về logic, chúng ta thấy cần thực hiện một hàm SUMIF kiểu:

=SUMIF(G2:G17,tìm ở bảng A mã số của nhân viên có mã số C11,H2:H7)

Chúng ta thấy rằng, việc tìm ở bảng A mã số của nhân viên có mã số C11, vừa vặn một câu lệnh tìm kiếm VLOOKUP. Tuy nhiên, lệnh VLOOKUP tìm kiếm trên cột đầu tiên của phạm vi tìm kiếm, do vậy, cột tên nhân viên phải đặt trước. Nếu như không thể đổi cột tên lên trước, bạn cần sử dụng hàm CHOOSE để tạo một bảng ảo, như nội dung chúng ta đã trao đổi ở trên.

Vậy ở ô D11, bạn gõ:

=SUMIF(G2:G17,VLOOKUP(C11,B2:C7,2,0),H2:H7)

Và copy sang các ô phía dưới

Và, trong trường hợp này ta thấy hàm VLOOKUP là tham số cho hàm SUMIF.

>>> Xem thêmThành thạo cách dùng hàm SUMIF giữa 2 sheet chỉ với một số bước đơn giản

Hàm VLOOKUP và HLOOKUP

Hàm VLOOKUP được sử dụng khi ta muốn dò tìm theo cột (chiều dọc)

Tương tự như vậy, ta có hàm HLOOKUP khi dò tìm theo hàng (chiều ngang)

Chúng ta hãy so sánh các hàm này:

Nếu hàm

=VLOOKUP(lookup_value, table_array, col_index_num, 0)

thực hiện thao tác:

tìm giá trị lookup_value trong bảng table_array, nếu tìm thấy chính xác, thì trả về giá trị ở cột thứ col_index_num trong bảng table_array, cùng hàng với giá trị tìm được.

Vậy thì hàm:

=VLOOKUP(lookup_value, table_array, row_index_num, 0)

sẽ làm việc gì, bạn đã biết phải không? Vâng, đúng vậy

Excel sẽ tìm giá trị lookup_value trong bảng table_array, nếu tìm thấy chính xác, thì trả về giá trị ở hàng thứ row_index_num num trong bảng table_array,  cùng cột với giá trị tìm được.

Nếu tham số 0 cuối cùng trong hàng là số 1, hàm VLOOKUP sẽ tìm kiếm theo kiểu gần đúng. Cơ chế tìm kiếm đã được CongNghePlus mô tả chi tiết ở trên, bạn tham khảo nhé!

Cách dùng hàm VLOOKUP giữa 2 sheet

Bạn cần thao tác trên nhiều sheet và muốn dùng hàm VLOOKUP giữa 2 file trong Excel? Trong trường hợp các bảng liên đới nằm ở các Sheet khác nhau của file Excel, bạn cần thêm tên để định danh chính xác cho bảng đó.

Ví dụ câu lệnh =VLOOKUP(O11,O$3:Q$8,3,0) mà bảng dò tìm O$3:Q$8 lại nằm ở Sheet có tên Bang diem thì câu lệnh trở thành: =VLOOKUP(O11,Bang diem!O$3:Q$8,3,0). Chúng ta nhận xét nó có thêm tên của Sheet và kèm theo dấu !

Bảng dò tìm có thể tự động được tạo ra khi bạn quét chuột trên phạm vi dò tìm. Điều này cũng tương tự, khi bạn chuyển sheet và quét chuột trên sheet khác.

>>> Xem thêmCách sử dụng hàm IF kết hợp VLOOKUP và LEFT trên Excel và Google Sheet

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

Dưới đây là một số lỗi thường gặp khi sử dụng hàm VLOOKUP như: lỗi #N/A, hàm VLOOKUP trả về giá trị 0,… CongNghePlus sẽ giải thích lỗi và đề xuất hướng khắc phục ngay sau đây.

Lỗi #N/A

Lỗi #N/A là lỗi tham chiếu về vùng dữ liệu dò tìm. Nó có thể do:

  • Định vị sai vùng tìm kiếm
  • Dữ liệu tìm kiếm không nằm trong cột đầu tiên của vùng tìm kiếm
  • Khi copy công thức, Excel tự động trượt vùng tham chiếu. Nếu không ghim vùng tìm kiếm, có thể bị lỗi.

Lỗi #REF!

Lỗi #REF là lỗi tham chiếu dữ liệu không hợp lệ. Nó có thể do:

  • Trong hàm chứa phép chia cho 0
  • Dữ liệu trong ô tham chiếu đến không phù hợp với phép toán

Lỗi #VALUE!

Lỗi #VALUE là lỗi kiểu dữ liệu không hợp lệ. Nó có thể do:

  • Nhập giá trị cho tham số vượt ra ngoài phạm vi của kiểu dữ liệu khai báo
  • Nhập giá trị khác kiểu cho tham số
  • Nhập giá trị không thực hiện được phép toán.

Lỗi #NAME?

Lỗi #NAME xuất hiện khi bạn gõ sai tên hàm. Bạn có thể sử dụng hàm gợi ý nhưng chú ý kích trượt con chuột sang hàm khác!

Một số lưu ý khi sử dụng hàm VLOOKUP

  • Hàm VLOOKUP có 4 tham số, nếu thiếu tham số thứ 4 thì giá trị mặc định của nó là 0
  • Hàm VLOOKUP có hai cơ chế tìm kiếm: chính xác hoặc gần đúng
  • Hàm VLOOKUP tìm kiếm theo nhiều tham số (điều kiện) thì về logic, chúng ta sẽ ghép các tham số thành một. Có thể ghép các tham số đó bằng cách tạo ra một cột mới bằng tay, hoặc tạo ra một bảng ảo bằng hàm CHOOSE
  • Hàm VLOOKUP có thể sử dụng hàm khác làm tham số, ngược lại nó cũng có thể là tham số cho hàm khác
  • Hàm VLOOKUP ngược lại với hàm HLOOKUP

Nếu bạn không thể chạy hàm VLOOKUP hay các hàm liên đới như hướng dẫn trên đây của CongNghePlus, vui lòng phản hồi lại cho chúng tôi để được giải đáp kỹ hơn. CongNghePlus luôn coi các ý kiến của độc giả là động lực để hoàn thiện hơn các kiến thức chia sẻ cộng đồng. Hãy chia sẻ các bài viết cũng như bấm các nút like để cổ vũ CongNghePlus bạn nhé!

5/5 - (1 bình chọn)