Google sheet vlookup khớp một phần

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

1. search_key. Giá trị để tìm kiếm trong cột đầu tiên của phạm vi
2. range. Các giá trị trên và dưới để xem xét cho tìm kiếm
3. index. 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ương
4. is_sorted. đầu vào tùy chọn. Chọn một sự lựa chọn
   • FALSE = Khớp chính xác. Điều này được khuyến nghị
   • VLOOKUP0 = Gần đúng. Đây là giá trị mặc định nếu is_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

chi tiết kỹ thuật

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ởi index. 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à VLOOKUP0 và FALSE

• Nếu is_sorted là VLOOKUP0, 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ếuis_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à VLOOKUP0 theo mặc định

Chúng tôi thực sự khuyên bạn nên

• Sử dụng FALSE cho is_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ình

Khi 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ình

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 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_key6 = 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ình

Sử dụng đối sánh gần đúng hoặc is_sorted = VLOOKUP0 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

Kết hợp chuẩn 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 range5 để 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”

range5 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ề sai

• Trả về một giá trị không mong muốn. Nếu bạn đặt is_sorted là VLOOKUP0, 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ành FALSE

• 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ới VLOOKUP
  • " 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 = VLOOKUP0 được sử dụng và nếu khóa tìm kiếm trong VLOOKUP 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 trong VLOOKUP 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()

#REF

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

#GIÁ TRỊ

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ủa range. VLOOKUP chỉ có thể tìm kiếm trong cột khóa tìm kiếm, khi index = 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ố

#TÊN?

• 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

Thực hành tốt nhất

 

Để 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

Sắp xếp cột đầu tiên theo thứ tự tăng dần khi bạn sử dụng đối sánh gần đúng, chẳng hạn như is_sorted = VLOOKUP0. Nếu bạn sử dụng đối sánh gần đúng hoặc is_sorted = VLOOKUP0, 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

Dọn dẹp dữ liệu Cắt bớt khoảng trắng.

Không lưu trữ giá trị số hoặc ngày dưới dạng văn bản

Đả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

Bạn có thể sử dụng VLOOKUP để đối sánh một phần Google Trang tính không?

Bạn có thể VLOOKUP khớp một phần không?

Tại sao VLOOKUP của tôi hoạt động một phần?