BÀI TẬP EXCEL VỚI CÁCH SỬ DỤNG HÀM VLOOKUP VÀ ỨNG DỤNG NÂNG CAO THƯỜNG GẶP (PHẦN 2)

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é!

Đặt tên cho vùng dữ liệu, dải ô hay bảng dữ liệu trong công thức hàm VLOOKUP

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)

Sử dụng ký tự đại diện trong công thức hàm VLOOKUP

Có một số kỹ tự có thể đại diện trong hàm VLOOKUP

• Dấu chấm hỏi (?) để khớp với bất cứ ký tự riêng lẻ nào, và
• Dấu sao (*) để khớp với bất kỳ dãy ký tự nào.
   

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:

1. Khi bạn không nhớ chính xác chuỗi ký tự bạn đang tìm kiếm.
2. Khi bạn muốn tìm một từ nào đó. Từ đó là một phần của nội dung ô. Hãy chú ý rằng hàm VLOOKUP tìm kiếm toàn bộ nội dung ô.
3. Khi cột cần tìm có khoảng trống thụt đầu dòng hay cuối dòng. Nếu gặp trường hợp này, thì bạn có thể vắt óc cố tìm ra lý do tại sao công thức thông thường của bạn lại không được thực hiện.

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

Ví dụ 1: Tìm kiếm với hàm VLOOKUP bằng một số ký tự bắt đầu hay kết thúc của từ khóa cần tìm

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:

• =VLOOKUP(“*man”,$A$2:$C$11,1,FALSE) – tìm tên kết thúc bằng “man”.
• =VLOOKUP(“ad*son”,$A$2:$C$11,1,FALSE) – tìm tên bắt đầu bằng “ad” và kết thúc bằng “son”.
• =VLOOKUP(“?????”,$A$2:$C$11,1,FALSE) – tìm họ có 5 ký tự.

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ề.

Ví dụ 2: Tìm kiếm với hàm VLOOKUP bằng ký tự đại diện dựa trên giá trị ô

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é).

Giá trị tìm kiếm chính xác và tương đối trong hàm VLOOKUP

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é:

1. Nếu range_lookup là FALSE, thì công thức sẽ tìm giá trị chính xác. Ví dụ, công thức sẽ tìm chính xác giá trị cần tìm khi bạn nhập giá trị đó làm tham số thứ nhất (lookup_value).

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

Ví dụ 1: Dùng VLOOKUP với kết quả tìm kiếm tuyệt đối

Để 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.

Ví dụ 2: Dùng hàm VLOOKUP với kết quả tìm kiếm tương đối

Đ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

Tổng kết

1. Hàm VLOOKUP không thể tìm ở bên trái. Nó luôn luôn tìm giá trị nằm trong cột cận trái của bảng dữ liệu cần tìm (table_array).
2. Trong công thức hàm VLOOKUP, tất cả giá trị đều không phân biệt dạng chữ, có nghĩa là ký tự viết HOA và viết thường đều được xử lý như nhau.
    
3. Nếu giá trị cần tìm nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của dải ô cần tìm, thì hàm VLOOKUP sẽ trả về lỗi #N/A.
    
4. Nếu câu lệnh thứ ba (col_index_num) nhỏ hơn 1, thì công thức hàm VLOOKUP sẽ trả về lỗi #VALUE!. Trong trường hợp, nó lớn hơn số cột trong dải ô cần tìm (table_array), thì công thức sẽ trả về lỗi #REF!.
    
5. Hãy sử dụng tham chiếu ô tuyệt đối trong tham số table_array của công thức hàm VLOOKUP để có đúng dải ô cần tìm khi xử lý công thức. Hãy cân nhắc việc sử dụng tên cho vùng dữ liệu hay cho bảng trong Excel như một phương án thay thế.
    
6. Khi tìm kiếm kết quả tương đối (range_lookup được cải đặt thành TRUE hay bỏ trống), hãy luôn lọc dữ liệu ở cột đầu tiên trong dải ô cần tìm theo thứ tự tăng dần.
    
7. Và cuối cùng, hãy nhớ tầm quan trọng của tham số cuối cùng (thứ 4). Sử dụng TRUE hay FALSE khi thích hợp và bạn sẽ không phải đau đầu nữa.

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é.