Hàm VLOOKUP

 

Nếu đã biết thông tin trên bảng tính, bạn có thể dùng hàm VLOOKUP để tìm thông tin liên quan theo hàng. Ví dụ: nếu muốn mua một quả cam, bạn có thể dùng hàm VLOOKUP để tìm giá.

VLOOKUP dành cho BigQuery

Tra cứu theo chiều dọc. Hàm này trả về các giá trị trong một cột dữ liệu tại vị trí tìm thấy một kết quả trùng khớp trong cột tìm kiếm.

Cách sử dụng tham khảo

VLOOKUP("Táo",tên_bảng!trái_cây,tên_bảng!giá)

Cú pháp

VLOOKUP(khoá_tìm_kiếm; dải_ô; chỉ_mục; được_sắp_xếp)

• khoá_tìm_kiếm: Giá trị cần tìm trong cột tìm kiếm.
• cột_tìm_kiếm: Cột dữ liệu cần xem xét khi tìm kiếm.
• cột_kết_quả: Cột dữ liệu cần xem xét để xác định kết quả.
• được_sắp_xếp: [KHÔNG BẮT BUỘC] Cách xác định kết quả trùng khớp cho khoá_tìm_kiếm.
  • FALSE: Nên dùng để tìm kết quả khớp chính xác.
  • TRUE: Đối với kết quả khớp gần đúng, đây là giá trị mặc định nếu bạn không chỉ định đối số được_sắp_xếp.
    Mẹo: Trước khi sử dụng kiểu khớp gần đúng, hãy sắp xếp khoá tìm kiếm theo thứ tự tăng dần. Nếu không, bạn có thể nhận được giá trị trả về không chính xác. Tìm hiểu lý do bạn có thể nhận được giá trị trả về không chính xác.

Mẹo: Để truy vấn cơ sở dữ liệu một cách linh hoạt hơn trong BigQuery, hãy sử dụng hàm XLOOKUP.

Cú pháp

=VLOOKUP(khoá_tìm_kiếm, dải_ô, chỉ_mục, [được_sắp_xếp])

Giá trị đầu vào

1. khoá_tìm_kiếm: Giá trị cần tìm kiếm trong cột đầu tiên của dải ô.
2. dải_ô: Các giá trị trên và dưới cần xem xét khi tìm kiếm.
3. chỉ_mục: Chỉ mục của cột với giá trị trả về của dải ô. Chỉ mục phải là số nguyên dương.
4. được_sắp_xếp: Giá trị đầu vào không bắt buộc. Chọn một tuỳ chọn:
   • FALSE = Kết quả khớp chính xác. Đây là tuỳ chọn nên dùng.
   • TRUE = Kết quả khớp gần đúng. Đây là tuỳ chọn mặc định nếu không có hàm được_sắp_xếp.
     Lưu ý quan trọng: Trước khi bạn dùng kiểu kết quả khớp gần đúng, hãy sắp xếp khoá tìm kiếm theo thứ tự tăng dần. Nếu không, bạn có thể nhận được giá trị trả về không chính xác. Tìm hiểu lý do bạn có thể nhận được giá trị trả về không chính xác.

Giá trị trả về

Giá trị khớp đầu tiên từ dải_ô đã chọn.

Thông tin chi tiết về kỹ thuật:

Ví dụ:

=VLOOKUP(G9, B4:D8, 3, FALSE)

=VLOOKUP("Táo", B4:D8, 3, TRUE)

