Nếu bạn đã biết thông tin trên bảng tính của mình, bạn có thể sử dụng VLOOKUP
để tìm kiếm thông tin liên quan theo hàng. Ví dụ: nếu bạn muốn mua một quả cam, bạn có thể sử dụng VLOOKUP
để tìm kiếm giá
cú pháp
=VLOOKUP[search_key,
range, index,
[is_sorted
]]
đầu vào
search_key
. Giá trị để tìm kiếm trong cột đầu tiên của phạm virange
. Các giá trị trên và dưới để xem xét cho tìm kiếmindex
. Chỉ mục của cột có giá trị trả về của phạm vi. Chỉ số phải là một số nguyên dươngis_sorted
. đầu vào tùy chọn. Chọn một sự lựa chọnFALSE
= Khớp chính xác. Điều này được khuyến nghịVLOOKUP
0 = Gần đúng. Đây là giá trị mặc định nếuis_sorted
không được chỉ định.
Quan trọng. Trước khi bạn sử dụng đối sánh gần đúng, hãy sắp xếp khóa tìm kiếm của bạn theo thứ tự tăng dần. Nếu không, bạn có thể nhận được giá trị trả về sai. Tìm hiểu lý do tại sao bạn có thể gặp phải giá trị trả lại sai.
Giá trị trả về
Giá trị khớp đầu tiên từ range
đã chọn
Thí dụ
=VLOOKUP[G9, B4. D8, 3, SAI]
=VLOOKUP["Táo", B4. D8, 3, ĐÚNG]
Đầu vàoMô tảsearch_key
Đây là giá trị bạn tìm kiếm trong cột đầu tiên của range
. Nếu bạn mong đợi một giá trị không có lỗi, khóa tìm kiếm phải nằm trong cột đầu tiên của range
. Tham chiếu ô cũng được hỗ trợ
Để làm một kiểm tra đơn giản. Nếu search_key
của bạn nằm ở B3, thì range
của bạn phải bắt đầu bằng cột B
range
Đây là range
nơi
- Hàm tìm kiếm khóa tìm kiếm được chỉ định trong cột đầu tiên của nó
VLOOKUP
trả về giá trị từ cột được chỉ định bởiindex
. Bạn cũng có thể sử dụng một phạm vi được đặt tên
Để trả về giá trị không có lỗi, khóa tìm kiếm của bạn phải nằm trong cột đầu tiên của range
Để làm một kiểm tra đơn giản. Nếu search_key
của bạn nằm ở B3, thì range
của bạn phải bắt đầu bằng cột B
index
Còn được gọi là “Số cột. ” Đây là chỉ mục của cột trong range
chứa giá trị trả về
- Chỉ số nhỏ nhất có thể là 1
- Chỉ mục lớn nhất có thể là số cột tối đa trong
range
đó
Sau khi bạn thiết lập phạm vi, VLOOKUP
chỉ nhìn vào cột khóa tìm kiếm, khi chỉ mục = 1 hoặc các cột xa hơn bên phải
Mẹo. Khi bạn sử dụng VLOOKUP
, hãy tưởng tượng rằng các cột của range
được đánh số từ trái sang phải và bắt đầu bằng 1
is_sorted
Đây là đầu vào tùy chọn. Hai lựa chọn có sẵn là VLOOKUP
0 và FALSE
- Nếu
is_sorted
làVLOOKUP
0, thìVLOOKUP
sử dụng đối sánh gần đúng.
Quan trọng. Trước khi bạn sử dụng đối sánh gần đúng, hãy sắp xếp khóa tìm kiếm của bạn theo thứ tự tăng dần. Nếu không, bạn có thể nhận được một giá trị không mong muốn được trả về. Tìm hiểu lý do tại sao bạn có thể gặp phải giá trị trả lại sai. Nếu
is_sorted
làFALSE
, thìVLOOKUP
sử dụng đối sánh chính xác- Nếu
is_sorted
không được chỉ định, nó sẽ làVLOOKUP
0 theo mặc định
Chúng tôi thực sự khuyên bạn nên
- Sử dụng
FALSE
chois_sorted
do hành vi nhất quán của nó cho dù cột khóa tìm kiếm có được sắp xếp hay không - Luôn chỉ định
is_sorted
để dễ đọc hơn, mặc dù đầu vào là tùy chọn
Đầu ra Mô tả Giá trị trả về
Đây là giá trị mà VLOOKUP
trả về dựa trên đầu vào của bạn. Chỉ có một giá trị trả về từ mỗi hàm VLOOKUP
- Nếu có nhiều giá trị khóa tìm kiếm khớp nhau, thì giá trị trong cột giá trị trả về có khóa tìm kiếm được liên kết được khớp đầu tiên trong cột khóa tìm kiếm sẽ được trả về
- Nếu #N/A được trả về, giá trị không được tìm thấy
Nếu bạn gặp phải một giá trị mong đợi hoặc lỗi như #N/A hoặc #VALUE. , 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ụ VLOOKUP cơ bản
VLOOKUP trên các khóa tìm kiếm khác nhau
Sử dụng VLOOKUP
để tìm giá của một quả cam và quả táo
dùng thử
Giải trìnhKhi sử dụng VLOOKUP
, bạn có thể sử dụng các phím tìm kiếm khác nhau, chẳng hạn như "Apple" và "Orange. "
Để trả về giá trị không có lỗi, các khóa tìm kiếm này phải nằm trong cột đầu tiên của range
. Nếu bạn không muốn điền giá trị cho các khóa tìm kiếm, bạn cũng có thể sử dụng tham chiếu ô, ví dụ: "G9. "
search_key
là "Cam"=VLOOKUP["Cam", B4. D8, 3, SAI]
Giá trị trả về = $1. 01
search_key
là "Táo"=VLOOKUP["Táo", B4. D8, 3, SAI]
Giá trị trả về = $1. 50
search_key
sử dụng tham chiếu ô của "Apple" trong G9=VLOOKUP[G9, B4. D8, 3, SAI]
Giá trị trả về = $1. 50
VLOOKUP trên các chỉ mục cột khác nhau
Sử dụng VLOOKUP
để tìm số lượng Cam trong cột chỉ số thứ hai
dùng thử
Giải trìnhKhi bạn sử dụng VLOOKUP
, hãy tưởng tượng rằng các cột của range
đượ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 của nó. Ví dụ, cột 2 cho số lượng
search_key
6 = 2
Tìm số lượng cam, là cột thứ hai của range
=VLOOKUP[G3, B4. D8, 2, SAI]
Giá trị trả về = 5
VLOOKUP đối sánh chính xác hoặc đối sánh gần đúng
- Sử dụng đối sánh chính xác
VLOOKUP
để tìm ID chính xác - Sử dụng đối sánh gần đúng của
VLOOKUP
để tìm ID gần đúng
dùng thử
Giải trìnhSử dụng đối sánh gần đúng hoặc is_sorted
= VLOOKUP
0 khi bạn tìm kiếm đối sánh tốt nhất, nhưng không phải là đối sánh chính xác
Nếu bạn muốn tìm kiếm ID = 102, không tồn tại trong bảng, kết quả khớp gần đúng sẽ quay lại một bước để cung cấp cho bạn ID = 101 làm kết quả. Điều này là do trong cột khóa tìm kiếm, 101 là giá trị gần nhất cũng nhỏ hơn 102
Đối sánh gần đúng tìm kiếm trong cột khóa tìm kiếm cho đến khi tìm thấy giá trị lớn hơn khóa tìm kiếm của bạn. Sau đó, nó dừng ở hàng trước giá trị lớn hơn và trả về giá trị từ cột giá trị trả về trên hàng đó. Điều đó có nghĩa là nếu cột khóa tìm kiếm của bạn 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ề sai
Quan trọng. Trước khi bạn sử dụng đối sánh gần đúng, hãy sắp xếp khóa tìm kiếm của bạn 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ị không mong muốn được trả về
Khi bạn tìm kiếm kết quả khớp chính xác, chẳng hạn như is_sorted
= FALSE
, nó sẽ trả về kết quả khớp chính xác. Ví dụ: tên trái cây cho ID = 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 dễ dự đoán hơn, chúng tôi khuyên bạn nên sử dụng đối sánh chính xác
=VLOOKUP[G6, A4. D8, 2, SAI]
Giá trị trả về = "Quả táo"
trận đấu gần đúng=VLOOKUP[G3, A4. D8, 2, ĐÚNG]
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 từ VLOOKUP
Bạn có thể muốn thay thế một giá trị lỗi do VLOOKUP
trả về khi khóa tìm kiếm của bạn không tồn tại. Trong trường hợp này, nếu bạn không muốn #N/A, bạn có thể sử dụng hàm range
5 để thay thế #N/A. Tìm hiểu thêm về IFNA[]
dùng thử
Ban đầu, VLOOKUP
trả về #N/A vì từ khóa tìm kiếm “Pencil” không tồn tại trong cột “Fruit”
range
5 thay thế lỗi #N/A bằng đầu vào thứ hai được chỉ định trong hàm. Trong trường hợp của chúng tôi, đó là “KHÔNG TÌM THẤY. ”
=IFNA[VLOOKUP[G3, B4. D8, 3, SAI],"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. , tìm hiểu thêm về IFERROR[]
VLOOKUP với nhiều tiêu chí
Không thể áp dụng trực tiếp VLOOKUP
cho nhiều tiêu chí. Thay vào đó, hãy tạo cột trợ giúp mới để áp dụng trực tiếp VLOOKUP
trên 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 Người trợ giúp nếu bạn sử dụng "&" để kết hợp Tên và Họ. =C4&D4 và kéo nó xuống từ B4 đến B8 sẽ cho bạn cột Trình trợ giúp. 2. Sử dụng tham chiếu ô B7, JohnLee, làm khóa tìm kiếm=VLOOKUP[B7, B4. E8, 4, SAI]
Giá trị trả về = "Hỗ trợ"
VLOOKUP với ký tự đại diện hoặc đối sánh một phần
Trong VLOOKUP
, bạn cũng có thể sử dụng ký tự đại diện hoặc đối sánh một phần. Bạn có thể sử dụng các ký tự đại diện này
- Một dấu chấm hỏi "?"
- Dấu hoa thị "*" khớp với bất kỳ chuỗi ký tự nào
Để sử dụng ký tự đại diện trong VLOOKUP
, bạn phải sử dụng đối sánh chính xác. "______4 = FALSE
"
dùng thử
"St*" được sử dụng để khớp với bất kỳ thứ gì 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, SAI]
Giá trị trả về = "Tiếp thị"
Khắc phục sự cố lỗi & thực tiễn tốt nhất
Giá trị trả về saiTrả về một giá trị không mong muốn. Nếu bạn đặt
is_sorted
làVLOOKUP
0, nhưng cột đầu tiên của bạn trong phạm vi không được sắp xếp theo thứ tự số hoặc bảng chữ cái theo thứ tự tăng dần, thì hãy thay đổi is_sorted thànhFALSE
- VLOOKUP đưa ra kết quả khớp đầu tiên.
VLOOKUP
chỉ trả về trận đấu đầu tiên. Nếu bạn có nhiều khóa tìm kiếm phù hợp, một giá trị sẽ được trả về, nhưng nó có thể không phải là giá trị mong đợi - dữ liệu không sạch. Đôi khi, các giá trị có dấu cách ở cuối và dẫn đầu có vẻ giống nhau nhưng
VLOOKUP
xử lý chúng theo cách khác. Ví dụ: những điều sau đây khác vớiVLOOKUP
- " Quả táo"
- "Quả táo "
- "Quả táo"
Để có kết quả như mong đợi, hãy xóa dấu cách trước khi bạn sử dụng VLOOKUP
Để tìm hiểu thêm, hãy xem phần thực hành tốt nhất của chúng tôi
#N/A- Nếu giá trị gần đúng hoặc
is_sorted
=VLOOKUP
0 được sử dụng và nếu khóa tìm kiếm trongVLOOKUP
nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên, thìVLOOKUP
trả về #N/A - Nếu đối sánh chính xác hoặc
is_sorted
=FALSE
được sử dụng, thì đối sánh chính xác của khóa tìm kiếm trongVLOOKUP
không được tìm thấy trong cột đầu tiên. Nếu bạn không muốn #N/A khi không tìm thấy khóa tìm kiếm ở cột đầu tiên, bạn có thể sử dụng hàm IFNA[]
Bạn có thể chỉ định nhầm một range
có số lớn hơn số cột tối đa của range
. Để tránh điều này, hãy chắc chắn rằng bạn
- Đếm các cột từ
range
đã chọn, không phải toàn bộ bảng - Bắt đầu đếm từ 1 thay vì 0
Nếu bạn nhận được #VALUE. lỗi, bạn có thể có
- Nhập sai văn bản hoặc tên cột cho
index
- Đã nhập một số nhỏ hơn 1 cho
index
.index
tối thiểu phải bằng 1 và nhỏ hơn số cột tối đa củarange
.VLOOKUP
chỉ có thể tìm kiếm trong cột khóa tìm kiếm, khiindex
= 1 hoặc các cột xa hơn bên phải
Quan trọng. index
chỉ chấp nhận một số
- Bạn có thể đã bỏ lỡ một câu trích dẫn trong khóa tìm kiếm khi
search_key
của bạn là dữ liệu văn bản
Để làmReasonSử dụng tham chiếu tuyệt đối cho
range
Bạn nên sử dụng
- Tài liệu tham khảo tuyệt đối cho
VLOOKUP
range
- VLOOKUP[G3, $B$3. $D$7, 3, SAI]
Bạn không nên sử dụng
- VLOOKUP[G3, B3. D7, 3, SAI]
Điều này ngăn những thay đổi không thể đoán trước trong range
khi nó được sao chép hoặc kéo xuống
is_sorted
= VLOOKUP
0. Nếu bạn sử dụng đối sánh gần đúng hoặc is_sorted
= VLOOKUP
0, 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ề sai. 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 bạn sử dụng VLOOKUP
Trước khi bạn sử dụng VLOOKUP
, hãy nhớ xóa dữ liệu của bạn. Dữ liệu không rõ ràng có thể khiến VLOOKUP
trả về một giá trị không thể đoán trước. Dưới đây là một số cạm bẫy phổ biến của dữ liệu không sạch
- Không gian dẫn. " táo"
- Không gian mà dấu vết. "táo "
- Khoảng trống hoặc khoảng trắng. "" và " " không tương đương
Để cắt bớt khoảng trắng ở đầu và cuối, bạn có thể sử dụng Dữ liệu
Đảm bảo giá trị ngày hoặc số trong cột đầu tiên của phạm vi VLOOKUP
, chẳng hạn như cột khóa tìm kiếm, không được lưu trữ dưới dạng giá trị văn bản. Bạn có thể nhận được một giá trị trả lại không mong muốn