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 cồn theo ngẫu nhiên hướng nào và trả về công dụng khớp đúng chuẩn theo mặc định, khiến cho việc sử dụng thuận lợi và dễ ợt hơn so với phiên phiên bản trước của nó.
Bạn đang xem: Công thức vlookup trong excel
Dùng hàm VLOOKUP khi bạn cần tìm tin tức trong một bảng hoặc dải ô theo hàng. Ví dụ: tra cứu giá mang lại một linh phụ kiện ô sơn theo số linh phụ kiện hoặc kiếm tìm tên nhân viên dựa trên ID nhân viên cấp dưới của họ.
Ở dạng đơn giản nhất, hàm VLOOKUP đến biết:
=VLOOKUP(Nội dung bạn có nhu cầu tra cứu, trên nơi bạn có nhu cầu tìm kiếm, số cột trong dải ô chứa giá trị đó sẽ trả về, trả về hiệu quả khớp sấp xỉ hoặc đúng mực – được thể hiện là 1/TRUE hoặc 0/FALSE).
Mẹo: Bí quyết để thực hiện hàm VLOOKUP là phải sắp xếp dữ liệu của công ty sao đến giá trị mà bạn có nhu cầu tra cứu vớt (Trái cây) nằm ở vị trí bên trái giá trị trả về (số tiền) mà bạn có nhu cầu tìm.
Sử dụng hàm VLOOKUP nhằm 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 thứ khách"! A:F,3,FALSE)
| lookup_value (bắt buộc) | Giá trị bạn có nhu cầu tra cứu. Giá chỉ trị bạn có nhu cầu tra cứu nên ở cột thứ nhất của phạm vi ô mà chúng ta chỉ định trong đối table_array đang chọn. Ví dụ, nếu table-array trải dài các ô B2:D7, thì mặt đường kết lookup_value bắt buộc ở cột B. Lookup_value có thể là một giá trị hoặc tham chiếu cho một ô. |
| table_array (bắt buộc) | Phạm vi những ô mà VLOOKUP sẽ tìm kiếm mang đến lookup_value và quý giá trả về. Bạn cũng có thể sử dụng phạm vi hoặc bảng đang đặt thương hiệu và chúng ta có thể sử dụng tên trong đối số thay do tham chiếu ô. Cột thứ nhất trong phạm vi ô cần chứa giá chỉ trị lookup_value. Phạm vi ô cũng cần bao hàm giá trị trả về mà bạn muốn tìm. Tìm hiểu phương 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ởi 1 cho 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ý giá lô-gic sẽ khẳng định xem bạn muốn 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 mang định rằng cột đầu tiên trong bảng được bố trí theo bảng vần âm hoặc số, kế tiếp sẽ kiếm tìm kiếm giá trị gần nhất. Đây đã 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 chuẩn - 0/FALSE sẽ tìm kiếm giá chỉ trị chính xác 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 thực hiện để desgin cú pháp mang lại hàm VLOOKUP:
Giá trị bạn có nhu cầu tra cứu, còn được gọi là giá trị tra cứu.
Dải ô đựng giá trị tra cứu. Hãy lưu giữ rằng giá trị tra cứu vớt phải luôn luôn nằm ngơi nghỉ cột trước tiên của dải ô nhằm hàm VLOOKUP có thể chuyển động chính xác. Ví dụ: Nếu giá trị tra cứu của công ty nằm ở ô C2 thì dải ô của bạn sẽ ban đầu ở C.
Số cột chứa giá trị trả về trong dải ô. Ví dụ: nếu bạn chỉ định B2:D11 có tác dụng dải ô thì chúng ta nên tính B là cột đầu tiên, C là cột máy hai, v.v..
Hay bạn cũng có thể chỉ định TRUE nếu bạn có nhu cầu có một công dụng 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ý hiếm trả về. Giả dụ bạn không những định bất cứ giá trị như thế nào thì cực hiếm mặc định sẽ luôn luôn là TRUE hay hiệu quả khớp tương đối.
Giờ thì hãy 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ề, hiệu quả khớp tương đối (TRUE) hoặc hiệu quả khớp đúng đắn (FALSE)).
Ví dụ
Dưới đó 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 tài 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 rất có thể dùng hàm VLOOKUP để phối hợp nhiều bảng thành một bảng, miễn là 1 trong những bảng tất cả trường phổ biến với toàn bộ các bảng khác. Điều này có thể đặc biệt hữu ích nếu như khách hàng cần share sổ thao tác với những người dân có phiên phiên bản Excel cũ hơn không cung cấp tính năng dữ liệu với rất nhiều bảng dưới dạng nguồn tài liệu - bằng phương pháp kết hợp những nguồn vào một bảng và thay đổi nguồn dữ liệu của tính năng dữ liệu sang bảng mới, kỹ năng dữ liệu có thể được sử dụng trong các phiên bản Excel cũ hơn (miễn là tính năng tài liệu được phiên bạn dạng cũ rộng hỗ trợ).
 |