Giá trị đầu vào	Nội dung mô tả
khoá_tìm_kiếm	Đây là giá trị bạn tìm kiếm trong cột đầu tiên của dải_ô. Nếu bạn muốn thấy một giá trị không phải lỗi, khoá tìm kiếm phải nằm trong cột đầu tiên của dải_ô. Tính năng tham chiếu ô cũng được hỗ trợ. Cách kiểm tra đơn giản: Nếu khoá_tìm_kiếm của bạn nằm ở B3 thì dải_ô của bạn phải bắt đầu ở cột B.
dải_ô	Đây là dải_ô, trong đó: Hàm này tìm kiếm khoá tìm kiếm được chỉ định trong cột đầu tiên. Hàm VLOOKUP trả về giá trị từ cột do chỉ_mục chỉ định. Bạn cũng có thể sử dụng một dải ô được đặt tên. Để trả về một giá trị không phải lỗi, khoá tìm kiếm của bạn phải nằm trong cột đầu tiên của dải_ô. Cách kiểm tra đơn giản: Nếu khoá_tìm_kiếm của bạn nằm ở B3 thì dải_ô của bạn phải bắt đầu ở cột B.
chỉ_mục	Còn được gọi là "Số cột". Đây là chỉ mục của cột trong dải_ô chứa giá trị trả về. Chỉ mục nhỏ nhất có thể là 1. Chỉ mục lớn nhất có thể là số cột tối đa trong dải_ô đó. Sau khi bạn thiết lập dải ô, VLOOKUP chỉ tìm trong cột khoá tìm kiếm, khi chỉ mục = 1 hoặc các cột ở phía bên phải. Mẹo: Khi bạn dùng hàm VLOOKUP, hãy tưởng tượng rằng các cột trong dải_ô được đánh số từ trái sang phải và bắt đầu bằng số 1.
được_sắp_xếp	Không bắt buộc phải nhập. Hai lựa chọn có sẵn là TRUE và FALSE. Nếu hàm được_sắp_xếp là TRUE thì VLOOKUP sẽ sử dụng kiểu khớp gần đúng. Lưu ý quan trọng: Trước khi bạn dùng kiểu kết quả khớp gần đúng, hãy sắp xếp khoá tìm kiếm theo thứ tự tăng dần. Nếu không, bạn có thể nhận được một giá trị trả về không mong muốn. Tìm hiểu lý do bạn có thể nhận được giá trị trả về không chính xác. Nếu đối số được_sắp_xếp là FALSE thì VLOOKUP sẽ dùng kiểu khớp chính xác. Nếu bạn không chỉ định hàm được_sắp_xếp thì giá trị theo mặc định sẽ là TRUE. Bạn rất nên sử dụng tuỳ chọn này. Sử dụng giá trị FALSE cho đối số được_sắp_xếp để nhất quán cho dù cột khoá tìm kiếm có được sắp xếp hay không. Hãy luôn chỉ định đối số được_sắp_xếp để dễ đọc hơn, mặc dù mục giá trị đầu vào này là không bắt buộc.

 

Kết quả đầu ra

Nội dung mô tả

Giá trị trả về

Đây là giá trị mà hàm VLOOKUP trả về dựa trên dữ liệu đầu vào của bạn. Mỗi hàm VLOOKUP chỉ trả về một giá trị. Nếu có nhiều giá trị khoá tìm kiếm khớp, hàm sẽ trả về giá trị trong cột giá trị trả về. Giá trị này có khoá tìm kiếm được so khớp đầu tiên trong cột khoá tìm kiếm. Nếu hàm trả về giá trị #N/A thì tức là không tìm thấy giá trị nào cả. Nếu bạn gặp phải một giá trị hoặc lỗi đã dự kiến như #N/A hoặc #VALUE!, hãy bắt đầu khắc phục sự cố. Nếu bạn muốn thay thế #N/A bằng một giá trị khác, hãy tìm hiểu thêm về cách sử dụng IFNA() trên VLOOKUP().

Ví dụ về hàm VLOOKUP cơ bản:

VLOOKUP cho các khoá tìm kiếm khác nhau

Sử dụng hàm VLOOKUP để tìm giá của một quả táo hoặc cam.

Dùng thử

Giải thích:

Khi sử dụng hàm VLOOKUP, bạn có thể sử dụng các khoá tìm kiếm khác nhau như "Táo" và "Cam".

Để trả về giá trị không phải lỗi, các khoá tìm kiếm này phải nằm trong cột đầu tiên của dải_ô. Nếu không muốn điền giá trị cho các khoá tìm kiếm, bạn cũng có thể sử dụng tham chiếu ô, ví dụ: "G9".

