Nội dung nội dung bài viết được vắt vấn chuyên môn bởi TS. Nguyễn Thị Chinh Lam - Giảng viên khóa huấn luyện và đào tạo kế toán tổng hợp thực hành tại kế toán tài chính Lê Ánh.
Bạn đang xem: Cách làm hàm vlookup trong excel
Vlookup là giữa những hàm cơ bạn dạng thường cần sử dụng trong excel. Hàm vlookup có thể chấp nhận được người thực hiện tìm kiếm những giá trị theo cột.
Bài viết dưới đây, lực lượng giảng viên tại lớp học kế toán thực tế tại kế toán Lê Ánh đã hướng dẫn đưa ra tiết các bạn cú pháp và những sử dụng hàm Vlookup trong excel
Hàm vào Excel được thiết kế sẵn dùng giám sát hoặc thực hiện một công dụng nào đó. Việc thực hiện thành thạo các hàm vẫn giúp bọn họ tiết kiệm được tương đối nhiều thời gian đối với tính toán thủ công bằng tay không dùng hàm. Các hàm trong Excel rất đa dạng bao phủ nhiều lĩnh vực, bao hàm hàm không yêu cầu đối số, bao hàm hàm yêu mong một hoặc nhiều đối số, và những đối số rất có thể là đề xuất hoặc trường đoản cú chọn.
1. Ý nghĩa của hàm Vlookup trong excel
Hàm vlookup được dùng để làm tìm kiếm theo cột
Hàm vlookup rất có thể dùng hòa bình hoặc kết hợp với các hàm không giống như: Sum; If…2. Cú pháp của hàm Vlookup trong excel
=VLOOKUP(Lookup_value, Table_array, Col_index_num,
Trong đó:
Lookup_value: là giá trị dùng làm tìm kiếmTable_array: là vùng điều kiện để dò tìm quý giá tìm kiếm, cột trước tiên trong table_array là cột để tìm quý giá tìm kiếm. Table_array rất có thể cùng hoặc khác sheet với Lookup_value và cũng có thể cùng tệp tin hoặc khác file với Lookup_value. Thường để ở dạng add tuyệt đốiCol_index_num: Là thiết bị tự của cột bắt buộc lấy tài liệu trên báo giá trị cần tìm. Cột đầu tiên của vùng dữ liệu được tính là 1Range_lookup: Là giao diện tìm kiếm, gồm 2 dạng hình TRUE với FALSE. (Có thể tất cả hoặc không)TRUE: tương ứng với 1 là tìm kiếm tương đối
FALSE: tương xứng với 0 là search kiếm tuyệt đối tức Hàm VLOOKUP vẫn tìm kiếm các giá trị trùng khớp nhau trả toàn
- Hàm Vlookup nằm trong hàm tham chiếu cùng tìm kiếm. Các hàm tham chiếu sử dụng trong cách làm giúp cho họ khỏi nhọc công sửa chữa các phương pháp khi các giá trị giám sát và đo lường có sự vắt đổi
Có 3 một số loại tham chiếu:
Tham chiếu add tương đốiTham chiếu địa chỉ cửa hàng tuyệt đối
Tham chiếu lếu hợp
Lưu ý:
- giá bán trị bạn có nhu cầu tra cứu, có cách gọi khác là giá trị tra cứu
- Dải ô đựng giá trị tra cứu
Hãy nhớ rằng giá trị tra cứu vớt phải luôn nằm ngơi nghỉ cột trước tiên của dải ô để hàm VLOOKUP có thể vận động chính xác. Ví dụ: Nếu cực hiếm tra cứu của bạn nằm sinh hoạt ô C2 thì dải ô của khách hàng sẽ bắt đầu ở C.- Số cột đựng giá trị trả về vào dải ô.
Ví dụ, nếu khách hàng chỉ định B2: D11 cùng với phạm vi, chúng ta nên đếm B là cột đầu tiên, C là sản phẩm hai, v.v.
- Lựa chọn tác dụng trả về
Bạn hoàn toàn có thể chỉ định TRUE nếu bạn muốn có một tác dụng khớp tương đối hoặc FALSE nếu bạn muốn có một hiệu quả khớp đúng đắn ở cực hiếm trả về.Nếu bạn không những định bất cứ giá trị nào thì cực hiếm mặc định sẽ luôn là TRUE hay kết quả khớp tương đối.Khóa Học kế toán tài chính Online - xúc tiến Trực Tiếp Với giáo viên Kế Toán Trưởng trên 15 Năm kinh Nghiệm
3. để ý khi áp dụng hàm Vlookup
Sử dụng F4 để cố định dòng, cột:
- F4 (1 lần): để có giá trị hay đối. Hoàn hảo và tuyệt vời nhất được đọc là cố định và thắt chặt cột và thắt chặt và cố định dòng ⇒ $cột$dòng
Ví dụ: $B$9 ⇒ cố định cột B và thắt chặt và cố định dòng 9
- F4 (2 lần): để sở hữu giá trị kha khá cột và hoàn hảo dòng – Được gọi là cố định và thắt chặt dòng , không thắt chặt và cố định cột ⇒ cột$dòng
Ví dụ: B$9 ⇒ cố định loại 9, không cố định và thắt chặt cột B
- F4 (3 lần): để sở hữu giá trị kha khá dòng và hoàn hảo nhất cột- Được hiểu là cố định cột, không thắt chặt và cố định dòng ⇒ $cộtdòng
Ví dụ: $B9 ⇒ cố định cột B, không cố định và thắt chặt dòng 9
4. Lấy một ví dụ về hàm Vlookup trong Excel
Ta tất cả 2 bảng excel như sau:
Yêu cầu: Thêm thông tin về Quê quán vào bảng mặt trên
Cách thực hiện:
Bước 1: click chuột vào ô E6
Bước 2: Đặt công thức: =VLOOKUP(A6,$D$12:$F$17,2,0)
Trong đó:
A6 là giá bán trị buộc phải tìm kiếm (ở đây mã nhân viên cấp dưới là tài liệu chung giữa 2 bảng cần ta kiếm tìm mã nhân viên)
$D$12:$F$17 là vùng dò tìm giá trị tìm kiếm (cần search mã nhân viên cấp dưới ở bảng 2 để đưa thông tin quê quán bắt buộc vùng dò tìm kiếm là toàn bộ bảng 2)
2 là số cột chứa tin tức cần kiếm tìm (đang ước ao tìm tin tức quê quán. Quê tiệm là cột thứ hai trong bảng 2)
0 là loại dò tìm bao gồm xác
Bước 3: Sao chếp bí quyết xuống những dòng khác
Ta nhận được kết quả:
Trên đây là cú pháp với hướng dẫn chi tiết cách áp dụng hàm Vlookup trong excel. Trong bài viết kế toán Lê Ánh đã đưa ra ví dụ minh họa chi tiết để chúng ta dễ tưởng tượng khi áp dụng hàm vlookup. Nếu chúng ta vẫn còn vấn đề chưa nắm rõ về hàm Vlookup có thể để lại câu hỏi, SĐT mặt dưới nội dung bài viết để được giải đáp
Tham khảo thêm >> Khóa học Kế Toán Tổng phù hợp Thực Hành
Excel đến docongtuong.edu.vn 365 Excel mang lại docongtuong.edu.vn 365 dành đến m&#x
E1;y Mac Excel mang lại web Excel 2021 Excel 2021 đến Mac Excel 2019 Excel 2019 cho Mac Excel năm nhâm thìn Excel năm 2016 cho Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 xem th&#x
EA;m...Ẩn bớt
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 vận động theo bất kỳ hướng nào và trả về tác dụng khớp đúng mực theo mang định, giúp việc sử dụng dễ ợt và dễ dàng hơn đối với phiên bạn dạng trước.
Sử dụng hàm VLOOKUP khi bạn cần tìm ngôn từ trong một bảng hoặc dải ô theo hàng. Ví dụ: tra cứu vớt giá mang đến một linh phụ kiện ô tô theo số linh kiện hoặc search tên nhân viên cấp dưới dựa trên ID nhân viên của họ.
Ở dạng đơn giản dễ dàng nhất, hàm VLOOKUP cho biết:
=VLOOKUP(Nội dung bạn có nhu cầu tra cứu, nơi bạn muốn tìm nó, số cột trong phạm vi đựng giá trị đề xuất trả về, trả về kết quả khớp gần đúng hoặc đúng đắn – được bộc lộ 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 khách hàng sao mang lại giá trị mà bạn muốn tra cứu vớt (Trái cây) nằm ở vị trí bên trái cực hiếm trả về (số tiền) mà bạn có nhu cầu tìm.
Sử dụng hàm VLOOKUP để tra cứu quý hiếm 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)
| lookup_value (bắt buộc) | Giá trị bạn muốn tra cứu. Giá bán trị bạn muốn tra cứu yêu cầu nằm vào cột trước tiên của phạm vi ô mà bạn chỉ định vào đối table_array số. Ví dụ, ví như table-array trải dài các ô B2:D7, thì lookup_value các bạn phải làm việc trong cột B. Lookup_value rất có thể là một giá trị hoặc tham chiếu mang lại một ô. |
| table_array (bắt buộc) | Phạm vi các ô mà VLOOKUP sẽ tìm kiếm đến lookup_value và quý giá trả về. Bạn có thể sử dụng phạm vi hoặc bảng đang đặt tên và chúng ta 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 ô đề xuất chứa lookup_value. Phạm vi ô cũng cần bao gồm giá trị trả về mà bạn có nhu cầu tìm. Tìm hiểu cách 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 đến cột kế bên cùng phía trái của table_array) chứa 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 kết quả khớp kha khá hay công dụng khớp bao gồm xác: Kết quả khớp kha khá - 1/TRUE trả định rằng cột thứ nhất trong bảng được bố trí theo số hoặc theo bảng chữ cái và kế tiếp sẽ tìm kiếm quý hiếm gần nhất. Đây đang là phương pháp mặc định nếu như bạn không xác định cách thức nào khác. Ví dụ: =VLOOKUP(90,A1:B100,2,TRUE). Kết trái khớp chính xác - 0/FALSE kiếm 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ó bốn phần thông tin mà các bạn sẽ cần áp dụng để thi công cú pháp mang đến hàm VLOOKUP:
Giá trị bạn muốn tra cứu, còn gọi là giá trị tra cứu.
Dải ô đựng giá trị tra cứu. Hãy ghi nhớ rằng giá trị tra cứu giúp phải luôn luôn nằm ở cột trước tiên của dải ô để hàm VLOOKUP bao gồm thể vận động chính xác. Ví dụ: Nếu quý giá tra cứu của khách hàng nằm sống ô C2 thì dải ô của doanh nghiệp sẽ bước đầu ở C.
Số cột cất giá trị trả về vào dải ô. Ví dụ, nếu như bạn chỉ rõ B2:D11 là phạm vi, 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 kha khá hoặc FALSE nếu bạn có nhu cầu có một hiệu quả khớp đúng mực ở quý giá trả về. Giả dụ bạn không những định bất cứ giá trị nào thì giá trị mặc định sẽ luôn là TRUE hay tác dụng khớp tương đối.
Giờ thì nên tập hợp tất cả mục trên lại cùng với nhau, như sau:
=VLOOKUP(giá trị tra cứu, dải ô cất giá trị tra cứu, số cột vào phạm vi chứa giá trị trả về, kết quả khớp kha khá (TRUE) hoặc Khớp đúng mực (FALSE)).
Ví dụ
Dưới đó là một số lấy ví dụ như về hàm VLOOKUP:
Ví dụ 1
Ví dụ 2
Ví dụ 3
Ví dụ 4
Ví dụ 5
Bạn rất có thể sử dụng hàm VLOOKUP để kết hợp nhiều bảng thành một, miễn là 1 trong các bảng có các trường bình thường với tất cả các bảng khác. Điều này có thể đặc biệt hữu ích nếu bạn cần share sổ làm việc với những người có phiên phiên bản Excel cũ rộng không cung ứng các thiên tài dữ liệu có rất nhiều bảng làm cho nguồn dữ liệu - bằng cách kết hợp các nguồn vào một bảng và chuyển đổi nguồn tài liệu của tính năng dữ liệu sang bảng mới, chúng ta cũng có thể sử dụng tác dụng dữ liệu trong số phiên bạn dạng Excel cũ rộng (miễn là bạn dạng thân tính năng tài liệu được phiên phiên bản cũ hơn hỗ trợ).
 |
