Hàm tìm kiếm có điều kiện trong excel

      488
Excel cho dienmayxuyena.com 365 Excel cho dienmayxuyena.com 365 dành đến máy Mac Excel cho web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel năm 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 xem thêm...Ít hơn

Mẹo: Hãy thử dùng hàm XLOOKUP mới, một phiên bản VLOOKUP được đổi mới hoạt động theo ngẫu nhiên hướng nào và trả về công dụng khớp đúng mực theo mặc định, khiến cho việc sử dụng tiện lợi và thuận tiện hơn đối với phiên phiên bản trước của nó.

Bạn đang xem: Hàm tìm kiếm có điều kiện trong excel


Dùng hàm VLOOKUP khi chúng ta cần tìm thông tin trong một bảng hoặc dải ô theo hàng. Ví dụ: tra cứu vãn giá đến một linh phụ kiện ô sơn theo số linh kiện hoặc tìm tên nhân viên cấp dưới dựa bên 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, trên nơi bạn muốn tìm kiếm, số cột trong dải ô chứa giá trị này sẽ trả về, trả về tác dụng khớp gần đúng hoặc đúng chuẩn – được biểu hiện là 1/TRUE hoặc 0/FALSE).


*

Mẹo: Bí quyết để sử dụng hàm VLOOKUP là phải thu xếp dữ liệu của công ty sao mang lại giá trị mà bạn có nhu cầu tra cứu giúp (Trái cây) nằm ở bên trái quý hiếm trả về (số tiền) mà bạn muốn tìm.


Sử dụng hàm VLOOKUP để tra cứu quý giá trong bảng.

Cú pháp 

VLOOKUP (lookup_value, table_array, col_index_num, )

Ví dụ:

=VLOOKUP(A2,A10:C20,2,TRUE)

=VLOOKUP("Fontana",B2:E7,2,FALSE)

=VLOOKUP(A2,"Chi tiết sản phẩm khách"! A:F,3,FALSE)

Tên đối số

Mô tả

lookup_value (bắt buộc)

Giá trị bạn muốn tra cứu. Giá trị bạn muốn tra cứu buộc phải ở cột đầu tiên của phạm vi ô mà các bạn chỉ định vào đối table_array vẫn chọn.

Ví dụ, nếu như table-array trải dài các ô B2:D7, thì mặt đường kết lookup_value cần ở cột B.

Lookup_value rất có thể là một cực hiếm hoặc tham chiếu cho một ô.

table_array (bắt buộc)

Phạm vi những ô nhưng mà VLOOKUP sẽ tìm kiếm đến lookup_value và giá trị trả về. Bạn có thể sử dụng phạm vi hoặc bảng sẽ đặt thương hiệu và bạn có thể sử dụng tên trong đối số thay do tham chiếu ô. 

Cột đầu tiên trong phạm vi ô yêu cầu chứa giá bán trị 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 trong một trang tính.

col_index_num (bắt buộc)

Số cột (bắt đầu bằng 1 mang đến cột nhiều phần bên tráitable_array ) đựng giá trị trả về.

range_lookup (tùy chọn)

Một quý hiếm lô-gic sẽ xác định xem bạn muốn hàm VLOOKUP tìm công dụng khớp tương đối hay tác dụng khớp chính xác:

Kết trái khớp kha khá - 1/TRUE giả định rằng cột trước tiên trong bảng được thu xếp theo bảng vần âm hoặc số, sau đó sẽ tìm kiếm kiếm quý hiếm gần nhất. Đây đã là cách thức mặc định nếu bạn không xác định phương thức nào khác. Ví dụ: =VLOOKUP(90,A1:B100,2,TRUE).

Kết quả khớp chính xác - 0/FALSE đang tìm kiếm giá chỉ 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à các bạn sẽ cần áp dụng để chế tạo 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 ô chứa giá trị tra cứu. Hãy lưu giữ rằng quý hiếm tra cứu vãn phải luôn nằm sinh hoạt cột trước 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 cực hiếm tra cứu của doanh nghiệp nằm sinh sống ô C2 thì dải ô của công ty sẽ ban đầu ở C.

Số cột chứa giá trị trả về trong dải ô. Ví dụ: nếu như bạn chỉ định B2:D11 làm dải ô thì chúng ta nên tính B là cột đầu tiên, C là cột thiết bị hai, v.v..

Hay chúng ta có thể chỉ định TRUE nếu bạn có nhu cầu có một hiệu quả khớp tương đối hoặc FALSE nếu bạn có nhu cầu có một công dụng khớp đúng đắn ở quý giá trả về. Trường hợp bạn không chỉ định bất kể giá trị như thế nào thì cực hiếm mặc định sẽ luôn luôn là TRUE hay kết quả khớp tương đối.