khoá_tìm_kiếm là "Cam"	=VLOOKUP("Cam", B4:D8, 3, FALSE) Giá trị trả về = 1,01 USD
khoá_tìm_kiếm là "Táo"	=VLOOKUP("Táo", B4:D8, 3, FALSE) Giá trị trả về = 1,5 USD
khoá_tìm_kiếm sử dụng tham chiếu ô của "Táo" trong G9	=VLOOKUP(G9, B4:D8, 3, FALSE) Giá trị trả về = 1,5 USD

VLOOKUP cho các chỉ mục cột khác nhau

Sử dụng hàm VLOOKUP để tìm số lượng Cam trong cột chỉ mục thứ hai.

Dùng thử

Giải thích:

Khi bạn sử dụng VLOOKUP, hãy tưởng tượng rằng các cột của dải_ô được đánh số từ trái sang phải và bắt đầu từ 1. Để tìm thông tin mục tiêu, bạn phải chỉ định chỉ mục cột. Ví dụ: cột 2 cho số lượng.

Chỉ_mục = 2 Tìm số lượng cam, cột thứ hai của dải_ô.

=VLOOKUP(G3, B4:D8, 2, FALSE) Giá trị trả về = 5

VLOOKUP khớp chính xác hoặc khớp gần đúng

• Sử dụng VLOOKUP kiểu kết quả khớp chính xác để tìm mã nhận dạng chính xác.
• Sử dụng VLOOKUP kiểu kết quả khớp gần đúng để tìm ID gần đúng.

Dùng thử

Giải thích:

Sử dụng kết quả khớp gần đúng hoặc được_sắp_xếp = TRUE khi bạn tìm kiếm kết quả phù hợp nhất nhưng không cần kết quả chính xác.

Nếu bạn muốn tìm kiếm Mã nhận dạng = 102 mà giá trị không tồn tại trong bảng, kết quả khớp gần đúng sẽ lùi lại một đơn vị để cung cấp cho bạn kết quả Mã nhận dạng = 101. Điều này là do trong cột khoá tìm kiếm, 101 là giá trị gần nhất và nhỏ hơn 102.

Kiểu khớp gần đúng sẽ tìm trong cột khoá tìm kiếm cho đến khi tìm thấy giá trị lớn hơn khoá tìm kiếm của bạn. Sau đó, hàm này dừng lại ở hàng trước giá trị lớn hơn đó và trả về giá trị trong cột giá trị trả về trên hàng đó. Điều đó có nghĩa là nếu cột khoá tìm kiếm không được sắp xếp theo thứ tự tăng dần, rất có thể bạn sẽ nhận được giá trị trả về không chính xác.

Lưu ý quan trọng: Trước khi sử dụng kiểu khớp gần đúng, hãy sắp xếp khoá tìm kiếm theo thứ tự tăng dần để trả về giá trị chính xác. Nếu không, bạn có thể nhận được một giá trị trả về không mong muốn.

Khi bạn tìm kiếm kết quả khớp chính xác, chẳng hạn như được_sắp_xếp = FALSE, hàm sẽ trả về kết quả khớp chính xác. Ví dụ: tên trái cây cho Mã nhận dạng = 103 là "Chuối". Nếu không có kết quả khớp chính xác, bạn sẽ gặp lỗi #N/A. Do hành vi của hàm dễ dự đoán hơn, bạn nên sử dụng kết quả khớp chính xác.

Khớp chính xác	=VLOOKUP(G6, A4:D8, 2, FALSE) Giá trị trả về = "Táo"
Khớp gần đúng	=VLOOKUP(G3, A4:D8, 2, TRUE) HOẶC =VLOOKUP(G3, A4:D8, 2) Giá trị trả về = "Chuối"

Các ứng dụng VLOOKUP phổ biến

Thay thế giá trị lỗi mà hàm VLOOKUP trả về

Có thể bạn sẽ muốn thay thế một giá trị lỗi được VLOOKUP trả về khi khoá tìm kiếm không tồn tại. Trong trường hợp này, nếu không muốn nhận kết quả #N/A, bạn có thể sử dụng các hàm IFNA() để thay thế #N/A. Tìm hiểu thêm về hàm IFNA().

