Cách dụng hàm VLOOKUP trong Excel
Hàm Vlookup là một hàm dùng để tìm kiếm thông tin cần tìm trong bảng, một phạm vi theo hàng hoặc cột, bảng nào đó. Show
Ví dụ: Tra cứu một số lượng iPhone 11 được bán trong tháng 8/2020 của công ty cổ phần Thế Giới Di Động, hoặc tra cứu tên cửa hàng trưởng của Thế Giới Di Động tại TPHCM. Công thức của hàm Vlookup là: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Trong đó: - Lookup_value: là giá trị cần dò tìm - table_array: là bảng cần dò tìm - col_index_num: là vị trí cột cần lấy giá trị - range_lookup: nhập vào số 0 để tìm chính xác và nhập vào số 1 để tìm gần đúng (thông thường nhập số 0) Trước khi vào tìm hiểu hàm Vlookup khô khan thì mình đi mua sắm tí nhé! Hôm nay cùng mình đi mua snack nghen! Lấy ví dụ thực thế để minh họa hàm Vlookup Khi bạn vào một cửa hàng để mua snack, việc đầu tiên là tìm được line (hàng) có snack trong khu vực nào của cửa hàng, vị trí chính xác chưng bày snack trong line đó, và quyết định mua, một là mua snack bất kì bạn gặp ngẫu nhiên, miễn là snack bạn sẽ mua (tương đối), hai là mua snack đúng loại bạn thích từ thương hiệu, loại, kích thước và hương vị (giá trị tuyệt đối). Từ đó chúng ta có công thức mua snack như sau: Tìm được line có snack > Biết hàng đó trong khu vực nào của cửa hàng > Vị trí chính sát chưng bày snack trong line đó > Đưa ra quyết đinh mua snack theo hướng tương đối hay tuyệt đối. 1. Tìm được line có snack > Giá trị cần dò tìm (Lookup_value) => G6 Giá trị cần dò tìm trong hàm Vlookup 2. Trong khu vực nào có chứa snack của cửa hàng > Bảng chứa giá trị cần dò tìm (Table_array) => (B1:D4) Bảng giá trị cần tìm trong hàm Vlookup 3. Vị trí chính xác chưng bày snack trong line đó > Vị trí cột cần lấy giá trị trong bảng chứa giá trị cần dò tìm (Col_index_num) => 2 Vị trí cần dò tìm trong hàm Vlookup 4. Đưa ra quyết định mua tương đối hay tuyệt đối > Tìm kiếm tương đối hoặc tuyệt đối (Col_index_num) =>1 Chọn giá trị tuyệt đối, tương đối trong hàm Vlookup Các bạn gõ dấu “=” sau đó nhập VLOOKUP( Và ta có công thức hoàn chỉnh cho công cuộc đi mua snack: = Vlookup(G6,B1:D4,2,1) Giá trị tìm được Khóa bảng tìm kiếm để máy tính hiểu rằng chỉ tìm giá trị trong bảng đã khóa, không tìm giá trị ra ngoài. Trong trường hợp này hàm công thức đang tự đối chiếu giá trị rỗng ngoài bảng nên sẽ báo lỗi ngay khi bạn Enter đấy! Lỗi không khóa bản tìm kiếm Bị lỗi #N/A vậy nên cần khóa bảng để tìm đúng giá trị và tránh mắc lỗi ở các trường hợp cơ bản. Cẩn thận bạn nhé! lỗi #N/A khi không khóa bản đối chiếu dữ liệu Ví dụ: Đề bài: Hãy xếp loại học sinh theo thứ hạng yếu, trung bình, khá, giỏi,... dựa trên 2 bảng cho trước dưới đây và sử dụng hàm Vlookup để tìm kiếm. Hãy sử dụng hàm Vlookup, để xếp loại thứ hạng trong 2 bảng sau. Áp dụng công thức hàm Vlookup ta có: =VLOOKUP(B2,$E$2:$G$9,2,0) Trong đó: B2: Giá trị cần dò tìm giá trị $E$2:$G$9: Bảng đối chiếu giá trị (đã khóa) 2: Vị trí cột cần dò tìm 0: Chọn giá trị tuyệt đối để dò tìm Chọn giá trị tuyệt đối để dò tìm Và kết quả là: Và kết quả Gặp lỗi #N/A vì giá trị so sánh lấy tuyệt đối tức giá trị cần tìm là các số nguyên dương từ 1 đến 10, không chấp nhận bất cứ số nào trong khoảng hoặc chứa dấu phẩy. Vd: Khi nhập công thức chúng ta chọn 0 hoặc FALSE tức là kết quả trả về chỉ nhận và trả về giá trị 9 chứ không chấp nhận giá trị 9,5. => Vì vậy chúng ta cần lưu ý khi chọn điều kiện cho giá trị hiển thị, tuyệt đối hay tương đối để đảm bảo đúng sự tìm kiếm của mình. Sửa lại công thức ta có công thức đúng như sau: Sửa lại công thức ta có công thức đúng Đây là link bài tập bạn có thể thực hành hàm Vlookup ngày tại đây Tham khảo: Cách sửa lỗi #N/A trong Excel có ví dụ minh họa
Nếu chưa phát hiện lỗi #NA thì bạn có thể khắc phục lỗi #NA bằng công thức sau. = IFERROR (công thức gốc, giá trị sẽ hiển thị nếu công thức gốc có lỗi). Lấy ví dụ trên minh họa: = IFERROR (VLOOKUP(B5,$G$2:$H$14,2,0)”Tìm giá trị lỗi”) Tham khảo: Cách sửa lỗi #REF trong Excel có ví dụ minh họa
Ví dụ lỗi #REF! xảy ra do việc xóa cột. -> Bạn có thể bấm ngay nút Hoàn tác trên Thanh công cụ Truy nhập Nhanh (hoặc nhấn CTRL+Z) để khôi phục hàng hoặc cột đó. Ví dụ: VLOOKUP với tham chiếu dải ô không chính xác. -> Điều chỉnh phạm vi có lớn hơn hoặc giảm cột tra cứu giá trị để khớp với tham chiếu phạm vi. =VLOOKUP(A8,A2:E5,5,false) sẽ là một phạm vi tham chiếu hợp lệ, như làm =VLOOKUP(A8,A2:D5,4,FALSE). Tham khảo: Cách sửa lỗi #VALUE trong Excel có ví dụ minh họa
a. Sự cố: Tham đối giá_trị_tra_cứu có nhiều hơn 255 ký tự. Giải pháp: Rút ngắn giá trị hoặc sử dụng kết hợp hàm INDEX và MATCH như một giải pháp thay thế. b. Sự cố: Số thứ tự cột giá trị hiển thị (Col_index_num) chứa văn bản hoặc nhỏ hơn 0.
Bài viết này đã giới thiệu cũng như hướng dẫn chi tiết về hàm Vlookup cơ bản. Hy vọng bài viết này đã mang đến bạn những thông tin bổ ích. Đừng quên để lại nhận xét, like và share bài viết này nhé! Trong Excel, hàm Vlookup là một hàm rất quan trọng, được sử dụng rất nhiều để hỗ trợ trong công việc. Nếu công việc của bạn thường xuyên sử dụng Excel thì bổ sung các hàm trong Excel, trong đó có hàm Vlookup là điều rất cần thiết. Cách thực hiện này giúp bạn: Hàm VLOOKUP trong Excel là hàm tìm kiếm giá trị theo cột và trả về phương thức hàng dọc, đây có thể nói là hàm excel khá thông dụng và tiện lợi với những người hay sử dụng Excel để thống kê, dò tìm dữ liệu theo cột chính xác. Trong khuôn khổ thủ thuật học excel, Taimienphi.vn sẽ trình bày tới bạn đọc 2 cách dò tìm cơ bản của hàm này. Ví dụ hàm Vlookup, bài tập hàm Vlookup trong Excel.Mục Lục bài viết: Cách sử dụng hàm Vlookup trong ExcelI. Cú pháp hàm VlookupLưu ý cú pháp hàm:VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])Trong đó: lookup_value: Giá trị dùng để dò tìm. table_array: Bảng giá trị dò, để ở dạng địa chỉ Tuyệt đối (có dấu $ phía trước bằng cách nhấn F4). col_index_num: Thứ tự của cột cần lấy dữ liệu trên bảng giá trị dò. range_lookup: Phạm vi tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối). II. Ví dụ về hàm Vlookup trong Excel1. Cách dò tìm tương đối: VD: Căn cứ vào bảng quy định xếp loại tương ứng với điểm đã cho, tiến hành xếp loại học lực cho các sinh viên có tên trong danh sách dưới: Ví dụ về hàm Vlookup excelTa sử dụng công thức ở cột D6 là: =VLOOKUP(C6,$C$16:$D$20,2,1) Kết quả khi dùng hàm vlookup 2. Cách dò tìm tuyệt đối Với cách dò tìm tuyệt đối thì bạn sẽ tìm được chi tiết hơn cách dò tìm tương đối. Ví dụ dò tìm tuyệt đối khi dùng hàm vlookupĐể điền thông tin quê quán của nhân viên ta dùng công thức Vlookup cho ô E6 như sau: =VLOOKUP(A6,$D$12:$F$17,2,0) Cách sử dụng dò tuyệt đối với hàm Vlookup Tương tự để điền vào ô trình độ của nhân viên ta làm như sau: Với ô F6 có công thức : =VLOOKUP(A6,$D$12:$F$17,3,0) Kết quả dùng hàm Vlookup dò giá trị tuyệt đối 3. Giá trị tương đối và giá trị tuyệt đối Với giá trị tuyệt đối bạn sẽ tìm kiếm được kết quả chính xác nhất, các giá trị tuyệt đối sẽ không bị thay đổi địa chỉ dù xuất hiện ở các cột, ô mới. Do đó chúng ta sử dụng giá trị tuyệt đối trong trường hợp cần tìm chính xác nhất thông tin và sử dụng giá trị tương đối trong trường hợp tìm kiếm các kết quả chính xác, gần chính xác nhất. Để tìm hiểu thêm, bạn đọc có thể tham khảo thêm bài viết về tham chiếu các hàm trong Excel nhé. III. Hàm Vlookup kết hợp hàm Hlookup, Left, Right, MatchỞ nội dung trên Taimienphi.vn đã hướng dẫn bạn tìm hiểu cơ bản về hàm Vlookup, để giúp các bạn hiểu hơn và áp dụng thực tế hơn, bây giờ chúng tôi sẽ đưa ra một tình huống bài tập cụ thể như sau. Bài toán này là sự kết hợp giữa hàm Vlookup và các hàm trong Excel như hàm Hlookup, hàm Left, hàm Right và hàm Match. Cho dữ liệu như bảng dưới đây với quy ước như sau: Yêu cầu cần giải quyết: Câu 1: Căn cứ vào 2 ký tự bên trái và 2 ký tự bên phải của Mã SP trong Bảng 1, hãy tra trong Bảng 2 đề điền giá trị cho cột Tên Hàng - Tên Hãng Sản xuất (Tính dữ liệu cho cột C5:C10). Đáp án: Câu 1: + Lấy Tên hàng: Lấy 2 ký tự bên trái trong cột Mã SP ở Bảng 1 (A5:A10) so với 2 ký tự trong cột Mã Hàng ở Bảng 2, nhưng vùng dữ liệu ta lấy sang cả cột Tên Hàng ở Bảng 2 (A15:B20) hoặc (A15:E20). + Lấy Tên Tỉnh Sản xuất: Lấy 2 ký tự bên phải trong cột Mã SP(A5:A10) ở Bảng 1 so với 2 ký tự trong "Hàng" Mã gạo - Tên tỉnh sản xuất ở Bảng 2, vùng dữ liệu lấy sẽ (A16:E20). = HLOOKUP(RIGHT(A5,2),$C$16:$E$20,2,FALSE) + Ghép 2 công thức: Có nhiều công thức để ghép hay nối chuỗi lại với nhau, ở bài toán này Taimienphi.vn hướng dẫn các bạn cách nối chuỗi sử dụng hàm &. Trong bài toán này, chúng tôi sẽ sử dụng dấu gạch ngang (có dấu cách trống) để ngăn cách 2 công thức đã tính toán ra kết quả ở trước, tức là Tên Hàng và Tên Tỉnh Sản Xuất, cụ thể là: " - " C6=+VLOOKUP(LEFT(A6,2),$A$15:$B$20,2,FALSE)&" - "&HLOOKUP(RIGHT(A6,2),$C$16:$E$20,2,FALSE) Câu 2: Tính Đơn giá Câu 3: Tính thành tiền IV. Sửa lỗi #NA hàm VlookupLỗi #NA là gì?Lỗi #NA được trả về trong công thức excel khi không tìm thấy giá trị thích hợp Khi sử dụng hàm Vlookup, chúng ta gặp lỗi #NA khi không tìm thấy điều kiện tìm kiếm trong vùng điều khiển, chính xác ở đây là cột đầu tiên trong vùng điều kiện của hàm Vlookup Cách sửa lỗi #NA trong hàm Vlookup: Trong Excel, hàm Vlookup được sử dụng phổ biến giúp bạn có thể thực hiện hàm tìm kiếm giá trị, trả về phương thức hàng dọc tốt, nhanh chóng. Hiện nay, bạn có thể áp dụng một trong hai cách dò hàm Vlookup. Với hai cách dò tìm tương đối và tuyệt đối của hàm Vlookup, hàm dò tìm giá trị theo cột bạn dễ dàng thống kê dữ liệu trên bảng tính Excel. Vlookup giúp cho việc thống kê các chi tiết để làm báo cáo lọc ra các danh sách cần thiết, công việc của bạn sẽ chính xác và rút ngắn thời gian hơn. Đây là hàm được dùng khá nhiều trong quá trình thao tác trên bảng tính. Để học tốt cũng như hiểu rõ hơn về hàm tìm kiếm bạn có thể tham khảo hàm Hlookup trong Excel đã được Taimienphi.vn giới thiệu. Hàm Hlookup giúp bạn tìm kiếm và tham chiếu theo cột, hoặc theo dòng, Khi bạn sử dụng hàm này, nếu thỏa mãn điều kiện đặt ra. Bên cạnh hàm tìm kiếm Vlookup trong Excel, bạn có thể tham khảo Hàm COUNTIF trong Excel, Hàm COUNTIF là một hàm đếm khá tiện lợi, với những điều kiện ban đầu, Hàm COUNTIF sẽ cho bạn những kết quả chính xác nhất. Hàm tính toán cũng có khá nhiều hàm khác nhau trong Excel, và Hàm SUMIF là một trong những hàm được nhiều người sử dụng, Hàm SUMIF là hàm tính tổng có điều kiện, giúp bạn tính tổng các cột, các hàng riêng lẻ mà không phải tính tổng tất cả các dữ liệu trong file Excel https://thuthuat.taimienphi.vn/huong-dan-su-dung-ham-vlookup-trong-excel-633n.aspx |