Giờ thì nên tập hợp tất cả mục bên trên lại với nhau, như sau:

=VLOOKUP(giá trị tra cứu, dải ô cất giá trị tra cứu, số cột trong dải ô chứa giá trị trả về, kết quả khớp tương đối (TRUE) hoặc công dụng khớp đúng chuẩn (FALSE)).

Ví dụ

Dưới đây là một số ví dụ về hàm VLOOKUP:

Ví dụ 1

*

Ví dụ 2

*

Ví dụ 3

*

Ví dụ 4

*

Ví dụ 5

*


Kết hợp dữ liệu từ một số trong những bảng vào trong 1 trang tính bằng cách dùng VLOOKUP

Bạn hoàn toàn có thể dùng hàm VLOOKUP để kết hợp nhiều bảng thành một bảng, miễn là một trong trong các bảng có trường chung với toàn bộ các bảng khác. Điều này hoàn toàn có thể đặc biệt hữu ích nếu như khách hàng cần share sổ thao tác làm việc với những người có phiên phiên bản Excel cũ rộng không cung cấp tính năng dữ liệu với nhiều bảng bên dưới dạng nguồn dữ liệu - bằng phương pháp kết hợp các nguồn vào trong 1 bảng và biến hóa nguồn dữ liệu của tính năng dữ liệu sang bảng mới, tính năng dữ liệu rất có thể được sử dụng trong những phiên bạn dạng Excel cũ rộng (miễn là tính năng tài liệu được phiên bản cũ rộng hỗ trợ).

*

Ở đây, cột A-F cùng H có các giá trị hoặc cách làm chỉ áp dụng giá trị trên trang tính, còn phần sót lại của cột thực hiện VLOOKUP và quý giá cột A (Mã vật dụng khách) với cột B (Luật sư) để đưa dữ liệu từ những bảng khác.

Sao chép bảng có trường phổ biến vào trang tính mới, rồi đặt tên mang đến bảng đó.

Bấm Dữ liệu > nạm Dữ liệu > quan hệ nhằm mở hộp thoại quản lý Mối quan lại hệ.

*

Đối cùng với mỗi mối quan hệ được liệt kê, hãy để ý những điều sau đây:

Trường liên kết những bảng (được liệt kê trong dấu ngoặc 1-1 trong vỏ hộp thoại). Đây là giá 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ách table_array trong bí quyết VLOOKUP của bạn.

Trường (cột) trong Bảng Tra cứu liên 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 hộp thoại thống trị Mối dục tình - các bạn sẽ phải nhìn vào Bảng Tra cứu tương quan để xem bạn muốn truy xuất ngôi trường nào. Bạn có nhu cầu ghi chú số cột (A=1) - đấy là số hiệu col_index_num trong cách làm của bạn.

Xem thêm: Top 12 Bộ Phim Cổ Trang Của Tiêu Ân Tuấn Sẵn Sàng Giả Gái Vì Chiều Con

Để thêm một trường vào bảng mới, hãy nhập phương pháp VLOOKUP vào cột trống đầu tiên bằng cách dùng tin tức bạn đã tích lũy ở bước 3.

In our example, column G uses Attorney (the lookup_value) to get the Bill Rate data from the fourth column (col_index_num = 4) from the Attorneys worksheet table, tblAttorneys (the table_array), with the formula =VLOOKUP(<
Attorney>,tbl_Attorneys,4,FALSE)
.

Công thức này cũng có thể dùng tham chiếu ô với tham chiếu dải ô. Trong lấy một ví dụ của bọn chúng tôi, tài liệu đó sẽ là =VLOOKUP(A2,"Attorneys"! A:D,4,FALSE).

Tiếp tục thêm những trường cho tới khi bạn có tất cả các trường đề xuất thiết. Nếu khách hàng đang cố gắng gắng sẵn sàng một sổ thao tác làm việc chứa những tính năng dữ liệu sử dụng các bảng, hãy biến hóa nguồn dữ liệu của tính năng tài liệu sang bảng mới.


Sự cố

Đã xảy ra lỗi gì

Trả về giá trị sai

Nếu range_lookup là TRUE hoặc bỏ trống, cột trước tiên cần được sắp xếp theo bảng vần âm 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ề có thể là cực hiếm mà chúng ta không muốn đợi. Hoặc bố trí cột thứ nhất hoặc là các bạn sẽ dùng FALSE cho giá trị khớp chủ yếu xác.

Lỗi #N/A vào ô

Nếu range_lookup là TRUE, thì nếu quý giá trong lookup_value nhỏ tuổi hơn giá chỉ trị bé dại nhất trong cột trước tiên của table_array, các bạn sẽ nhận giá trị lỗi #N/A.