Dùng thử

Ban đầu, hàm VLOOKUP sẽ trả về giá trị #N/A vì khoá tìm kiếm "Bút chì" không tồn tại trong cột "Trái cây". Hàm IFNA() thay thế lỗi #N/A bằng giá trị đầu vào thứ hai được chỉ định trong hàm. Trong trường hợp của chúng ta, giá trị này là "KHÔNG TÌM THẤY".

=IFNA(VLOOKUP(G3, B4:D8, 3, FALSE),"KHÔNG TÌM THẤY") Giá trị trả về = "KHÔNG TÌM THẤY"

Mẹo: Nếu bạn muốn thay thế các lỗi khác như #REF!, hãy tìm hiểu thêm về hàm IFERROR().

Dùng hàm VLOOKUP với nhiều tiêu chí

Không thể áp dụng trực tiếp hàm VLOOKUP theo nhiều tiêu chí. Thay vào đó, hãy tạo một cột trợ giúp mới để trực tiếp áp dụng hàm VLOOKUP theo nhiều tiêu chí để kết hợp nhiều cột hiện có.

Dùng thử

1. Bạn có thể tạo cột Trợ giúp nếu sử dụng "&" để kết hợp Tên và Họ.	=C4&D4 và kéo từ B4 xuống B8 để bạn có được cột Trợ giúp.
2. Dùng tham chiếu ô B7, JohnLee, làm khoá tìm kiếm.	=VLOOKUP(B7; B4:E8, 4, FALSE) Giá trị trả về = "Hỗ trợ"

Hàm VLOOKUP có kết quả khớp ký tự đại diện hoặc khớp một phần

Trong VLOOKUP, bạn cũng có thể dùng kết quả khớp ký tự đại diện hoặc khớp một phần. Bạn có thể sử dụng các ký tự đại diện sau:

• Dấu chấm hỏi "?" khớp với bất kỳ ký tự đơn nào.
• Dấu hoa thị "*" khớp với mọi chuỗi ký tự.

Để sử dụng ký tự đại diện trong hàm VLOOKUP, bạn phải sử dụng kiểu khớp chính xác: "được_sắp_xếp = FALSE".

Dùng thử

"St*" dùng để khớp với mọi giá trị bắt đầu bằng "St" bất kể số lượng ký tự, chẳng hạn như "Steve", "St1", "Stock" hoặc "Steeeeeeve".

=VLOOKUP("St*", B4:D8, 3, FALSE) Giá trị trả về = "Tiếp thị"

Khắc phục lỗi và các phương pháp hay nhất:

Giá trị trả về không chính xác

• Giá trị trả về là một giá trị không mong muốn: Nếu bạn đặt đối số được_sắp_xếp là TRUE nhưng cột đầu tiên trong dải ô không được sắp xếp theo số hoặc chữ cái theo thứ tự tăng dần thì bạn nên thay đổi đối số được_sắp_xếp thành FALSE.

• VLOOKUP cho kết quả khớp đầu tiên: VLOOKUP chỉ trả về kết quả khớp đầu tiên. Nếu bạn có nhiều khoá tìm kiếm đã khớp, hàm sẽ trả về một giá trị nhưng có thể không phải là giá trị bạn mong muốn.
• Dữ liệu không sạch: Đôi khi, các giá trị chứa khoảng trắng trước và sau nhìn có thể trông giống nhau nhưng VLOOKUP lại xử lý các giá trị này theo cách khác nhau. Ví dụ: Các nội dung sau đây là khác nhau đối với VLOOKUP:
  • " Táo"
  • "Táo "
  • "Táo"

Để nhận được kết quả mong muốn, hãy xoá khoảng trắng trước khi sử dụng hàm VLOOKUP.

Để tìm hiểu thêm, hãy xem mục các phương pháp hay nhất của chúng tôi.

#N/A

