Trong bài trước chúng ta đã được học về hàm tham chiếu VLOOKUP dùng để tìm kiếm, tham chiếu theo 1 giá trị. Thế nhưng đấy chỉ là mức cơ bản vì trong khá nhiều trường hợp yêu cầu chúng ta tham chiếu và dò tìm kết quả chỉ theo vài ký tự nhất định mà thôi. Lúc này chúng ta sẽ cần kết hợp hàm VLOOKUP với hàm LEFT để tạo nên tham chiếu đúng. Dưới đây thủ thuật vn sẽ cùng các bạn tìm hiểu về cách sử dụng hàm VLOOKUP kết hợp hàm LEFT trong Excel thông qua các ví dụ cụ thể nhé.
VLOOKUP kết hợp LEFT để xác định bộ phận làm việc thông qua mã nhân viên
Ví dụ dưới đây chúng ta có bảng danh sách nhân viên với 3 cột Mã nhân viên, Họ tên, Bộ phận. Và chúng ta có cũng bảng tham chiếu để xác định bộ phận làm việc như như hình dưới.
Phân tích bài toán
Chúng ta có mã nhân viên có 5 ký tự trong đó 2 ký tự đầu đại diện cho bộ phận làm việc.
Bảng tham chiếu E1:F5 sẽ là bảng tra cứu tên bộ phận dựa theo kỹ hiệu
Vậy làm thế nào để có thể tra cứu được Bộ phận của từng người căn cứ vào Mã của họ?
Cách giải bài toán với VLOOPUP và LEFT.
Chúng ta cần làm 2 việc sau để giải quyết bài toán
- 1: Sử dụng hàm LEFT để tách được 2 ký tự đầu trong mã nhân viên.
- 2: Sử dụng VLOOKUP kết hợp với hàm LEFT ở trên để tham chiếu trong bảng E1:F5 để trả về bộ phận tương ứng.
Nhắc lại cú pháp hàm LEFT
=LEFT(text, [num_chars])
Trong đó
- text: Là đoạn văn bản cần tách ký tự
- num_chars: Là số ký tự cần tách. Nếu không nhập thì sẽ tự nhận giá trị là 1
Áp dụng công thức hàm LEFT vào ô C2. Gõ =LEFT(A2,2) nhấn Enter chúng ta thu được kết quả SX là 2 ký tự đầu của ô A2.
Tiếp theo, bây giờ chúng ta tham chiếu kết quả của hàm LEFT tới vùng bảng E1:F5 để tìm từng tên bộ phận. Lúc này kết quả hàm LEFT chính là giá trị cần dò tìm.
Viết hàm VLOOKUP lồng với hàm LEFT như sau:
=VLOOKUP(LEFT(A2,2),E1:F5,2,0)
Nếu chưa hiểu về cách VLOOLUP hoạt động có thể đọc lại bài của mình tại đây
Cách sử dụng hàm VLOOKUP chi tiết
Giải thích công thức:
- LEFT(A2:2) trả về giá trị cần dò tìm
- E1:F5 là vùng tham chiếu (vùng muốn dò tìm)
- 2 là cột thứ 2 trong vùng tham chiếu ( vì chúng ta muốn trả về tên bộ phận mà)
- 0 là phương thức tham chiếu chính xác.
Kết quả như sau:
Vậy là chúng ta đã trả về được bộ phận Sản xuất cho ô C2. Copy công thức cho các ô còn lại là xong.
Tham chiếu sai định dạng
Việc tham chiếu sai định dạng trong VLOOKUP sẽ gây lỗi #N/A. Đây là lỗi gặp khá nhiều nhất là trong kế toán. Lỗi này chủ yếu xảy ra khi chúng ta tham chiếu đến 1 ô text và number. Nếu không để ý kỹ việc định dạng này kết quả tham chiếu sẽ trả về lỗi #N/A.
Vị dụ dưới dây trả về lỗi #N/A
Nguyên nhân:
Như nói ở trên nguyên nhân chính là tham chiếu sai định dạng. Trong hàm VLOOKUP ở trên ô I1 là một ô có dữ liệu dạng số. Thế nhưng trong vùng tham chiếu cột A lại là cột dữ liệu dạng Text. vì vậy kết quả trả về chắc chắn bị lỗi do Excel sẽ coi 2 dữ liệu này không tương đồng với nhau. Dẫn tới không tìm ra kết quả và thông báo lỗi #N/A.
Để khắc phục điều này, đơn giản bạn chỉ cần sử dụng hàm LEFT và hàm LEN để chuyển đổi giá trị tại ô I1 về dạng TEXT rồi sau đó tham chiếu bằng hàm VLOOKUP sẽ cho kết quả chính xác:
Lời kết
Vậy là chúng ta đã cùng nhau tìm hiểu cách sử dụng hàm VLOOKUP kết hợp hàm LEFT qua vài ví dụ đơn giản rồi đấy không khó lắm đúng không nào. Để tránh sai sót chúng ta nên dùng hàm LEFT trước để check kết quả sau đó mới viết hàm VLOOKUP lồng bên ngoài. Cách viết này có lâu hơn 1 chút nhưng lại chắc chắn cho kết quả đúng mà có lỗi cũng dễ sử hơn.