| Ở đây, những cột A-F với H có những giá trị hoặc công thức chỉ sử dụng những giá trị bên trên trang tính và những cột còn lại sử dụng VLOOKUP và các giá trị của cột A (Mã sản phẩm công nghệ khách) và cột B (Luật sư) để đưa 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 trong những trang tính bắt đầu và để tên đến bảng đó.
Hãy bấm >Công cụ Dữ > Quan hệ để mở hộp quản lý Quan hệ dữ liệu.
Đối cùng với mỗi mối quan hệ được liệt kê, hãy chú ý những điều sau đây:
Trường liên kết những bảng (được liệt kê trong lốt ngoặc đối kháng trong hộp thoại). Đây là công thức lookup_value cho phương pháp VLOOKUP của bạn.
Tên Bảng Tra cứu vãn Liên quan. Đây là công thức table_array trong cách làm VLOOKUP của bạn.
Trường (cột) vào Bảng Tra cứu tương quan có dữ liệu bạn có nhu cầu trong cột mới. Thông tin này ko được hiển thị trong hộp thoại làm chủ Quan hệ - các bạn sẽ phải coi Bảng Tra cứu tương quan để xem bạn muốn truy xuất trường nào. Bạn có nhu cầu ghi chú số cột (A=1) - đây 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 công thức VLOOKUP vào cột trống đầu tiên bằng cách dùng thông tin bạn đã thu thập ở cách 3.
Trong lấy một ví dụ của chúng tôi, cột G thực hiện Attorney (the lookup_value) để lấy dữ liệu Bill Rate trường đoản cú cột thứ tư (col_index_num = 4) từ bỏ bảng trang tính Attorneys, tbl
Attorneys ( table_array), với cách làm =VLOOKUP(<
Attorney>,tbl_Attorneys,4,FALSE).
Công thức cũng hoàn toàn có thể sử dụng tham chiếu ô cùng tham chiếu dải ô. Trong lấy ví dụ của bọn chúng tôi, hàm sẽ là =VLOOKUP(A2,"Attorneys"! A:D,4,FALSE).
Tiếp tục thêm trường cho tới khi các bạn có tất cả các trường mình cần. Nếu khách hàng đang kiếm tìm cách chuẩn bị sổ thao tác làm việc chứa những tính năng tài liệu sử dụng các bảng, hãy biến đổi nguồn dữ liệu của tính năng tài liệu thành bảng mới.
| Trả về quý giá sai | Nếu range_lookup là TRUE hoặc quăng quật trống, cột đầu tiên cần được bố trí theo bảng chữ cái hoặc số. Ví như cột trước tiên không được chuẩn bị xếp, quý giá trả về rất có thể là giá trị mà các bạn không ước ao đợi. Hoặc bố trí cột đầu tiên 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ý hiếm trong lookup_value nhỏ dại hơn giá bán trị nhỏ nhất trong cột thứ nhất của table_array, các 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ố chủ yếu 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! vào ô | Nếu col_index_num lớn 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 coi mục cách sửa lỗi #REF!. |
| Lỗi #VALUE! trong ô | Nếu table_array nhỏ tuổi hơn 1, các bạn sẽ nhận quý hiếm lỗi #VALUE! . Để biết thêm thông tin về cách xử lý các lỗi #VALUE! vào hàm VLOOKUP, hãy xem mục cách sửa lỗi #VALUE! vào hàm VLOOKUP. |
| #NAME? vào ô | 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 vệt ngoặc kép xung quanh tên trong 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 biết thêm thông tin, hãy xem mục cách 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 chúng ta dựa vào giao điểm ẩn mang đến giá trị tra cứu với dùng toàn thể cột làm cho tham chiếu. Ví dụ: =VLOOKUP(A:A,A:C,2,FALSE). Bạn cũng có thể giải quyết sự cố bằng phương pháp neo tham chiếu tra cứu vớt với toán tử |
| Dùng tham chiếu tuyệt vời nhất cho range_lookup | Bằng giải pháp dùng các tham chiếu hoàn hảo sẽ được cho phép bạn điền từ trên xuống một công thức để nó luôn xem thuộc phạm vi tra cứu chính xác. Tìm hiểu biện pháp dùng tham chiếu ô xuất xắc đối. |
| Không lưu trữ quý giá số hoặc ngày bên 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 dữ liệu vào cột đầu tiên của table_array không được tàng trữ như là những giá trị văn bản. Vào trường thích hợp này, VLOOKUP rất có thể trả về một cực 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 lúc dùng VLOOKUP khi range_lookup là TRUE. |
| Dùng cam kết tự đại diện | Nếu range_lookup là FALSE cùng lookup_value là văn bản, chúng ta có thể sử dụng các ký tự thay mặt — vệt chấm hỏi (?) cùng dấu sao (*) — trong lookup_value. Một vệt chấm hỏi khớp với bất kỳ ký tự đối kháng nào. Một vệ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) đã tìm kiếm tất cả các phiên phiên bản của Fontana với vần âm cuối cùng hoàn toàn có thể khác nhau. |
| 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 giá trị văn phiên bản trong cột đầu tiên, hãy bảo đảm dữ liệu vào cột thứ nhất không có tầm khoảng trắng làm việc đầu, khoảng chừng trắng ở cuối, thực hiện không thống nhất lốt ngoặc trực tiếp (" hoặc ") và cong (" hoặc "), hoặc ký kết tự ko in ra. Giữa những trường thích hợp này, VLOOKUP có thể trả về quý giá không mong muốn. Để gồm được hiệu quả chính xác, hãy thử áp dụng hàm CLEAN hoặc hàm TRIM để loại bỏ khoảng chừng trắng ở cuối các giá trị ô trong bảng. |