• Nếu sử dụng kiểu khớp gần đúng hoặc được_sắp_xếp = TRUE và nếu khoá tìm kiếm trong hàm VLOOKUP nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên thì hàm VLOOKUP trả về giá trị #N/A.
• Nếu sử dụng kiểu khớp chính xác hoặc được_sắp_xếp = FALSE thì sẽ không tìm thấy kết quả khớp chính xác của khoá tìm kiếm trong hàm VLOOKUP trong cột đầu tiên. Nếu không muốn nhận kết quả #N/A khi không tìm thấy khoá tìm kiếm trong cột đầu tiên, bạn có thể sử dụng hàm IFNA().

#REF!

Bạn có thể chỉ định nhầm một dải_ô có số cột nhiều hơn số cột tối đa của dải_ô. Để tránh tình trạng này, hãy đảm bảo rằng bạn:

• Đếm cột trong dải_ô đã chọn, không phải toàn bộ bảng.
• Bắt đầu đếm từ 1 thay vì 0.

#VALUE!

Nếu bạn nhận được kết quả #VALUE! có thể là do bạn đã:

• Nhập văn bản hoặc tên cột không chính xác cho chỉ_mục.
• Đã nhập một số nhỏ hơn 1 cho chỉ_mục. Chỉ_mục ít nhất phải bằng 1 và nhỏ hơn số cột tối đa của dải_ô. Hàm VLOOKUP chỉ có thể tìm kiếm trong cột khoá tìm kiếm khi chỉ_mục = 1 hoặc các cột ở phía bên phải.

Lưu ý quan trọng: chỉ_mục chỉ chấp nhận số.

#NAME?

• Bạn có thể đã bỏ lỡ một dấu ngoặc kép trong khoá tìm kiếm khi khoá_tìm_kiếm là dữ liệu văn bản.

Các phương pháp hay nhất

 

Việc cần làm	Lý do
Sử dụng tham chiếu tuyệt đối cho dải_ô	Bạn nên sử dụng: Tham chiếu tuyệt đối cho dải_ô VLOOKUP VLOOKUP(G3, $B$3:$D$7, 3, FALSE) Bạn không nên sử dụng: VLOOKUP(G3, B3:D7, 3, FALSE) Điều này giúp ngăn chặn những thay đổi bất ngờ trong dải_ô khi bạn sao chép hoặc kéo xuống.
Sắp xếp cột đầu tiên theo thứ tự tăng dần khi bạn sử dụng kiểu khớp gần đúng, chẳng hạn như được_sắp_xếp = TRUE.	Nếu sử dụng kiểu khớp gần đúng hay được_sắp_xếp = TRUE, bạn phải sắp xếp cột đầu tiên theo thứ tự tăng dần. Nếu không, rất có thể bạn sẽ nhận được giá trị trả về không chính xác. Tìm hiểu thêm về cách sắp xếp.
Làm sạch dữ liệu của bạn trước khi sử dụng hàm VLOOKUP	Trước khi bạn dùng hàm VLOOKUP, hãy nhớ làm sạch dữ liệu của bạn. Dữ liệu không sạch có thể khiến hàm VLOOKUP trả về một giá trị không dự đoán được. Dưới đây là một số sai lầm phổ biến về dữ liệu không sạch: Khoảng trắng ở phía trước: " táo" Khoảng trắng ở sau: "táo " Khoảng trống hoặc khoảng trắng: "" và " " không tương đương Để xoá khoảng trắng trước và sau, bạn có thể sử dụng Dữ liệu Làm sạch dữ liệu Cắt bỏ khoảng trắng.
Không lưu trữ các giá trị số hoặc ngày dưới dạng văn bản	Đảm bảo các giá trị ngày hoặc số trong cột đầu tiên của dải ô VLOOKUP (ví dụ: cột khoá tìm kiếm) không được lưu trữ dưới dạng văn bản. Bạn có thể nhận được giá trị trả về không mong muốn. Ở đầu Trang tính, hãy chọn cột khoá tìm kiếm. Nhấn vào Trình đơn định dạng Số. Chọn một tuỳ chọn theo loại dữ liệu mà bạn muốn: Ngày Số