Hàm VLOOKUP trong Excel luôn là lựa chọn lý tưởng trong trường hợp nếu người dùng muốn lấy thông tin từ một bảng tính nào đó. Phần 1 và Phần 2 của 23 điều về hàm VLOOKUP ai cũng nên biết đã được Taimienphi.vn giới thiệu, và dưới đây là 23 điều về hàm VLOOKUP ai cũng nên biết (Phần 3) , phần cuối cùng cung cấp cho bạn nhưungx kiến thức cần thiết của hàm này.
- 23 điều về hàm Vlookup phần 1
- 23 điều về hàm Vlookup phần 2
15. Sử dụng hàm ROW hoặc hàm COLUMN để tính toán chỉ số cột
Nếu cảm thấy khó chịu sau khi thay đổi sao chép một công thức nào đó, bạn có thể sử dụng hàm ROW hoặc hàm COLUMN để tạo các chỉ số cột độ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 bạn thiết lập một công thức VLOOKUP, sau đó sao chép mà không có bất kì sự thay đổi nào.
Ví dụ, với dữ liệu nhân viên bên dưới, bạn có thể sử dụng hàm COLUMN để tạo ra chỉ số cột động. Với công thức đầu tiên ở ô C3, COLUMN 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 :
Tất cả các công thức đều giống nhau và không yêu cầu chỉnh sửa.
Công thức mà bạn đang sử dụng là:
=VLOOKUP(id,data,COLUMN()-1,0)
16. Sử dụng VLOOKUP + MATCH cho chỉ số cột động đầy đủ
Ngoài ra bổ sung thêm 1 bước cho thủ thuật ở trên, đó là bạn có thể sử dụng hàm MATCH để tìm kiếm vị trí của một cột trong một bảng và trả về kết quả là chỉ số cột động đầy đủ.
Điều này còn được gọi là tra cứu hai chiều vì bạn đang tìm kiếm cả dòng và cột.
Bạn có thế áp dụng thủ thuật này để tìm kiếm doanh thu bán hàng của 1 nhân viên trong 1 tháng cụ thể, hoặc tìm kiếm giá 1 sản phẩm nào đó từ 1 nhà cung cấp cụ thể.
Cho ví dụ, giả sử doanh thu bán hàng của bạn được chia bởi nhân viên bán hàng:
Một điều về hàm VLOOKUP ai cũng nên biết khác VLOOKUP có thể dễ dàng tìm thấy tên người bán hàng, nhưng không có cách nào để xử lý tên tháng tự động. Cách giải quyết ở đây là sử dụng hàm MATCH để thay thế chỉ số cột tĩnh.
Hàm MATCH sẽ cho kết quả là số thứ tự của cột cần lấy giá trị tìm kiếm, bao 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,dữ liệu,MATCH(H3,tháng,0),0)
Lưu ý : bạn nên sử dụng 2 cách tra cứu với hàm INDEX và hàm MATCH. 2 cách đều linh hoạt 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.
17. VLOOKUP cho phép ký tự đại diện cho phù hợp một phần
Bất kỳ khi nào sử dụng hàm VLOOKUP ở chế độ tìm giá trị chính xác, bạn có tùy chọn sử dụng các ký tự đại diện trong giá trị tra cứu.
Excel cung cấp 2 ký tự đại diện: dấu sao (*) khớp với một hoặc nhiều ký tự, và dấu hỏi chấm (?) khớp với một 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.
Trong ví dụ bên dưới đây, nhập “Mon *” vào ô H3, name range có tên gọi là “val”, và VLOOKUP sẽ trả về kết quả là “Monet”.
Công thức trong trường hợp này rất đơn giản:
=VLOOKUP(giá trị,data,1,0)
Nếu muốn, bạn có thể điều chỉnh công thức VLOOKUP để sử dụng ký tự đại diện phù hợp, trong 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 có dạng:
=VLOOKUP(giá trị&"*",dữ liệu,1,0)
Lưu ý: Cẩn thận với các ký tự đại diện và VLOOKUP. Thủ thuật này có thể cung cấp cho bạn một kết quả sai, không như mong đợi.
18. Bạn có thể bắt lỗi #N/A và hiển thị một thông báo thân thiện
Khi ở chế độ tìm kiếm giá trị chính xác, VLOOKUP sẽ hiển thị thông báo lỗi #N/A trong trường hợp nếu không tìm thấy kết quả phù hợp. Lỗi này thông báo cho bạn biết không thể tìm kiếm được kết quả trong bảng tìm kiếm. Tuy nhiên, bạn có thể bắt lỗi này và hiển thị một thông báo khác để thay thế.
Lỗi #N/A hiển thị khi VLOOKUP không thể tìm thấy giá trị tìm kiếm phù hợp. Lỗi thông báo cho bạn biết không thể tìm kiếm được kết quả phủ hợp trong bảng tìm kiếm. Trong ví dụ dưới đây là bảng đồ uống, Latte không tồn tại nên VLOOKUP trả về lỗi #N/A.
Công thức chuẩn để tìm kiếm kết quả chính xác trong trường hợp trên là:
=VLOOKUP(E6,data,2,0)
Trường hợp nếu muốn hiển thị thông báo khác thân thiện hơn thông báo lỗi #N/A, bạn hoàn toàn có thể làm được điều này.
Cách đơn giản nhất là bọn hàm VLOOKUP trong hàm IFERROR. Hàm IFERROR cho phép bạn “bắt” bất kỳ một lỗi nào và trả về kết quả mà bạn muốn.
Để bắt lỗi #N/A và hiển thị thông báo lỗi “not found”, bạn có thể bọc công thức ban đầu trong hàm IFERROR và thiết lập kết quả mà bạn muốn:
Nếu giá trị tìm kiếm được tìm thấy, không có lỗi xảy ra và hàm VLOOKUP sẽ trả về kết quả bình thường. Dưới đây là công thức:
=IFERROR(VLOOKUP(E6,data,2,0),"Not found")
19. Số hiển thị dưới dạng text (văn bản) có thể gây ra lỗi tìm kiếm
Trong một số trường hợp khi làm việc với hàm VLOOKUP có thể chứa các số được nhập dưới dạng text (văn bản). Nếu chỉ đơn giản là lấy các con số dưới dạng text từ một cột trong bảng, điều này không quan trọng.
Nhưng nếu cột đầu tiên trong bảng chứa số được nhập dưới dạng text, bạn 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.
Trong ví dụ sau đây, số ID trong bảng Planet được nhập dưới dạng text, đó là nguyên nhân gây ra lỗi VLOOKUP trả về thông báo lỗi #N/A khi giá trị tra cứu (lookup_value) ở ô H3 được nhập vào là số 3:
Để khắc phục tình trạng này, bạn cần đảm bảo giá trị tìm kiếm và cột đầu tiên của bảng có cùng kiểu dữ liệu (hoặc cùng kiểu number hoặc cùng kiểu text).
Để làm được điều này, bạn chuyển đổi các giá trị trong cột tìm kiếm sang 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 tìm kiếm, bạn cũng 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 nối "" vào công thức như sau:
=VLOOKUP(id&"",planets,2,0)
Nếu không chắc chắn dữ liệu trong bảng tìm kiếm đang sử dụng dạng text hay number, vẫn còn cách để bạn đáp ứng cho cả hai lựa chọn. Bằng cách 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:
=IFERROR(VLOOKUP(id,planets,3,0),VLOOKUP(id&"",planets,3,0))
20. Sử dụng VLOOKUP để thay thế các hàm IF lồng nhau
Một trong những điểm thú vị là VLOOKUP có thể thay thế các hàm IF trong Excel lồng nhau. Nếu đã từng làm việc với các công thức bao gồm các hàm IF trong Excel lồng nhau, bạn sẽ biết các công thức này làm việc tốt thế nào, tuy nhiên điểm trừ là các hàm IF lồng nhau sử dụng nhiều dấu ngoặc đơn.
Ngoài ra bạn cũng nên cẩn thận thứ tự dấu ngoặc đơn khi xây dựng công thức, để đảm bảo không xuất hiện 1 lỗi logic nào.
Thường thì hàm IF lồng nhau được sử dụng trong trường hợp để tính thang điểm chữ dựa trên thang điểm số đã có. Trong ví dụ dưới đây bạn có thể nhìn thấy công thức được xây dựng từ các hàm IF lồng nhau để tính thang điểm chữ dựa trên thang điểm số:
Công thức các hàm IF lồng nhau trong ví dụ trên có dạng:
=IF(C5<><><><>
Công thức trên hoạt động tốt nhưng cần lưu ý cả logic và thang điểm số phải được đưa vào vào công thức. Nếu vì một lý do nào đó mà bạn cần phải thay đổi công thức tính, bạn sẽ phải cập nhật công thức cẩn thận, sau đó sao chép công thức xuống toàn bộ bảng.
Trái lại VLOOKUP có thể chỉ định cùng một thang điểm với công thức đơn giản. Tất cả những gì bạn cần làm là đảm bảo bảng điểm được thiết lập cho VLOOKUP (nghĩa là nó được sắp xếp theo số điểm từ nhỏ đến lớn và chứa dấu ngoặc đơn để xử lý tất cả điểm).
Sau khi xác định tên vùng dữ liệu cho bảng tính thang điểm chữ là “key”, bạn có công thức VLOOKUP đơn giản, trả về kết quả thang điểm chữ giống như công thức các hàm IF lồng nhau ban đầu:
=VLOOKUP(C5,key,2,TRUE)
Ngoài ra cả logic và giá trị điểm số không được đưa ngay vào công thức, mà được đưa vào các ô và bảng. Vì thế, nếu có bất cứ thay đổi nào, bạn 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.
21. VLOOKUP chỉ có thể xử lý một điều kiện duy nhất
VLOOKUP được thiết kế để tìm các giá trị dựa trên một điều kiện duy nhất, được cung cấp như 1 giá trị tra cứu (lookup_value) để tìm trong cột đầu tiên của bảng (cột tra cứu).
Tức là bạn không thể dễ dàng thực hiện những việc như tìm kiếm 1 nhân viên tên “Smith” 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.
Tuy nhiên, vẫn có một số cách để khắc phục hạn chế này. Một trong số đó phải kể đến là tạo 1 cột phụ để nối các giá trị từ các cột khác với nhau và tạo ra các giá trị tìm kiếm rồi xử lý giống như nhiều điều kiện.
Ví dụ, bạn muốn tìm bộ phận và nhóm của 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. Vậy làm thế nào để tra cứu cả 2 cùng một lúc?
Đầu tiên thêm một cột phụ để nối dữ liệu họ và tên với nhau:
Tiếp theo cấu hình VLOOKUP sử dụng bảng tính bao gồm cột mới này và nối tên và họ cho giá trị tìm kiếm:
Công thức VLOOKUP để tìm kiếm tên và họ sử dụng cột phụ có dạng như sau:
=VLOOKUP(D3&C3,data,4,0)
22. Sử dụng 2 hàm VLOOKUP nhanh hơn 1 hàm VLOOKUP
Nghe có vẻ hơi điên, nhưng nếu có nhiều dữ liệu cần được xử lý và yêu cầu phải trả về kết quả chính xác, bạn có thể nghĩ đến việc sử dụng 2 hàm VLOOKUP để tăng tốc quá trình xử lý.
Thử tưởng tượng bạn có rất nhiều dữ liệu đơn đặt hàng, giả sử là hơn 10.000 đơn và bạn đang sử dụng hàm VLOOKUP để tra cứu tổng số đơn hàng dựa trên mã đơn hàng (id). Giả sử công thức của bạn có dạng:
=VLOOKUP(ma don hang,data, 5, FALSE)
Tham số FALSE ở cuối công thức để buộc VLOOKUP tìm kiếm giá trị chính xác, vì trong trường hợp này có thể bị sót một vài đơn hàng. Trong trường hợp này, kiểu tìm kiếm chính xác sẽ gây ra lỗi VLOOKUP trả kết quả là lỗi #N/A
Vấn đề đặt ra là kiểu tìm kiếm chính xác làm cho hàm VLOOKUP xử lý 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ẽ nhanh hơn rất nhiều vì Excel có thể làm tìm kiếm nhị phân.
Vấn đề khác là đối với kiểu tìm kiếm tương đối, VLOOKUP có thể trả lại kết quả sai khi một giá trị không được tìm thấy. Thậm chí, kết quả có thể trông hoàn toàn bình thường, vì vậy bạn rất khó phát hiện sai sót.
Giải pháp ở đây là sử dụng VLOOKUP 2 lần, cả 2 đều là tìm kiếm tương đối. Hàm VLOOKUP đầu tiên chỉ đơn giản là kiểm tra giá trị thực sự tồn tại. Hàm 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ị nào mà bạn muốn, chẳng hạn như “Not found”.
Công thức cuối cùng có dạng:
=IF(VLOOKUP(order_id,order_data,1,TRUE)=order_id, VLOOKUP(order_id,order_data,5,TRUE), "Missing")
Lưu ý: Để áp dụng phương pháp này, các dữ liệu của bạn phải được sắp xếp. Nó chỉ đơn giản là cách bảo vệ để hạn chế việc mất một giá trị tra cứu.
23. Kết hợp hàm INDEX + MATCH nhanh hơn hàm VLOOKUP
Nếu tham gia các diễn đàn Excel, bạn sẽ bị cuốn vào các cuộc tranh luận giữa hàm VLOOKUP với hàm INDEX và hàm MATCH.
Về cơ bản là kết hợp hàm INDEX + MATCH có thể làm được mọi thứ mà hàm VLOOKUP (và hàm HLOOKUP) có thể làm, nhưng tốc độ xử lý nhanh hơn và linh hoạt hơn. Ngoài ra bạn cũng có thể bắt đầutìm hiểu về hàm INDEX và hàm MATCH ngay bây giờ vì nó cung cấp cho bạn 1 bộ công cụ tốt hơn.
Các lập luận chống lại hàm INDEX + MATCH cho rằng công thứcđòi hỏi sử dụng kết hợp 2 hàm thay vì 1, do đó, sự phức tạp tăng lên cho người dùng, nhất là những người dùng mới làm quen.
Nếu sử dụng Excel thường xuyên, chắc hẳn bạn sẽ muốn tìm hiểu làm thế nào để sử dụng INDEX + MATCH. Đó là 1 sự kết hợp hữu ích. Nhưng điều đó không có nghĩa là bạn bỏ qua hàm VLOOKUP.
Chắc chắn một điều là bạn sẽ phải nhờ đến hàm VLOOKUP mọi lúc mọi nơi, đặc biệt trong trường hợp nếu bạn phải nhận lại dữ liệu từ người khác. Trong những tình huống đơn giản, VLOOKUP sẽ giúp bạn hoàn thành công việc tốt hơn.
Như vậy trong bài viết 23 điều về hàm VLOOKUP ai cũng nên biết (Phần 3) này Taimienphi.vn đã giới thiệu hết cho bạn các tính năng, cách sử dụng cũng như một số ví dụ về hàm. Nếu có bất kỳ thắc mắc hoặc câu hỏi nào cần giải đáp, bạn có thể để lại ý kiến của mình trong phần bình luận bên dưới bài viết nhé.