Hàm VLOOKUP trong Excel cực kỳ hữu ích trong việc tra cứu dữ liệu. Dưới đây là những điều bạn cần biết về cách dùng hàm VLOOKUP trong Excel. Show
Bạn muốn kiểm tra một giá trị cụ thể trong bảng tính Excel, đồng thời tra cứu thông tin về nó. Chạy một VLOOKUP giúp bạn tiết kiệm công sức đáng kể trong trường hợp này. Nó là một trong số cách tốt nhất để thực hiện một truy vấn theo chiều dọc trong Microsoft Excel. Bài viết sẽ cho bạn biết ý nghĩa, cách dùng và ví dụ minh họa về hàm VLOOKUP trong Microsoft Excel.
Cách dùng hàm VLOOKUPHàm VLOOKUP là gì?Hàm VLOOKUP là hàm dò tìm dữ liệu trong Excel. Tôi giả định rằng bạn đã có kiến thức nền về Excel và có thể sử dụng những hàm cơ bản như SUM, AVERAGE, và TODAY. Một trong những chức năng thông dụng nhất của VLOOKUP là một hàm dữ liệu, nghĩa là nó sẽ hoạt động dựa trên những bảng cơ sở dữ liệu hoặc đơn giản hơn là danh sách các hạng mục. Danh sách có rất nhiều loại. Bạn có thể lập bảng về nhân sự trong công ty, các loại mặt hàng sản phẩm, danh sách khách hàng, hoặc bất kỳ cái gì cũng được. Và dưới đây là danh sách những sản phẩm của công ty A đang bán trên thị trường:  Bảng cơ sở dữ liệu thường có những dấu hiệu nhận biết cho từng sản phẩm. Ở bảng dữ liệu trên, dấu hiệu nhận biết đặc biệt là cột "Item Code" (Mã sản phẩm). Chú ý: Để có thể sử dụng hàm VLOOKUP thì bảng dữ liệu phải có cột chứa dấu hiệu nhận biết như mã hoặc ID, và buộc phải là cột đầu tiên như bảng trên. VLOOKUP có lẽ là hàm nổi tiếng nhất trong Excel, nhưng vì cả lý do tốt và xấu. Về mặt tốt, hàm VLOOKUP rất dễ sử dụng và làm được điều gì đó rất hữu ích. Đặc biệt, đối với những người dùng mới, việc xem hàm VLOOKUP quét bảng, tìm một kết quả phù hợp và trả về một kết quả chính xác là điều vô cùng thích thú. Sử dụng thành thạo hàm VLOOKUP là một kỹ năng cần thiết, từ người mới bắt đầu đến người dùng Excel thành thạo. Về mặt tiêu cực, hàm VLOOKUP bị giới hạn và có các giá trị mặc định nguy hiểm. Không giống như INDEX và MATCH (hoặc XLOOKUP), hàm VLOOKUP cần một bảng hoàn chỉnh với các giá trị tra cứu trong cột đầu tiên. Điều này làm cho việc sử dụng hàm VLOOKUP với nhiều tiêu chí trở nên khó khăn. Ngoài ra, hành vi đối sánh mặc định của VLOOKUP khiến bạn dễ dàng nhận được kết quả không chính xác. Nhưng đừng quá lo lắng! Chìa khóa để sử dụng thành công hàm VLOOKUP là nắm vững các kiến thức cơ bản. VLOOKUP có 4 đối số: lookup_value, table_array, column_index_num và range_lookup. Lookup_value là giá trị cần tìm và table_array là phạm vi dữ liệu dọc cần tìm bên trong. Cột đầu tiên của table_array phải chứa các giá trị tra cứu để tìm kiếm. Đối số column_index_num là số cột của giá trị cần truy xuất, trong đó cột đầu tiên của table_array là cột 1. Cuối cùng, range_lookup điều khiển hành vi tìm giá trị khớp. Nếu range_lookup là TRUE, thì hàm VLOOKUP sẽ thực hiện đối sánh gần đúng. Nếu range_lookup là FALSE, thì hàm VLOOKUP sẽ thực hiện đối sánh chính xác. Quan trọng, range_lookup là tùy chọn và được đặt mặc định là TRUE, do đó, VLOOKUP sẽ thực hiện so khớp gần đúng theo mặc định. Công thức của hàm VLOOKUP trong ExcelHàm VLOOKUP trong Excel có công thức như sau: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) Trong đó:
Dò tìm tương đối chỉ có thể áp dụng khi giá trị cần dò tìm trong table_array đã được sắp xếp theo thứ tự (tăng dần hoặc giảm dần hay theo bảng chữ cái). Với những bảng như vậy bạn có thể dùng dò tìm tương đối, khi đó nó tương tự như dùng hàm IF vô hạn vậy. Đối với các giá trị cần dò tìm không thể hoặc chưa được sắp xếp hãy dùng dò tìm tuyệt đối để tìm chính xác giá trị. Video hướng dẫn dùng hàm VLOOKUPVD1: Hàm VLOOKUP để đánh giá xếp loại của học sinh, nhân viênGiả sử, bạn có bảng điểm học sinh như sau: STTHọ và tênĐiểm TBXếp loại1Nguyễn Hoàng Anh9.12Trần Vân Anh8.33Nguyễn Quanh Vinh9.54Trần Hồng Quang8.65Đỗ Thanh Hoa5.06Lê Hồng Ngọc4.57Đoàn Thanh Vân7.28Ngô Ngọc Bích0.09Hoàng Thu Thảo6.610Đinh Minh Đức8.7 Và bảng quy định xếp loại như sau: Quy định xếp loại0Yếu5.5Trung bình7Khá8.5Giỏi Giờ ta sẽ sử dụng hàm VLOOKUP để nhập xếp loại cho các học sinh. Bạn để ý thấy rằng bảng quy định xếp loại đã được sắp xếp theo thứ tự từ thấp đến cao (từ yếu đến giỏi) nên trong trường hợp này ta có thể dùng dò tìm tương đối. Trong Excel 2 bảng này được trình bày như sau: Ở đây, giá trị cần dò tìm nằm ở cột C, bắt đầu từ dòng thứ 6. Phạm vi tìm kiếm là $A$18:$B$21, thứ tự cột chứa giá trị dò tìm là 2. Tại ô D6, bạn nhập vào công thức: =VLOOKUP($C6,$A$18:$B$21,2,1). Đây là công thức dò tìm tương đối, bạn có thể thực hiện dò tìm tuyệt đối nếu muốn (hoặc do bảng xếp loại chưa được sắp xếp theo thứ tự) bằng cách thêm 0 vào công thức như thế này: =VLOOKUP($C6,$A$18:$B$21,2,0). Nhấn Enter. Nhấp chuột vào ô D6, xuất hiện ô vuông nhỏ ở góc dưới bên phải, bạn nhấp chuột vào đó và kéo dọc xuống hết bảng để sao chép công thức xếp loại cho những học sinh còn lại. Khi đó, ta có kết quả dùng hàm VLOOKUP để xếp loại học lực học sinh như sau: Bạn có thắc mắc tại sao phải sử dụng $ trước C6 không? $ sẽ giúp cố định cột C, chỉ thay đổi các hàng khi bạn kéo công thức xuống toàn bảng. Còn $A$18:$B$21 để giúp cố định bảng quy định xếp loại, khiến nó không bị thay đổi khi bạn kéo công thức. VD2: Hàm VLOOKUP dò tìm tuyệt đối để lấy dữ liệuGiả sử, bạn có một bảng dữ liệu nhân viên, lưu trữ mã nhân viên, họ tên, chức vụ. Một bảng khác lưu trữ mã nhân viên, quê quán, trình độ học vấn. Giờ bạn muốn điền thông tin quê quán, trình độ học vấn cho từng nhân viên thì phải làm như thế nào? Giả sử bạn có bảng nhân viên và quê quán nhân viên như sau: Giờ bạn muốn điền thông tin quê quán cho nhân viên. Tại ô D4, bạn nhập vào công thức dò tìm tuyệt đối như sau: =Vlookup($A4,$A$16:$C$25,2,0) Rồi nhấn Enter. Nhấp vào ô vuông nhỏ xuất hiện dưới góc ô D4 và kéo xuống toàn bảng để sao chép công thức cho các nhân viên khác. Để điền thông tin trình độ cho các nhân viên, tại ô E4 bạn nhập vào công thức dò tìm tuyệt đối: =VLOOKUP($A4,$A$16:$C$25,3,0) Bạn tiếp tục nhấn Enter và kéo xuống để sao chép công thức cho những nhân viên còn lại, ta sẽ được kết quả như sau: VD3: Dùng VLOOKUP để trích xuất dữ liệuTiếp tục với bộ dữ liệu của ví dụ 2, giờ ta sẽ đi tìm quê quán của 3 nhân viên là Nguyễn Hoàng Anh, Trần Vân Anh và Nguyễn Quang Vinh. Mình đã trích xuất ra thành một bảng mới F15:G18. Việc dò tìm này mình sẽ thực hiện trên bảng A3:E13, sau khi đã được điền quê quán và trình độ. Lúc này, bạn nhập công thức dò tìm tuyệt đối sau vào ô G16: =VLOOKUP($F16,$B$3:$E$13,3,0) > nhấn Enter. Sao chép công thức cho 2 nhân viên còn lại ta được kết quả như sau: Lưu ý trong ví dụ này, giá trị dò tìm nằm ở cột B, vì thế vùng dò tìm ta tính từ cột B mà không tính từ cột A. VD4: Dùng hàm VLOOKUP trên 2 sheet Excel khác nhauQuay lại với bộ dữ liệu ở ví dụ 2 sau khi nhân viên được điền xong trình độ và quê quán, ta đặt tên cho sheet là QTM. Ở một sheet khác của bảng tính, đặt tên là QTM1, bạn cần lấy thông tin về trình độ và chức vụ của nhân viên với thứ tự sắp xếp của nhân viên đã thay đổi. Đây mới là lúc bạn thấy được sức mạnh thực sự của hàm VLOOKUP. Để dò tìm dữ liệu về "Trình độ" của nhân viên, bạn nhập công thức: =VLOOKUP($B4,QTM!$B$3:$E$13,4,0) vào ô C4 của sheet QTM1. Trong đó:
Nhấn Enter, rồi sao chép công thức cho toàn bộ nhân viên còn lại trong bảng, ta được kết quả như sau: Để dò tìm dữ liệu "Chức vụ" của nhân viên, tại ô D4 của sheet QTM1, bạn nhập vào công thức: =VLOOKUP($B4,QTM!$B$3:$E$13,2,0), nhấn Enter. Sao chép công thức cho các nhân viên còn lại, ta được như sau: Sử dụng hàm VLOOKUP để làm gì?Trước tiên chúng ta cần tìm câu trả lời chính xác cho câu hỏi "chúng ta sử dụng hàm VLOOKUP để làm gì?". Hàm VLOOKUP được sử dụng để hỗ trợ tra cứu thông tin trong một trường dữ liệu hoặc danh sách dựa vào những mã định danh có sẵn. Ví dụ, chèn hàm VLOOKUP kèm theo mã sản phẩm vào một bảng tính khác, nó sẽ hiển thị thông tin của sản phẩm tương ứng với mã đó. Những thông tin đó có thể là mô tả, giá thành, số lượng tồn kho, tùy theo nội dung công thức mà bạn viết. Lượng thông tin cần tìm càng nhỏ thì khi viết hàm VLOOKUP sẽ càng khó khăn hơn. Thông thường bạn sẽ sử dụng hàm này vào một bảng tính tái sử dụng như mẫu. Mỗi lần nhập mã sản phẩm thích hợp, hệ thống sẽ truy xuất tất cả những thông tin cần thiết về sản phẩm tương ứng. Những điều cần nhớ về hàm VLOOKUPDưới đây là danh sách những điều quan trọng cần nhớ về hàm VLOOKUP trong Excel:
Sự khác biệt chính giữa hàm VLOOKUP và XLOOKUP trong ExcelVLOOKUP XLOOKUP Mặc định kết hợp chính xác N Y Trả về giá trị ở bên phải dữ liệu tra cứu Y Y Trả về giá trị ở bên trái của dữ liệu tra cứu N Y Trả về kết quả nhiều hơn một giá trị N Y Cột tra cứu cần được phân loại Chỉ kết hợp gần đúng Chỉ tìm nhị phân Tìm kiếm từ trên xuống dưới Y Y Tim từ dưới lên trên N Y Tìm kiếm nhị phân N Y Tùy chỉnh thông báo không tìm thấy giá trị tra cứu N Y Tìm ký tự đại diện Y Y Tìm kiếm chính xác Y Y Tìm kiếm gần đúng trả về giá trị nhỏ hơn tiếp theo Y Y Tìm kiếm gần đúng trả về giá trị lớn hơn tiếp theo N Y Có thể trả về một giá trị sai nếu không phân loại giá trị tra cứu Y - Gần đúng N - Gần đúng, Y - Nhị Phân Thêm cột mới để tìm bảng có thể phá vỡ công thức Y N Y - Có N - Không Xem thêm:
Sau khi biết cách dùng VLookup, bạn cũng cần biết những lỗi phổ biến và cách xử lý vấn đề. Những lỗi thường gặp khi dùng hàm VLOOKUP trong ExcelGiá trị tra cứu nằm sai cộtMột trong số lỗi phổ biến nhất mà bạn sẽ gặp khi dùng công thức VLOOKUP là hàm trả về chỉ giá trị N/A. Lỗi này xảy ra khi nó không thể tìm thấy giá trị tra cứu bạn đã yêu cầu VLOOKUP tìm kiếm.Trong một số trường hợp, bạn có thể dùng hàm XLOOKUP thay cho VLOOKUP, tuy nhiên, chỉ dễ sửa lỗi này trong hàm VLOOKUP. Cách khắc phục vấn đề: Lí do khiến VLOOKUP không tìm thấy giá trị mong muốn có thể do giá trị này không nằm trong bảng. Tuy nhiên, nếu nó trả về tất cả giá trị N/A, giá trị tra cứu là văn bản, thì rất có thể đó là vì nó chỉ có thể đọc từ trái sang phải.Khi tạo bảng trong Excel cho VLOOKUP, đảm bảo đặt giá trị tra cứu ở bên trái của giá trị bạn muốn trả về. Cách dễ nhất để làm việc này là đặt nó ở cột đầu tiên trong bảng. Giờ công thức sẽ trả về một giá trị: Nhìn chung, VLOOKUP là cách tuyệt vời để truy vấn dữ liệu nhanh hơn trong Microsoft Excel. Dù các truy vấn VLOOKUP theo chiều dọc trên cột và có một chút hạn chế khác, Microsoft vẫn liên tục cập nhật các tính năng tra cứu trên Excel để mở rộng tính ứng dụng của chúng. Ví dụ, HLOOKUP, XLOOKUP... có thể giúp bạn tra cứu dữ liệu theo nhiều hướng khác nhau. QuanTriMang.com sẽ tiếp tục cung cấp cho bạn thông tin về cách dùng hàm này ở những bài sau. |