Nếu range_lookup là FALSE, thì cực hiếm lỗi #N/A chỉ báo là không kiếm thấy số bao gồm xác.

Để biết thêm tin tức về cách giải quyết và xử lý các lỗi #N/A vào hàm VLOOKUP, hãy xem mục cách sửa lỗi #N/A vào hàm VLOOKUP.

Lỗi #REF! trong ô

Nếu col_index_num to hơn số cột trong 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 xem mục bí quyết sửa lỗi #REF!.

Lỗi #VALUE! vào ô

Nếu table_array nhỏ hơn 1, các bạn sẽ nhận quý giá lỗi #VALUE! .

Để biết thêm thông tin về cách xử lý các lỗi #VALUE! trong hàm VLOOKUP, hãy coi mục giải pháp sửa lỗi #VALUE! trong hàm VLOOKUP.

#NAME? vào ô

Giá trị lỗi #NAME? thường có nghĩa là công thức thiếu lố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 bao phủ tên trong công thức. Ví dụ, hãy nhập tên là "Fontana" trong =VLOOKUP("Fontana",B2:E7,2,FALSE).

Để biết thêm thông tin, hãy coi mục biện pháp sửa lỗi #NAME!..

Lỗi #SPILL! vào ô

Lỗi cụ thể #SPILL! thường tức là công thức của bạn dựa vào giao điểm ẩn mang đến giá trị tra cứu và sử dụng cục bộ cột có tác dụng tham chiếu. Ví dụ, =VLOOKUP(A:A,A:C,2,FALSE). Bạn có thể giải quyết sự cố bằng phương pháp neo tham chiếutra cứu vớt với toán tử
như sau: =VLOOKUP(
A:A,A:C,2,FALSE). Ko kể ra, chúng ta có thể sử dụng phương pháp VLOOKUP truyền thống lịch sử vàtham chiếu mang đến một ô duy nhất cố kỉnh vì cục bộ cột: =VLOOKUP( A2,A:C,2,FALSE).


Làm thế này

Lý do

Dùng tham chiếu hoàn hảo và tuyệt vời nhất cho range_lookup

Bằng giải pháp dùng các tham chiếu tuyệt đối hoàn hảo sẽ 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 thiết yếu xác.

Tìm hiểu bí quyết dùng tham chiếu ô tốt đối.

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

Khi tra cứu 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 đầu tiên của table_array không được lưu trữ như là những giá trị văn bản. Trong trường vừa lòng này, VLOOKUP hoàn toàn có thể trả về một quý hiếm không đúng hoặc không được ước ao đợ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 — dấu chấm hỏi (?) với dấu sao (*) — vào lookup_value. Một vết chấm hỏi khớp với ngẫu nhiên ký tự 1-1 nào. Một lốt 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 vệt 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) đang tìm kiếm tất cả các trường hợp có Fontana với vần âm cuối cùng hoàn toàn có thể thay đổi.

Hãy bảo đảm dữ liệu của bạn không chứa các ký tự không đúng.

Khi kiếm tìm kiếm quý giá văn bản trong cột đầu tiên, hãy đảm bảo dữ liệu vào cột đầu tiên không có tầm khoảng trắng sinh hoạt đầu, khoảng trắng sống cuối, thực hiện không thống nhất vết ngoặc trực tiếp (" hoặc ") và cong (" hoặc "), hoặc ký kết tự không in ra. Trong số những trường đúng theo này, VLOOKUP hoàn toàn có thể trả về cực hiếm không ước ao muốn.

Để có được công dụng chính xác, hãy thử áp dụng hàm CLEAN hoặc hàm TRIM để loại bỏ khoảng trắng ở cuối những giá trị ô trong bảng.


Bạn đề nghị thêm trợ giúp?

Bạn luôn hoàn toàn có thể hỏi một chuyên viên trong xã hội Kỹ thuật Excel hoặc thừa nhận sự cung ứng trongCộng đồng trả lời.

Thẻ Tham chiếu Nhanh: bồi dưỡng về VLOOKUP Thẻ Tham chiếu Nhanh: Mẹo khắc phục và hạn chế sự nỗ lực về VLOOKUP biện pháp sửa lỗi #VALUE! vào hàm VLOOKUP biện pháp sửa lỗi #N/A trong hàm VLOOKUP Tổng quan tiền về các công thức vào Excel phương pháp tránh những công thức bị lỗi Phát hiện nay lỗi trong bí quyết các hàm Excel (theo bảng chữ cái) các hàm Excel (theo danh mục) Hàm VLOOKUP (bản xem trước miễn phí)