Nội dung chính
Trong phần 1 của bài viết, Gitiho đã giới thiệu cho bạn đọc những điều căn bản về cách sử dụng hàm VLOOKUP. Trong phần 2 này, hãy cùng Gitiho làm sáng tỏ những khía cạnh khác, nâng cao hơn về hàm này nhé!
Thông thường khi hàm VLOOKUP yêu cầu nhập table_array, thì bạn sẽ bôi đen bảng hoặc dãy chứa dữ liệu đó. Nhưng nếu như bạn chỉ cần nhập tên bảng hoặc dãy dự liệu đó thì sao, có vẻ đơn giản hơn nhỉ? Hãy cùng thử nhé.
Để tạo tên cho vùng dữ liệu, bạn chỉ cần chọn dãy ô chứa dữ liệu đó và gõ bất kỳ tên nào vào Name box (Hộp tên) – ở bên trái thanh Công thức như hình dưới đây:
Khi đó, công thức với hàm VLOOKUP có thể nhập như sau:
=VLOOKUP(“Product 1”,Products,2)
Hầu hết tên vùng dữ liệu trong Excel đều được sử dụng cho toàn bộ trang tính, nên bạn không cần định rõ tên của trang tính trong câu lệnh table_array, dù cho dải ô bạn cần tìm nằm ở trang tính khác. Nếu nó nằm ở sổ làm việc khác (file khác), thì bạn phải đặt tên file đó phía trước tên vùng dữ liệu đã đặt tên, ví dụ:
=VLOOKUP(“Product 1”, PriceList.xlsx!Products,2)
Bên cạnh đó, việc sử dụng tên cho vùng dữ liệu là một sự thay thế tuyệt vời cho tham chiếu ô tuyệt đối. Bởi vì tên vùng dữ liệu không đổi khi sao chép công thức sang các ô khác, nên bạn có thể chắc rằng bạn sẽ luôn tìm đúng vùng dữ liệu cần tìm.
Nếu bạn đã đổi vùng dữ liệu thành bảng với đầy đủ tính năng (dùng tab Insert > Table) thì bạn có thể dùng chuột chọn vùng dữ liệu đó, và Excel sẽ tự động thêm tên các cột (hay tên bảng trong trường hợp bạn đã chọn toàn bộ bảng) vào công thức:
=VLOOKUP(“Product 1”, Table46[[Product]:[Price]],2)
hay thậm chí là có dạng như thế này:
=VLOOKUP(“Product 1”, Table46,2)
Có một số kỹ tự có thể đại diện trong hàm VLOOKUP
Việc sử dụng ký tự đại diện trong công thức hàm VLOOKUP có thể rất hữu ích trong nhiều trường hợp:
Xem thêm: Hướng dẫn giải bài tập Excel có dùng hàmVLOOKUP, hàm IF, hàm SUM
Giả sử bạn phải tìm tên của 1 khách hàng trong khi bạn không nhớ đầy đủ tên của anh ta, bạn chỉ nhớ tên anh ta bắng đầu bằng vài ký tự như ''ack''. Việc còn lại, hãy để hàm VLOOKUP lo!
Sử dụng công thức sau để tìm kiếm:
=VLOOKUP(“ack*”,$A$2:$C$11,1,FALSE)
Sau khi bạn thấy kết quả là tên bạn tìm kiếm, bạn có thể sử dụng công thức hàm VLOOKUP tương tự để tìm số tiền người khách hàng ấy đã trả. Bạn chỉ cần thay đổi thông số thứ ba trong công thức thành số thứ cột mà bạn cần tìm, đó là cột C trong trường hợp này:
=VLOOKUP(“ack*”,$A$2:$C$11,3,FALSE)
Sau đây là thêm vài ví dụ về việc sử dụng hàm VLOOKUP bằng ký tự bắt đầu và kết thúc:
Lưu ý: Để công thức hàm VLOOKUP có ký tự đại diện chạy chính xác, bạn phải luôn thêm FALSE là tham số cuối cùng (như đã phân tích ở trên). Nếu dải ô cần tìm của bạn có hơn một mục nhập khớp với tiêu chuẩn đại diện, thì giá trị tìm thấy đầu tiên sẽ được trả về.
Làm thế nào để tìm kiếm giá trị ở 1 ô nào đó? Giả sử, bạn có mã bản quyền ở cột A (ví dụ là 3MLHK-ABCD-7ERQV) và tên bản quyền ở cột B (là Jeremy Hill), và 1 vài ký tự của mã bản quyền đó ở ô C1 (ví dụ là ABCD)
Bạn có thể dùng hàm VLOOKUP để tìm kiếm như sau:
=VLOOKUP(“*”&C1&”*”, $A$2:$B$12, 2, FALSE)
Công thức này tìm kiếm bất kỳ mã bản quyền nào có chứa ký tự như ở ô C1 (là ABCD) và sau đó trả kết quả cột B tương ứng. Xin hãy chú ý rằng chúng ta sử dụng dấu và (&) trước và sau một tham chiếu ô trong thông số đầu tiên nhằm nối chuỗi ký tự.
Như bạn có thể thấy trong ảnh chụp màn hình bên dưới, hàm VLOOKUP của tôi trả về “Jeremy Hill” bởi vì mã bản quyền của anh ấy có một nhóm ký tự nằm trong ô C1:
Lưu ý: Bảng dữ liệu (table_array) chính là Table 7 trong hình trên, vùng dữ liệu này đã được đặt tên theo cách làm ở mục trên (hãy đọc lại nếu chưa biết làm nhé).
Như phân tích ở Phần 1 của bài viết, yếu tố tùy chọn cuối cùng là range_lookup. Bạn có thể nhận được kết quả khác nhau tùy vào lựa chọn nhập TRUE hay FALSE.
Đầu tiên, hãy xem ý nghĩa của chúng nhé:
Nếu có 2 hay nhiều hơn 2 giá trị từ cột đầu tiên của bảng dữ liệu (table_array) khớp với giá trị cần tìm, thì kết quả tìm thấy đầu tiên sẽ được trả về.
Nếu không thể tìm thấy giá trị tìm kiếm chính xác, thì lỗi #N/A sẽ được trả về.
Ví dụ, nếu bạn sử dụng công thức =VLOOKUP(4, A2:B15,2,FALSE), nhưng dữ liệu của bạn không chứa giá trị 4 từ ô A2 đến cột A15, thì công thức sẽ trả về lỗi #N/A.
2. Nếu range_lookup là TRUE hay bỏ trống, thì công thức sẽ tìm kết quả tương đối. Nói cách khác, công thức hàm VLOOKUP của bạn sẽ tìm kiếm kết quả tuyệt đối đầu tiên và nếu không được tìm thấy, thì nó sẽ trả về kết quả tương đối. Giá trị tìm kiếm tương đối là giá trị lớn nhất tiếp theo, chỉ nhỏ hơn lookup_value.
Nếu bạn nhập TRUE hay bỏ trống tham số này, thì các giá trị ở cột đầu tiên của bảng dữ liệu cần tìm phải được sắp xếp theo thứ tự TĂNG DẦN, đó là từ nhỏ nhất đến lớn nhất. Nếu không thì, hàm VLOOKUP trong Excel có thể sẽ không tìm được giá trị chính xác.
Hãy xem các ví dụ dưới đây để hiểu rõ hơn nhé.
Xem thêm: Bộ đề thi, bài tập Excel có đáp án về hàm trong Excel quan trọng
Để phục vụ mục đích này, bạn cần đặt FALSE làm câu lệnh cuối cùng trong công thức.
Thử làm với bảng “Animal speed” để làm ví dụ đầu tiên và tìm ra loài vật nào có thể chạy 50mph xem nhé. Bạn có công thức dưới đây:
=VLOOKUP(50, $A$2:$B$15, 2, FALSE)
Lưu ý: Bảng dữ liệu chứa 2 ô có giá trị 50 là A5 và A6 nhưng công thức chỉ cho kết quả là ô A5? Vì sao? Vì công thức sẽ trả kết quả tuyệt đối chính là đầu tiên tìm thấy, khớp với giá trị cần tìm.
Điều đầu tiên bạn cần làm đó là lọc cột đầu tiên trong bảng dữ liệu tham chiếu theo thứ tự tăng dần. Sau đó, bạn cần nhập TRUE hoặc bỏ qua tham số cuối cùng trong công thức hàm. Lý do của việc này đã được Gitiho giải thích cặn kẽ ở phần lý thuyết của mục này.
Bây giờ, bạn biết phải viết công thức thế nào chứ?
=VLOOKUP(69, $A$2:$B$15, 2, TRUE)
hay
=VLOOKUP(69,$A$2:$B$15,2)
Cả 2 công thức đều đúng.
Dưới đây là 1 ví dụ minh họa với yêu cầu ''Tìm loài vật có tốc độ chạy gần 60mph nhất", và ta có kết quả như sau:
Ở ví dụ trên, có lẽ bạn sẽ thắc mắc, 70 gần với 69 hơn, tại sao kết quả lại trả về "Antelope" với tốc độ 61?, Bởi vì với hàm VLOOKUP trả về giá trị tương đối (mà không phải tuyệt đối) kết quả hiển thị sẽ trả về giá trị lớn nhất tiếp theo, và giá trị này nhỏ hơn giá trị cần tìm.
Xem thêm: Bài tập hàm IF nhiều điều kiện kèm hướng dẫn giải chi tiết
Hy vọng qua bài viết trên, bạn đọc đã nắm kỹ các thông tin và ứng dụng của hàm VLOOKUP để tra cứu và tìm kiếm trong Excel. Hãy luyện tập thêm các bài tập Excel kèm đáp án mà Gitiho đã thiết kế nhắm giúp bạn đọc thành thạo các kĩ năng liên quan đến hàm VLOOKUP này.
Chúc bạn học tốt! Đừng quên rằng chúng mình có những khóa học vô cùng hiệu quả và bổ ích về tin học văn phòng nhé.