Bài viết này chúng ta sẽ tiếp tục tìm hiểu những điều thú vị về hàm VLOOKUP trong Excel các bạn nhé.
11. Với tham chiếu tuyệt đối có thể sao chép công thức VLOOKUP
Nếu chúng ta cần lấy thông tin từ nhiều hơn một cột trong một bảng, chúng ta có thể sử dụng Fill handle để Copy - Paste VLOOKUP. Thế nhưng muốn sao chép công thức VLOOKUP chúng cần sử cần sử dụng tham chiếu tuyệt đối cho các giá trị tra cứu (lookup_value) và bảng dò tìm (table_array).
Ví dụ bởi vì giá trị tra cứu và bảng dò tìm là tuyệt đối dưới đây chúng ta có thể sao chép công thức trên các cột, sau đó chỉ cần trở lại và thay đổi các chỉ số cột khi cần thiết để dò tìm kết quả khác.
Nếu vẫn chưa rành về cách viết hàm VLOOKUP xem tại đây: Cách sử dụng hàm VLOOKUP
12. Đăt Tên vùng (Define name) làm cho hàm VLOOKUP trở nên dễ đọc và dễ di chuyển hơn
Địa chỉ của vùng tuyệt đối ở trên trông khá xấu xí và làm cho công thức VLOOKUP trở nên khó đọc hơn. Do đó, để làm cho công thức VLOOKUP sạch và dễ dàng đọc hơn, các bạn thay thế các tham chiếu tuyệt đối bằng các Tên (Name) vùng. Trong ví dụ dữ liệu nhân viên ở trên, chúng ta có thể đặt tên cho ô nhập liệu B3 là "MaNV" và sau đó đặt tên cho bảng dò tìm là "data", công thức của bạn sẽ được viết như sau:
Không chỉ dễ đọc hơn, mà công thức này còn dễ di chuyển hay copy hơn nữa, vì đơn giản tên vùng là tuyệt đối
13. Chèn 1 cột có thể phá vỡ công thức VLOOKUP hiện có
Nếu các bạn có công thức VLOOKUP đang có trong một bảng tính, công thức này có thể sẽ bị phá vỡ nếu bạn chèn thêm 1 cột trong bảng. Đó là bởi vì giá trị Chỉ số cột (column_index) trong công thức VLOOKUP không thể thay đổi tự động khi các cột được chèn hoặc xóa.
Ví dụ dưới đây, công thức VLOOKUP để tra cứu Xếp hạng và Lượng bán hàng đã bị phá vỡ hởi vì có cột mới được chèn vào giữa cột Năm và Xếp hạng.
Để tránh vấn đề này, chúng ta có thể áp dụng cách tính toán Chỉ số cột (column_index) được nhắc tới ngay bên dưới
14. Sử dụng ROW hoặc COLUMN để tính toán Chỉ số cột
Để tránh cho việc hàm VLOOPUP bị phá vỡ khi chèn thêm cột hoặc sự thay đổi sau khi sao chép một công thức chúng ta có thể sử dụng một trong hai hàm ROW, hoặc hàm COLUMN để tạo ra các chỉ số cột năng động. Chỉ cần có dữ liệu từ các cột liên tiếp, thủ thuật này cho phép chúng ta thiết lập một công thức VLOOKUP, sau đó sao chép qua mà không sợ có bất kì sự thay đổi nào.
Ví dụ, với dữ liệu nhân viên bên dưới, chúng ta có thể sử dụng hàm COLUMN để tạo ra Chỉ số cột năng động. Đối với công thức VLOOUP đầu tiên ở ô C3, COLUMN tự chính nó sẽ trả kết quả là 3 (bởi vì cột C là cột thứ ba trong bảng tính) vì vậy chỉ cần trừ đi 1, và sao chép công thức qua các ô còn lại là được.
Tất cả các công thức sẽ giống hệt nhau và không cần phải chỉnh sửa. Công thức được sử dụng là: =VLOOKUP(MaNV,data,COLUMN()-1,0)
15. Sử dụng VLOOKUP + MATCH cho Chỉ số cột năng động đầy đủ
Chúng ta có thể sử dụng MATCH để tìm kiếm vị trí của một cột trong một bảng và trả kết quả là chỉ số cột năng động. Điều này đôi lúc được gọi là một tra cứu hai chiều kể từ khi bạn đang tìm kiếm cả dòng và cột. Chúng ta có thế áp dụng thủ thuật này để tìm kiếm lượng bán hàng của 1 nhân viên bán hàng trong một tháng cụ thể, hoặc tìm kiếm giá của một sản phẩm nào đó từ nhà cung cấp cụ thể.
Xem ví dụ dưới đây, giả sử chúng ta có bảng doanh số bán hàng mỗi tháng, chia ra bởi nhân viên bán hàng:
VLOOKUP có thể dễ dàng tìm thấy tên người bán hàng, nhưng lại không có cách nào để xử lý Tên tháng cách tự động. Bí quyết chính là sử dụng hàm MATCH ở vị trí Chỉ số cột (column_index).
Hàm MATCH sẽ cho kết quả là số thứ tự của cột cần lấy giá trị tra cứu, gồm tất cả các cột trong bảng để "đồng bộ" với Chỉ số cột được sử dụng trong hàm VLOOKUP =VLOOKUP(H2,data,MATCH(H3,thang,0),0) Lưu ý : bạn nên thường xuyên sử dụng 2 cách tra cứu với hàm INDEX và hàm MATCH. 2 cách đều linh động và hiệu suất tốt hơn nếu làm việc trên các tập dữ liệu lớn.
16. VLOOKUP cho phép tìm kiếm dựa trên ký tự đại diện
Bất cứ lúc nào bạn đang sử dụng VLOOKUP với kiểu tìm kiếm chính xác, chúng ta cũng được tùy chọn sử dụng các ký tự đại diện trong các giá trị tra cứu. Những ký tự đại diện cho phép người sử dụng làm một cuộc tìm kiếm chính xác, dù chỉ khớp 1 phần. Excel cung cấp hai ký tự đại diện: dấu sao (*) nếu muốn khớp với 1 hoặc nhiều ký tự, và dấu hỏi (?) nếu muốn khớp với 1 ký tự. Bạn có thể gõ dấu trực tiếp vào 1 ô và xem nó như là một giá trị tra cứu với VLOOKUP. Ví dụ, trong hình dưới đây, nhập "Kha*" vào H3, Name cho H3 là "GiaTri". VLOOKUP sẽ chạy ra kết quả là "Khanh".
Công thức trong trường hợp trên rất đơn giản: =VLOOKUP(GiaTri,data,1,0). Nếu bạn thích, bạn có thể điều chỉnh công thức VLOOKUP để sử dụng ký tự đại diện một cách chuyên nghiệp như ví dụ dưới đây, chỉ đơn giản là ghép các giá trị trong H3 với một dấu sao.
Trong trường hợp này, công thức VLOOKUP để nổi dấu sao (*) với giá trị tra cứu là: =VLOOKUP(GiaTri&"*",data,1,0) Lưu ý: Hãy cẩn thận với các ký tự đại diện và VLOOKUP. Bạn sẽ trở nên lười biếng hơn với thủ thuật tiện lợi này cũng như cách làm này dễ dàng cung cấp cho bạn một kết quả sai, không như mong đợi.
17. Thay thế lỗi #N/A và hiển thị một thông điệp thân thiện
Khi chúng ta sử dụng hàm VLOOKUP để tìm kiếm chính xác nếu không có kết quả trả về Excel sẽ hiển thị lỗi #N/A. Lỗi này rất hữu ích vì VLOOKUP cho chúng ta biết rõ ràng rằng không có kết quả tra cứu trong bảng dò tìm. Tuy nhiên, lỗi #N/A lại không hề thú vị khi nhìn vào bảng tính. Thật may mắn chúng ta có thể bắt lỗi này và hiển thị thông báo khác để thay thế. Trong ví dụ dưới đây , "Cà phê" không tồn tại như một thức uống trong bảng, do VLOOKUP đưa ra lỗi #N/A
Có thể thấy lỗi #N/A rất ngứa mắt nếu bạn muốn hiển thị một thông điệp thân thiện hơn. Cách đơn giản nhất là bọc hàm VLOOKUP trong hàm IFERROR. Hàm IFERROR cho phép người dùng "bắt" bất kỳ lỗi nào và trả về một kết quả mà bạn muốn. Để bắt lỗi #N/A trong ví dụ trên và hiển thị thông điệp "Không xác định" chúng ta sử dụng công thức VLOOKUP bên trong hàm IFERROR và thiết lập kết quả mà bạn muốn:
=IFERROR(VLOOKUP(E6,data,2,0),"Không xác định")
Nếu giá trị tra cứu được tìm thấy, không có lỗi xảy ra, hàm VLOOKUP sẽ trả về kết quả bình thường.
18. Số ở dạng Text có thể gây ra lỗi tìm kiếm
Việc nhập liệu sau định dạng trong Excel đôi lúc cũng gây lỗi khi sử dụng VLOOKUP. Lỗi thường gặp nhất đó là các con số được nhập dưới dạng Text. Nhiều khi nhập cứ nhập đôi lúc chúng ta quên chỉnh sửa lỗi này. Do đó khi sử dụng VLOOKUP trong trường hợp này chúng ta sẽ nhận được kết quả là lỗi #N/A nếu giá trị tra cứu (lookup_value)không phải là dạng Text.
Ví dụ sau đây, số ID trong bảng Hành Tinh được nhập dưới dạng Text, đó là nguyên nhân dẫn đến khi sử dụng VLOOKUP trả về lỗi #N/A khi giá trị tra cứu (lookup_value) ở ô H3 được gõ vào là số 3:
Để giải quyết vấn đề này, đơn giản chỉ cần bạn chắc chắn rằng giá trị tra cứu (lookup_value) và cột đầu tiên trong bảng dò tìm cùng kiểu dữ liệu (hoặc cả hai là số, hoặc cả hai là Text). Một cách đơn giản để làm điều này là chuyển đổi các giá trị trong cột tìm kiếm thành dạng Number bằng cách thêm số 0 khi sử dụng tính năng Paste Special. Nếu không kiểm soát được bảng dò tìm, chúng ta có thể điều chỉnh công thức VLOOKUP để chuyển đổi các giá trị tra cứu (lookup_value) thành dạng Text, bằng cách ghép "" vào công thức như sau : =VLOOKUP(id&"",hanh_tinh,2,0)
Cách khác nếu không chắc chắn dữ liệu trong bảng dò tìm đang ở dạng Number hay Text hãy bọc hàm VLOOKUP trong hàm IFERROR và viết một công thức để xử lý cả hai trường hợp trên =IFERROR(VLOOKUP(id,hanh_tinh,3,0),VLOOKUP(id&"",hanh_tinh,3,0))
19. Sử dụng VLOOKUP để thay thế các hàm IF lồng nhau
Một trong những điều khá vụ vị của VLOOKUP đó là có thể dùng để thay thế các hàm IF lồng nhau. Nếu bạn đã từng làm việc với công thức bao gồm nhiều hàm IF lồng nhau bạn sẽ thấy sự bất lợi của các hàm IF lồng nhau này là quá dài và quá nhiều ngoặc đơn. Chỉ cần 1 lỗi logic trong cách viết ngoặc thì khả năng hàm cho kết quả sai là bình thường. Một ứng dụng cần dùng đến các hàm IF lồng nhau đó là tính điểm chữ dựa trên số điểm của các em học sinh. Trong ví dụ dưới đây, bạn sẽ thấy một công thức đã được xây dựng với nhiều hàm IF lồng nhau để tính điểm chữ dựa trên QUY TẮC TÍNH ĐIỂM CHỮ ở bên phải bảng dữ liệu.
Công thức sử dụng các hàm IF lồng nhau để tính điểm chữ trong ví dụ trên như sau: =IF(C5<64,"F",IF(C5<73,"D",IF(C5<85,"C",IF(C5<95,"B","A"))))
Ngược lại nếu bạn sử dụng VLOOKUP để ghép các lớp với nhau chỉ với một công thức đơn giản. Tất cả bạn cần làm là đảm bảo bảng QUY TẮC TÍNH ĐIỂM CHỮ được thiết lập để dùng cho VLOOKUP (có nghĩa là nó phải được sắp xếp từ nhỏ đến lớn theo điểm số, và được chứa trong khung để xử lý tất cả các điểm số).
Sau khi đặt Name vùng cho bảng QUY TẮC TÍNH ĐIỂM CHỮ là quy_tac , công thức VLOOKUP được dùng như sau để thay thế các hàm IF lồng nhau.: =VLOOKUP(C5,quy_tac,2,TRUE)
Có thể thấy rằng cả logic và giá trị điểm số không được đưa ngay vào công thức, mà qua các ô và bảng. Vì thế, nếu có bất cứ điều gì thay đổi, nào chỉ cần cập nhật trực tiếp trong bảng, và các công thức VLOOKUP sẽ tự động cập nhật.
20. VLOOKUP chỉ có thể xử lý 1 điều kiện
Mặc định VLOOKUP chỉ có thể tìm thấy kết quả dựa trên 1 điều kiện duy nhất mà thôi. Điều này có nghĩa là chúng ta không thể dễ dàng làm những việc như tìm kiếm 1 nhân viên tên "Khanh" trong Bộ phận "kế toán", hoặc tìm kiếm 1 nhân viên dựa trên tên và họ trong 2 cột riêng biệt được.
Tuy nhiên, vẫn có cách để khắc phục những hạn chế này đó là tạo ra 1 cột phụ để móc nối các giá trị từ các cột khác với nhau để tạo ra các giá trị tra cứu. Ví dụ, ở đây muốn tìm Bộ phận và Nhóm cho một nhân viên, nhưng tên và họ lại xuất hiện trong 2 cột riêng biệt thì phải làm thế nào?
Đầu tiên chúng ta thêm một cột phụ với chức năng đơn giản là ghép tên à họ của nhân viên:
Sau đó, xây dựng công thức VLOOKUP sử dụng bảng dò tìm bao gồm cột phụ này, và nhập Giá trị tra cứu là tên và họ của nhân viên:
Sử dụng công thức VLOOKUP để tìm kiếm tên và họ với nhau có sử dụng cột phụ mới thêm vào như sau: =VLOOKUP(D3&C3,data,4,0)
21. Sử dụng 2 VLOOKUP thì nhanh hơn 1
Nghe hơi ảo phải không nào thế nhưng đây hoàn toàn là sự thật đấy các bạn ạ. Nếu bạn có một bảng dữ liệu với số lượng dữ liệu lớn và muốn dò tìm kết quả chính xác, bạn nên nghĩ đến điều này. VLOOKUP sẽ được tăng tốc độ khi bạn thêm 1 VLOOKUP nữa vào công thức.
Cùng phân tích lý do nhé: Hãy tưởng tượng, chúng ta có rất nhiều dữ liệu theo đơn đặt hàng, hơn 10.000 đơn và bạn đang sử dụng VLOOKUP để tra cứu tổng số đơn đặt hàng dựa trên Mã đơn hàng. Giả sử, công thức của chúng ta như thế này: =VLOOKUP(ma_donhang, data, 5, FALSE) Chữ FALSE ở cuối công thức để buộc VLOOKUP tìm kiếm chính xác. Tại sao phải tìm kiếm chính xác? Bởi vì đây là dịp để một vài số đơn hàng sẽ không được tìm thấy. Trong trường hợp này, kiểu tìm kiếm chính xác sẽ làm cho VLOOKUP trả kết quả là lỗi #N/A
Vấn đề phát sinh ở đây là kiểu tìm kiếm chính xác sẽ làm VLOOKUP chạy rất chậm, vì Excel phải chạy tuyến tính qua tất cả các giá trị cho đến khi nó tìm thấy kết quả tương ứng. Ngược lại, kiểu tìm kiếm tương đối thì sẽ hơn vì Excel có thể làm những gì gọi là tìm kiếm nhị phân. Thế nhưng nếu sử dụng kiểu tìm kiếm tương đối là VLOOKUP thì có thể trả lại kết quả sai khi một giá trị không được tìm thấy. Tệ hơn nữa, kết quả đó có thể trông hoàn toàn bình thường, do đó, rất khó để phát hiện sai sót.
Vì vậy giải pháp ở đây là sử dụng VLOOKUP 2 lần, cả 2 lần tìm kiếm tương đối.
- VLOOKUP đầu tiên chỉ đơn giản là kiểm tra rằng giá trị thực sự tồn tại.
- VLOOKUP khác đang chạy (cũng trong kiểu tìm kiếm tương đối) để lấy dữ liệu mà bạn muốn. Nếu không, bạn có thể trả lại bất kỳ giá trị mà bạn muốn, ví dụ như "Không tìm thấy".
Công thức cuối cùng sẽ như thế này : =IF(VLOOKUP(ma_donhang, data,1,TRUE)=ma_donhang, VLOOKUP(ma_donhang, data,5,TRUE), "Không tìm thấy")
22. Đồng INDEX + MATCH > VLOOKUP và hơn thế nữa
Cuộc tranh luận không hồi kết trong các diễn đàn Excel về việc so sánh tốc độ và hiệu quả của VLOOKUP với hàm INDEX + MATCH. 1 số cho rằng INDEX + MATCH có thể làm tất cả mọi thứ mà VLOOKUP (và cả HLOOKUP) có thể làm, với nhiều sức mạnh hơn, tốc độ nhanh hơn và xử lý linh hoạt hơn tuy có hơi phức tạp hơn chút xíu. Số còn lại cho rằng sử dụng 2 hàm thay vì một nên độ phức tạp tăng lên gây khó dễ cho người mới tìm hiểu. Thực tế sử dụng hàm nào là lựa chọn của bạn rành hàm nào hơn thì sử dụng hàm đó.
Lời kết.
Ai cần có VLOOKUP, ai khó cũng có VLOOKUP, nói gì thì nói VLOOKUP vẫn là một trong các hàm thường dùng và quan trọng nhất trong Excel. Hi vọng 22 điều thú vị về hàm VLOOKUP trong Excel có thể bạn chưa biết này sẽ giúp ích cho các bạn.