| Ở đây, cột A-F với H có những giá trị hoặc cách làm chỉ sử dụng giá trị trên trang tính, còn phần còn sót lại của cột sử dụng VLOOKUP và giá trị cột A (Mã trang bị khách) với cột B (Luật sư) để mang dữ liệu từ những bảng khác. |
Sao chép bảng bao gồm trường tầm thường vào trang tính mới, rồi đặt tên cho bảng đó.
Bấm Dữ liệu > thế Dữ liệu > quan hệ nhằm mở hộp thoại làm chủ Mối quan tiền hệ.
Đối với mỗ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 vết ngoặc đơn trong hộp thoại). Đây là giá chỉ lookup_value cho bí quyết VLOOKUP của bạn.
Tên Bảng Tra cứu giúp Liên quan. Đây là cách table_array trong phương pháp 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 ko được hiển thị trong vỏ hộp thoại quản lý Mối dục tình - bạn sẽ phải quan sát vào Bảng Tra cứu liên quan để xem bạn có nhu cầu truy xuất trường nào. Bạn muốn ghi chú số cột (A=1) - đây là số hiệu col_index_num trong công thức của bạn.
Để thêm một trường vào bảng mới, hãy nhập cách làm VLOOKUP vào cột trống đầu tiên bằng phương pháp dùng tin tức bạn đã tích lũy ở cách 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 rất có thể dùng tham chiếu ô và tham chiếu dải ô. Trong lấy một ví dụ của 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 đến khi các bạn có tất cả các trường cần thiết. Nếu như khách hàng đang cầm gắng chuẩn bị một sổ làm việc chứa những tính năng dữ liệu sử dụng những bảng, hãy đổi khác nguồn tài liệu của tính năng dữ liệu sang bảng mới.
| Trả về cực hiếm sai | Nếu range_lookup là TRUE hoặc bỏ trống, cột thứ nhất cần được bố trí theo bảng vần âm hoặc số. Trường hợp cột đầu tiên không được chuẩn bị xếp, giá trị trả về rất có thể là quý giá mà bạn không mong đợi. Hoặc thu xếp cột trước tiên hoặc là các bạn sẽ dùng FALSE cho giá trị khớp chính xác. |
| Lỗi #N/A trong ô | Nếu range_lookup là TRUE, thì nếu giá trị trong lookup_value bé dại hơn giá bán trị nhỏ dại nhất trong cột đầu 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ố chủ yếu xác. Để biết thêm tin tức về cách xử lý các lỗi #N/A vào hàm VLOOKUP, hãy xem mục giải pháp sửa lỗi #N/A vào hàm VLOOKUP. |
| Lỗi #REF! trong ô | Nếu col_index_num lớn hơn số cột vào table-array, bạn sẽ nhận được giá trị lỗi #REF! . Để biết thêm thông tin về cách giải quyết các lỗi #REF! vào hàm VLOOKUP, hãy coi mục phương pháp sửa lỗi #REF!. |
| Lỗi #VALUE! vào ô | Nếu table_array bé dại hơn 1, các bạn sẽ nhận cực hiếm lỗi #VALUE! . Để biết thêm thông tin về cách giải quyết và xử lý các lỗi #VALUE! trong hàm VLOOKUP, hãy xem mục biện pháp sửa lỗi #VALUE! trong hàm VLOOKUP. |
| #NAME? trong ô | Giá trị lỗi #NAME? thường có nghĩa là công thức thiếu dấu ngoặc kép. Để tìm thương hiệu của một người, hãy bảo đảm bạn dùng lốt ngoặc kép bao quanh tên vào công thức. Ví dụ, hãy nhập tên là "Fontana" vào =VLOOKUP("Fontana",B2:E7,2,FALSE). Để hiểu biết thêm thông tin, hãy coi mục giải pháp sửa lỗi #NAME!.. |
| Lỗi #SPILL! vào ô | Lỗi ví dụ #SPILL! thường tức là công thức của chúng ta dựa vào giao điểm ẩn cho giá trị tra cứu cùng sử dụng cục bộ cột làm tham chiếu. Ví dụ, =VLOOKUP(A:A,A:C,2,FALSE). Chúng ta có thể giải quyết sự cố bằng cách neo tham chiếutra cứu vãn với toán tử |
| Dùng tham chiếu tuyệt đối hoàn hảo cho range_lookup | Bằng biện pháp dùng các tham chiếu hoàn hảo và tuyệt vời nhất sẽ có thể chấp nhận được bạn điền từ trên xuống một cách làm để nó luôn xem cùng phạm vi tra cứu chính xác. Tìm hiểu phương pháp dùng tham chiếu ô hay đố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 trong cột đầu tiên của table_array ko được tàng trữ như là những giá trị văn bản. Vào trường đúng theo này, VLOOKUP hoàn toàn có thể trả về một quý giá không đúng hoặc không được hy vọng đợ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 khi 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 những 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 ngẫu nhiên ký tự đối kháng nào. Một lốt sao khớp với chuỗi cam kết tự bất kỳ. Nếu bạn muốn tìm dấu chấm hỏi hay vết sao thực sự, hãy nhập một vệt sóng (~) trước cam kết tự đó. Ví dụ, =VLOOKUP("Fontan?",B2:E7,2,FALSE) vẫn tìm kiếm tất cả các trường hợp bao gồm Fontana với vần âm cuối cùng có thể thay đổi. |
| Hãy bảo đảm dữ liệu của bạn ko chứa các ký tự không đúng. | Khi tìm kiếm kiếm quý giá văn bạn dạng trong cột đầu tiên, hãy bảo vệ dữ liệu trong 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, thực hiện không thống nhất vệt ngoặc thẳng (" hoặc ") cùng cong (" hoặc "), hoặc cam kết tự không in ra. Trong những trường đúng theo này, VLOOKUP hoàn toàn có thể trả về giá trị không mong mỏi muốn. Để bao gồm được công dụng chính xác, hãy thử thực hiện hàm CLEAN hoặc hàm TRIM để loại bỏ khoảng trắng ở cuối những giá trị ô vào bảng. |
Bạn bắt buộc thêm trợ giúp?
Bạn luô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: tu dưỡng về VLOOKUP Thẻ Tham chiếu Nhanh: Mẹo khắc phục sự cố gắng về VLOOKUP giải pháp sửa lỗi #VALUE! trong hàm VLOOKUP cách sửa lỗi #N/A trong hàm VLOOKUP Tổng quan tiền về các công thức trong Excel giải pháp tránh các công thức bị lỗi Phát hiện lỗi trong phương pháp 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 coi trước miễn phí)