Cách Dùng Hàm Vlookup Để Lọc Dữ Liệu, Hàm Vlookup Trong Excel
Trên file Excel, các bạn có dữ liệu ở cả 2 Sheet không giống nhau mà lại ý muốn lọc tài liệu trùng nhau cùng với số lượng bạn dạng ghi lớn. Vậy các bạn hãy thuộc theo dõi nội dung bài viết dưới phía trên để biết cách lọc dữ liệu trùng nhau từ bỏ 2 Sheet vào Excel.
Dưới đây là là cách áp dụng hàm VLOOKUP để lọc tài liệu trùng nhau từ bỏ 2 Sheet trong Excel, mời các bạn cùng theo dõi.
Sử dụng hàm VLOOKUP để lọc tài liệu trùng nhau trong 2 sheet
mang sử chúng ta có tài liệu Sheet 1 như sau:
Dữ liệu Sheet 2 như sau:
Để kiểm tra dữ liệu trùng nhau chúng ta nhập công thức hàm Vlookup vào ô trước tiên như sau:
=VLOOKUP(Sheet1!B5;Sheet2!$B$4:$G$33;1;0)
trong đó: Sheet1!B5 là ô B5 vào Sheet1 buộc phải kiểm tra tài liệu trùng.
Sheet2!$B$4:$G$33 là vùng tài liệu cần kiểm tra dữ liệu trùng nhau trong Sheet 2.
1 là cột nhưng các bạn muốn hàm Vlookup trả về vào vùng dữ liệu cần kiểm tra.
0 là hình trạng tìm kiếm chính xác của hàm Vlookup.
như vậy nếu tài liệu trùng thì hàm sẽ trả về chính dữ liệu trùng đó, nếu dữ liệu không trùng thì hàm vẫn trả về lỗi #N/A.
Cách viết hàm Vlookup
Hàm Vlookup với tài liệu từ hai sheet buộc phải sẽ mang tên sheet đứng trước mỗi giá bán trị, các bạn cũng có thể nhập hàm như bình thường. Nếu cảnh giác hơn các bạn có thể thực hiện viết hàm Vlookup như sau:
1. Lựa chọn chuột trong ô C5 của Sheet 1 (ô trước tiên cần kiểm tra dữ liệu trùng), tiếp theo các bạn chọn thẻ Formulas -> Insert Function.
JGn Y_011352900.png" alt="*">
2. Xuất hiện thêm hộp thoại Insert Function các bạn chọn vào phần Or select a category (1) là Lookup và Reference bởi vì hàm Vlookup thuộc đội hàm này, chúng ta chọn vì vậy thì vào phần Select a function sẽ rút gọn gàng tìm kiếm mang đến hàm Vlookup hơn. Tiếp theo sau chọn Vlookup vào phần Select a function (2) và lựa chọn OK.
3. Lộ diện hộp thoại Function Arguments các các bạn sẽ thấy những đối số trong hàm Vlookup, trên đây các bạn chọn dữ liệu lần lượt cho những đối số như sau:
trong phần Lookup_value, chúng ta đặt nhỏ trỏ chuột vào đây tiếp nối chọn ô B5 vào Sheet 1, hôm nay trong ô Lookup_value sẽ xuất hiện thêm Sheet1!B5. tiếp sau trong phần Table_array các bạn đặt con trỏ loài chuột vào phần này và chọn sheet 2 và lựa chọn vùng dữ liệu cần kiểm tra tài liệu trùng lặp. Vì thế trong Table_array sẽ mở ra Sheet2!B4:G33 cùng với B4:G33 là vùng chúng ta chọn.
V_012223848.jpg" alt="*">
nếu như các bạn có nhu cầu kiểm tra nhiều dữ liệu thì chúng ta cần cố định và thắt chặt vùng chọn này bằng phương pháp đặt nhỏ trỏ loài chuột vào giữa hoặc cuối B4 dấn phím F4, tiếp sau đặt chuột vào thời điểm cuối G33 cùng nhấn phím F4 để cố định vị trí. Như vậy từ bây giờ Table_array sẽ là Sheet2!$B$4:$G$33 như hình dưới. tiếp sau trong phần Col_index_num chúng ta nhập địa chỉ cột cơ mà các bạn có nhu cầu trả về tài liệu nếu hai dữ liệu trùng nhau, lấy một ví dụ nếu tài liệu trùng nhau thì các bạn trả về tài liệu trùng nhau đó thì các bạn nhập số 1 để trả về dữ liệu tương xứng trong cột đầu tiên. Trong phần Range_lookup chúng ta nhập 0 để hàm tìm kiếm kiếm thiết yếu xác. Tiếp đến nhấn OK nhằm thêm hàm vào ô các bạn chọn.
Như vậy các bạn sẽ được hiệu quả như dưới:
Để kiểm tra các dữ liệu dưới chúng ta chỉ cần sao chép công thức xuống là được, vì các bạn đã thắt chặt và cố định vùng đề nghị hàm sẽ không bị chuyển vùng khi bạn xào luộc công thức hàm. Tác dụng các các bạn sẽ được trả về những tài liệu trùng, những dữ liệu không trùng đã trả về lỗi #N/A.
Kết vừa lòng thêm hàm IF và ISNA để vứt bỏ lỗi #N/A
Hàm ISNA giúp chúng ta kiểm tra tài liệu #N/A, nếu tài liệu là #N/A thì hàm ISNA đang trả về TRUE, nếu chưa phải thì hàm vẫn trả về FALSE.
Hàm IF giúp các bạn kiểm tra nếu điều kiện đúng thì hàm sẽ trả về giá trị a mà chúng ta chỉ định, nếu đk sai thì hàm vẫn trả về quý giá b mà các bạn chỉ định.
bởi vậy với ví dụ ở trên thay vày chỉ sử dụng hàm Vlookup các bạn có thể kết phù hợp hàm IF và hàm ISNA để bình chọn dữ liệu, chúng ta nhập công thức hàm như sau:
Hàm ISNA chất vấn giá trị trả về của hàm VLOOKUP, giả dụ hàm VLOOKUP trả về giá trị #N/A thì hàm ISNA đang trả về quý giá TRUE. Nhưng mà hàm VLOOKUP trả về quý hiếm #N/A nếu dữ liệu không trùng lặp. Vị vậy hàm IF sẽ trả về “Không”.
nếu như hàm VLOOKUP trả về giá chỉ trị ví dụ thì hàm ISNA vẫn trả về giá trị FALSE với hàm IF đã trả về điều kiện sai là “Trùng dữ liệu”.
Tương tự các bạn sao chép phương pháp xuống những dòng bên dưới và được hiệu quả như sau:
Trên đây bài viết đã chia sẻ đến các bạn cách lọc tài liệu trùng nhau từ 2 Sheet trong Excel. Vì thế các bạn có thể lọc tài liệu trùng nhau giữa các sheet không giống nhau một biện pháp nhanh chóng. Hi vọng bài viết này sẽ giúp ích cho những bạn. Chúc các bạn thành công!
Excel mang đến Microsoft 365 Excel cho Microsoft 365 dành đến m&#x E1;y Mac Excel cho web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 xem th&#x EA;m...&#x CD;t hơn
Mẹo: Hãy thử sử dụng hàm XLOOKUP mới, phiên phiên bản cải tiến của hàm VLOOKUP hoạt động theo ngẫu nhiên hướng nào với trả về kết quả khớp đúng đắn theo mang định, giúp việc sử dụng tiện lợi và thuận lợi hơn đối với phiên phiên bản trước.Bạn sẽ xem: phương pháp dùng hàm vlookup nhằm lọc dữ liệu
Sử dụng hàm VLOOKUP khi bạn cần tìm văn bản trong một bảng hoặc dải ô theo hàng. Ví dụ: tra cứu vớt giá cho một linh phụ kiện ô tô theo số linh phụ kiện hoặc tìm tên nhân viên cấp dưới dựa trên ID nhân viên cấp dưới của họ.
Ở dạng đơn giản dễ dàng nhất, hàm VLOOKUP mang đến biết:
=VLOOKUP(Nội dung bạn muốn tra cứu, nơi bạn muốn tìm nó, số cột trong phạm vi chứa giá trị nên trả về, trả về tác dụng khớp sấp xỉ hoặc đúng chuẩn – được biểu thị là 1/TRUE hoặc 0/FALSE).
Mẹo: Bí quyết để áp dụng hàm VLOOKUP là phải sắp xếp dữ liệu của doanh nghiệp sao mang đến giá trị mà bạn có nhu cầu tra cứu vớt (Trái cây) nằm tại vị trí bên trái quý giá trả về (số tiền) mà bạn muốn tìm.
Sử dụng hàm VLOOKUP để tra cứu quý hiếm trong bảng.
=VLOOKUP(A2,"Chi tiết sản phẩm công nghệ khách"! A:F,3,FALSE)
Tên đối số
Mô tả
lookup_value (bắt buộc)
Giá trị bạn có nhu cầu tra cứu. Giá trị bạn có nhu cầu tra cứu buộc phải nằm trong cột thứ nhất của phạm vi ô mà bạn chỉ định vào đối table_array số.
Ví dụ, trường hợp table-array trải dài những ô B2:D7, thì lookup_value bạn phải ở trong cột B.
Lookup_value có thể là một quý giá hoặc tham chiếu cho một ô.
table_array (bắt buộc)
Phạm vi các ô nhưng VLOOKUP sẽ tìm kiếm cho lookup_value và quý giá trả về. Bạn có thể sử dụng phạm vi hoặc bảng sẽ đặt tên và bạn cũng có thể sử dụng tên trong đối số thay vì chưng tham chiếu ô.
Cột thứ nhất trong phạm vi ô phải chứa lookup_value. Phạm vi ô cũng cần bao gồm giá trị trả về mà bạn muốn tìm.
Tìm hiểu biện pháp chọn phạm vi vào một trang tính.
col_index_num (bắt buộc)
Số cột (bắt đầu bởi 1 mang lại cột không tính cùng phía trái của table_array) đựng giá trị trả về.
range_lookup (tùy chọn)
Một quý hiếm lô-gic sẽ xác minh xem bạn có nhu cầu hàm VLOOKUP tìm hiệu quả khớp tương đối hay tác dụng khớp bao gồm xác:
Kết trái khớp tương đối - 1/TRUE trả định rằng cột đầu tiên trong bảng được sắp xếp theo số hoặc theo bảng vần âm và tiếp đến sẽ tìm kiếm giá trị gần nhất. Đây đang là cách thức mặc định nếu như khách hàng không xác định phương pháp nào khác. Ví dụ: =VLOOKUP(90,A1:B100,2,TRUE).
Kết trái khớp đúng đắn - 0/FALSE tìm kiếm kiếm giá trị đúng chuẩn trong cột đầu tiên. Ví dụ: =VLOOKUP("Smith",A1:B100;2,FALSE).
Cách bắt đầu
Có tư phần thông tin mà bạn sẽ cần áp dụng để thi công cú pháp đến hàm VLOOKUP:
Giá trị bạn muốn tra cứu, nói một cách khác là giá trị tra cứu.
Dải ô cất giá trị tra cứu. Hãy lưu giữ rằng cực hiếm tra cứu vãn phải luôn nằm sống cột đầu tiên của dải ô nhằm hàm VLOOKUP bao gồm thể chuyển động chính xác. Ví dụ: Nếu quý hiếm tra cứu của chúng ta nằm ở ô C2 thì dải ô của người sử dụng sẽ bước đầu ở C.
Số cột đựng giá trị trả về trong dải ô. Ví dụ, nếu bạn chỉ rõ B2:D11 là phạm vi, bạn nên tính B là cột đầu tiên, C là cột thiết bị hai, v.v.
Hay bạn cũng có thể chỉ định TRUE nếu bạn muốn có một kết quả khớp kha khá hoặc FALSE nếu bạn muốn có một hiệu quả khớp đúng chuẩn ở quý giá trả về. Nếu như bạn không chỉ có định bất cứ giá trị làm sao thì giá trị mặc định sẽ luôn là TRUE hay hiệu quả khớp tương đối.
Giờ thì nên tập hợp toàn bộ mục trên lại cùng với nhau, như sau:
=VLOOKUP(giá trị tra cứu, dải ô đựng giá trị tra cứu, số cột vào phạm vi cất giá trị trả về, tác dụng khớp tương đối (TRUE) hoặc Khớp chính xác (FALSE)).
Ví dụ
Dưới đây là một số ví dụ về hàm VLOOKUP:
Ví dụ 1
Bạn có thể sử dụng hàm VLOOKUP để phối kết hợp nhiều bảng thành một, miễn là một trong những trong các bảng có những trường thông thường với toàn bộ các bảng khác. Điều này rất có thể đặc biệt hữu ích nếu như khách hàng cần chia sẻ sổ thao tác với những người có phiên bản Excel cũ hơn không cung ứng các tài năng dữ liệu có khá nhiều bảng làm cho nguồn dữ liệu - bằng cách kết hợp những nguồn vào một bảng và chuyển đổi nguồn dữ liệu của tính năng dữ liệu sang bảng mới, bạn có thể sử dụng tuấn kiệt dữ liệu trong các phiên phiên bản Excel cũ hơn (miễn là phiên bản thân tính năng dữ liệu được phiên bản cũ hơn hỗ trợ).
Ở đây, những cột A-F cùng H có những giá trị hoặc bí quyết chỉ sử dụng những giá trị trên trang tính và những cột còn sót lại sử dụng VLOOKUP và những giá trị của cột A (Mã vật dụng khách) với cột B (Luật sư) để lấy dữ liệu từ các bảng khác.
Sao chép bảng có các trường chung vào một trang tính bắt đầu và để tên mang lại bảng đó.
Hãy bấm >Công cụ Dữ > Quan hệ để mở hộp làm chủ Quan hệ dữ liệu.
Đối với mỗi quan hệ được liệt kê, hãy xem xét những điều sau đây:
Trường liên kết các bảng (được liệt kê trong lốt ngoặc 1-1 trong hộp thoại). Đây là công thức lookup_value cho bí quyết VLOOKUP của bạn.
Tên Bảng Tra cứu vớt Liên quan. Đây là công thức table_array trong bí quyết VLOOKUP của bạn.
Trường (cột) trong Bảng Tra cứu tương quan có dữ liệu bạn muốn trong cột mới. Thông tin này không được hiển thị trong vỏ hộp thoại làm chủ Quan hệ - bạn sẽ phải coi Bảng Tra cứu liên quan để xem bạn có nhu cầu truy xuất trường nào. Bạn có nhu cầu ghi chú số cột (A=1) - đó là số col_index_num công thức của bạn.
Để thêm 1 trường vào bảng mới, hãy nhập bí quyết VLOOKUP vào cột trống đầu tiên bằng phương pháp dùng thông tin bạn đã tích lũy ở cách 3.
Trong lấy ví dụ như của bọn chúng tôi, cột G áp dụng Attorney (the lookup_value) để mang dữ liệu Bill Rate trường đoản cú cột thứ tư (col_index_num = 4) từ bảng trang tính Attorneys, tblAttorneys ( table_array), với bí quyết =VLOOKUP(Attorney>,tbl_Attorneys,4,FALSE).
Công thức cũng có thể sử dụng tham chiếu ô với tham chiếu dải ô. Trong ví dụ như của chúng tôi, hàm sẽ là =VLOOKUP(A2,"Attorneys"! A:D,4,FALSE).
Tiếp tục thêm trường cho đến khi bạn có tất cả các trường bản thân cần. Nếu như bạn đang tra cứu cách chuẩn bị sổ thao tác chứa những tính năng dữ liệu sử dụng những bảng, hãy chuyển đổi nguồn tài liệu của tính năng dữ liệu thành bảng mới.
Sự cố
Đã xẩy ra lỗi gì
Trả về quý hiếm sai
Nếu range_lookup là TRUE hoặc vứt trống, cột đầu tiên cần được sắp xếp theo bảng chữ cái hoặc số. Nếu như cột thứ nhất không được sắp tới xếp, quý hiếm trả về hoàn toàn có thể là cực hiếm mà bạn không mong muốn đợi. Hoặc sắp xếp cột thứ nhất hoặc là các bạn sẽ dùng FALSE mang đến giá trị khớp bao gồm xác.
Lỗi #N/A trong ô
Nếu range_lookup là TRUE, thì nếu giá trị trong lookup_value nhỏ tuổi hơn giá chỉ trị bé dại nhất trong cột thứ nhất của table_array, bạn sẽ nhận quý hiếm lỗi #N/A.
Nếu range_lookup là FALSE, thì quý hiếm lỗi #N/A chỉ báo là không tìm thấy số bao gồm xác.
Để biết thêm tin tức về cách giải quyết các lỗi #N/A vào hàm VLOOKUP, hãy coi mục phương pháp sửa lỗi #N/A trong hàm VLOOKUP.
Lỗi #REF! trong ô
Nếu col_index_num to hơn số cột vào table-array, bạn sẽ nhận giá tốt trị lỗi #REF! .
Để biết thêm thông tin về cách giải quyết và xử lý các lỗi #REF! trong hàm VLOOKUP, hãy coi mục biện pháp sửa lỗi #REF!.
Lỗi #VALUE! trong ô
Nếu table_array nhỏ dại hơn 1, bạn sẽ nhận cực hiếm lỗi #VALUE! .
#NAME? trong ô
Giá trị lỗi #NAME? thường có nghĩa là công thức thiếu vết ngoặc kép. Để tìm tên của một người, hãy bảo đảm bạn dùng vết ngoặc kép xung quanh tên vào công thức. Ví dụ, hãy nhập thương hiệu là "Fontana" trong =VLOOKUP("Fontana",B2:E7,2,FALSE).
Để hiểu thêm thông tin, hãy xem mục phương pháp sửa lỗi #NAME!..
Lỗi #SPILL! trong ô
Lỗi vậy #SPILL! thường có nghĩa là công thức của khách hàng dựa vào giao điểm ẩn đến giá trị tra cứu với dùng toàn thể cột làm tham chiếu. Ví dụ: =VLOOKUP(A:A,A:C,2,FALSE). Chúng ta cũng có thể giải quyết sự cố bằng phương pháp neo tham chiếu tra cứu với toán tử như sau: =VLOOKUP(A:A,A:C,2,FALSE). Ngoại trừ ra, chúng ta có thể sử dụng phương thức VLOOKUP truyền thống và tham chiếu mang lại một ô duy nhất nắm vì tổng thể cột: =VLOOKUP(A2,A:C,2,FALSE).
Làm thế này
Lý do
Dùng tham chiếu tuyệt vời cho range_lookup
Bằng bí quyết dùng những tham chiếu tuyệt vời và hoàn hảo nhất sẽ được cho phép bạn điền từ bên trên xuống một phương pháp để nó luôn xem cùng phạm vi tra cứu chính xác.
Tìm hiểu bí quyết dùng tham chiếu ô tuyệt đối.
Không giữ trữ cực hiếm số hoặc ngày dưới dạng văn bản.
Khi tìm kiếm kiếm các giá trị số hoặc ngày, hãy đảm bảo an toàn dữ liệu vào cột trước tiên của table_array ko được lưu trữ như là những giá trị văn bản. Trong trường hòa hợp này, VLOOKUP rất có thể trả về một quý hiếm không đúng hoặc ko được ý muốn đợi.
Sắp xếp cột đầu tiên
Sắp xếp cột đầu tiên của table_array trước khi dùng VLOOKUP lúc range_lookup là TRUE.
Dùng ký tự đại diện
Nếu range_lookup là FALSE cùng lookup_value là văn bản, bạn có thể sử dụng các ký tự đại diện — vết chấm hỏi (?) cùng dấu sao (*) — vào lookup_value. Một lốt chấm hỏi khớp với bất kỳ ký tự đơn nào. Một dấu sao khớp với chuỗi ký tự bất kỳ. Nếu bạn có nhu cầu tìm vết chấm hỏi hay dấu sao thực sự, hãy nhập một dấu sóng (~) trước ký kết tự đó.
Ví dụ: =VLOOKUP("Fontan?",B2:E7;2,FALSE) vẫn tìm kiếm toàn bộ các phiên bản của Fontana với vần âm cuối cùng có thể khác nhau.
Hãy bảo đảm dữ liệu của bạn không chứa các ký tự ko đúng.
Khi kiếm tìm kiếm cực hiếm văn bạn dạng trong cột đầu tiên, hãy bảo đảm dữ liệu vào cột trước tiên không có khoảng trắng sinh sống đầu, khoảng tầm trắng sống cuối, áp dụng không thống nhất lốt ngoặc thẳng (" hoặc ") với cong (" hoặc "), hoặc ký tự không in ra. Trong số những trường hòa hợp này, VLOOKUP rất có thể trả về cực hiếm không mong muốn.
