Trong bài trước tip.com.vn đã chia sẻ về hàm tham chiếu VLOOKUP dùng để tìm kiếm, tham chiếu theo 1 giá trị. Thế nhưng đó chỉ là mức cơ bản vì trong nhiều trường hợp yêu cầu các bạn tham chiếu và dò tìm kết quả chỉ theo một số ký tự nhất định mà thôi. Lúc này bạn cần kết hợp hàm VLOOKUP với hàm LEFT để có một tham chiếu đúng. Dưới đây mình sẽ giới thiệu cho các bạn 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ể.
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ụ, chúng ta có một bảng danh sách nhân viên với 3 cột gồm: Mã nhân viên, Họ tên và Bộ phận. Và đương nhiên chúng ta có bảng tham chiếu để xác định bộ phận làm việc như hình bên dưới.
Phân tích bài toán
Theo ví dụ ta có mã nhân viên gồm 5 ký tự, trong đó 2 ký tự đầu đại diện cho bộ phận làm việc.
Bảng tham chiếu vùng E1:F5 sẽ là bảng tra cứu tên bộ phận dựa theo ký hiệu
Vậy làm cách nào để có thể tra cứu được Bộ phận của mỗi người căn cứ vào Mã của họ?
Cách giải bài toán với VLOOPUP và LEFT.
Để giải quyết bài toán, chúng ta cần thực hiện như sau:
- 1: Sử dụng hàm LEFT để tách 2 ký tự đầu trong cột Mã nhân viên.
- 2: Sử dụng hàm VLOOKUP kết hợp với hàm LEFT ở trên để tham chiếu trong bảng E1:F5 để kết quả 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 riêng ký tự
- num_chars: Là số ký tự cần tách. Nếu không nhập thì hàm sẽ tự nhận giá trị là 1
Áp dụng công thức hàm LEFT vào ô C2 =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, 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 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 sử dụng hàm VLOOLUP, các bạn có thể tham khảo bài viết 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 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 kết quả trả về là tên bộ phận)
- 0 là phương thức tham chiếu chính xác.
Kết quả như sau:
Như vậy, kết quả đã trả về 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 hàm VLOOKUP sẽ gây lỗi #N/A. Đây là lỗi thường gặp trong Excel, 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 ô có cả text và number. Nếu không chú ý 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 hàm sẽ trả về lỗi #N/A
Nguyên nhân:
Như mình nói ở trên, nguyên nhân chính là tham chiếu sai định dạng. Trong hàm VLOOKUP ở ví dụ trên ô I1 là một ô có dữ liệu dạng số. Thế nhưng vùng tham chiếu trong 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áo lỗi do Excel coi 2 dữ liệu này không tương đồng với nhau, dẫn tới không tìm thấy kết quả và sẽ báo lỗi #N/A.
Khắc phục lỗi này khá đơ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, sau đó tham chiếu bằng hàm VLOOKUP sẽ cho kết quả chính xác:
Lời kết
Như vậy, 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 một số ví dụ đơn giản, không quá khó đúng không nào. Để hạn chế xảy ra sai sót, các bạn nên sử dụng hàm LEFT trước để check kết quả, sau đó viết hàm VLOOKUP lồng bên ngoài. Cách viết này dù lâu hơn 1 chút nhưng chắc chắn mang lại kết quả đúng, mà có lỗi cũng dễ sử hơn.
