VLOOKUP là một hàm cơ bản và hay sử dụng trong Excel dùng để tìm kiếm, tham chiếu theo 1 giá trị. Nhưng trong một số trường hợp chúng ta muốn dò tìm kết quả dựa theo một số ký tự nhất định chứ không phải toàn bộ các ký tự của ô chứa giá trị tìm kiếm.
Hướng dẫn cách sử dụng hàm VLOOKUP kết hợp hàm LEFT trong Excel
Hướng dẫn cách sử dụng hàm VLOOKUP kết hợp hàm LEFT trong Excel | Bostonenglish.edu.vn
Trong trường hợp như vậy chúng ta sẽ cần kết hợp hàm LEFT bên trong hàm VLOOKUP. Sau đây chúng ta cùng tìm hiểu một số trường hợp cụ thể như sau:
Xác định bộ phận làm việc theo mã nhân viên
Ví dụ chúng ta có 1 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 như sau:
Trong đó mã nhân viên gồm 5 ký tự, 2 ký tự đầu đại diện cho bộ phận làm việc.
Bảng E1:F5 bao gồm nội dung chi tiết tên các bộ phận.
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 làm:
Để tra cứu được bộ phận theo mã nhân viên, chúng ta cần làm 2 việc sau:
- Thứ 1: Tách được 2 ký tự đầu trong mã nhân viên bằng hàm LEFT
- Thứ 2: Sử dụng kết quả của hàm LEFT để tham chiếu trong bảng E1:F5 để tìm bộ phận tương ứng.
Cách sử dụng hàm LEFT chúng ta có:
=LEFT(text, [num_chars])
- text: đoạn văn bản cần tách ký tự
- num_chars: số ký tự cần tách. Nếu không nhập thì sẽ tự nhận giá trị là 1
Trong trường hợp này, số ký tự cần tách là 2, do đó ta có:
Tại ô C2 sử dụng hàm =LEFT(A2,2) ta thu được kết quả 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ên bộ phận. Khi đó coi 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)
Trong đó kết quả cần tìm là cột thứ 2 trong vùng bảng E1:F5. Phương thức tham chiếu là tìm chính xác theo ký hiệu nên sử dụng số 0.
Công thức hàm RIGHT
– Công thức:
=RIGHT(text;[num_chars])
– Trong đó:
+ text: Chuỗi văn bản có chứa chuỗi ký tự mà bạn muốn lấy phần bên phải.
+ [num_chars]: Số ký tự bên phải muốn trả về.
– Hàm RIGHT dùng để lấy ký tự nằm ở bên phải của chuỗi.
Chúng ta sẽ sử dụng lại bảng dữ liệu trên để thực hiện hàm VLOOKUP với hàm RIGHT. Tìm khóa học của từng sinh viên.
Bước 1: Sử dụng hàm RIGHT để lấy ra mã khóa học trong MSSV.
– Cách thực hiện:
Tại ô C2 > Nhập công thức “=RIGHT(A2;3)” > Nhấn enter > Cho ra kết quả 3 ký tự bên phải của ô A2.
– Công thức:
=RIGHT(A2;3)
– Ý nghĩa công thức: Lấy 3 ký tự cuối là mã khóa học trong MSSV ở cột A.
Sử dụng hàm RIGHT để lấy ra mã khóa học trong MSSV
Bước 2: Kết hợp RIGHT với VLOOKUP.
– Cách thực hiện:
Tại ô D2 > Nhập công thức “=VLOOKUP(C2;$D$9:$E$10;2;0)” > Nhấn enter > Cho ra kết quả khóa học theo đối chiếu ở bảng khóa học.
– Công thức:
=VLOOKUP(C2;$D$9:$E$10;2;0)
– Ý nghĩa công thức: Lấy ra tên khóa học dựa vào giá trị dò tìm là mã khóa học ở cột C.
Sử dụng VLOOKUP để xác định khóa học
Tuy nhiên việc kết hợp riêng lẻ mất thời gian, nên ta gộp chung 2 hàm VLOOKUP và RIGHT lại với nhau như sau:
Bước 3: Tại ô D2 > Nhập công thức “=VLOOKUP(RIGHT(A2;3);$D$9:$E$10;2;0)” > Nhấn enter > Cho ra kết quả khóa học theo đối chiếu ở bảng khóa học.
– Công thức:
=VLOOKUP(RIGHT(A2;3);$D$9:$E$10;2;0)
Kết hợp RIGHT với VLOOKUP
Tham chiếu với 1 số ở định dạng Text
Có thể ít khi bạn gặp trường hợp này, nhưng trong Kế toán thì gặp nhiều, đó là khi sử dụng các tài khoản kế toán, chúng ta rất hay nhầm lẫn không biết con số đó ở dạng Text hay dạng số. Do đó khi sử dụng hàm VLOOKUP ra kết quả lỗi #N/A như sau:
Nguyên nhân:
Trong hàm VLOOKUP, vị trí ô I1 là dữ liệu dạng Số
Trong vùng DM_TK!A5:E315 thì cột Số TK (cột A) lại là dạng Text
Khi đó 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ả.
Để khắc phục điều này, chúng ta sẽ sử dụng hàm LEFT và hàm LEN để chuyển giá trị tại ô I1 về dạng TEXT. Khi đó tham chiếu bằng hàm VLOOKUP sẽ chính xác:
Xem thêm: Hướng dẫn cách sử dụng hàm LEFT nâng cao kết hợp nhiều hàm trong Excel
Như vậy là chúng ta đã sử dụng được hàm VLOOKUP kết hợp hàm LEFT rồi. Trong nội dung này, các bạn nên chú ý trình tự logic: Dùng hàm LEFT trước, sau đó mới viết hàm VLOOKUP lồng bên ngoài. Như vậy khi viết sẽ chính xác hơn, không bị sai sót cũng như dễ hiểu hơn.
Ngoài ra hàm VLOOKUP còn có thể kết hợp với rất nhiều hàm khác như: IF, CHOOSE, MID, VALUE, LEN, …
Các lỗi thường gặp khi kết hợp hàm VLOOKUP và hàm LEFT/RIGHT
Lỗi #N/A
Lỗi #N/A xuất hiện do không tìm thấy giá trị dò tìm.
Ví dụ như hình dưới đây ta thấy trong công thức hàm RIGHT lấy 5 ký tự, không khớp với giá trị trong bảng dò tìm. Chính vì vậy hàm VLOOKUP không dò tìm được.
Cách khắc phục: Thay đổi đối số thứ 2 trong hàm RIGHT phù hợp với bảng dò tìm. (chỉ tách ra 3 ký tự cuối từ chuỗi mã sinh viên).
Lỗi #N/A
Lỗi #REF!
Lỗi #REF! xuất hiện do không tìm thấy cột dò tìm.
Ví dụ như trong hình ta có thể thấy trong dải ô $D$9:$E$10 chỉ có 2 cột để dò tìm.
Cách khắc phục: Sửa giá trị cột dò tìm phù hợp với dải ô có số cột đã chọn.
Lỗi #REF!
Lỗi #VALUE!
Lỗi #VALUE! xuất hiện do không tìm thấy giá trị cột dò tìm.
Cách khắc phục: Thay đổi giá trị cột dò tìm phải lớn hơn hoặc bằng 1.
Lỗi #VALUE!
Một số lưu ý khi kết hợp hàm VLOOKUP và hàm LEFT/RIGHT
– Cách viết công thức của các hàm VLOOKUP, LEFT hay RIGHT đều không phân biệt chữ hoa hay thường.
– VLOOKUP có 2 kiểu tìm kiếm là tìm kiếm tương đối và tìm kiếm tuyệt đối.
– Bảng dò tìm kết quả cần được sắp xếp trước khi thực hiện tìm kiếm.
– VLOOKUP chỉ tìm kiếm dữ liệu từ trái qua phải.
– Chỉ tìm được giá trị đầu tiên xuất hiện trong bảng.
Trên đây là hướng dẫn cách sử dụng kết hợp hàm Vlookup với hàm Left cùng một số hàm có liên quan. Hi vọng rằng sau bài viết này, các bạn có thể dễ dàng sử dụng kết hợp hàm Vlookup với hàm Left để thuận tiện hơn trong công việc của mình.
Ngoài việc kết hợp với hàm Left, hàm Vlookup còn được kết hợp với hàm IF để giải quyết nhiều bài toán khác nhau, bạn có thể kết hợp hàm Vlookup và IF để tìm các dữ liệu có điều kiện.
Nguồn Tổng hợp
Bostonenglish.edu.vn cũng giúp các bạn giải đáp những vấn đề sau đây:
- Hàm VLOOKUP kết hợp LEFT
- Hàm VLOOKUP kết hợp hàm IF
- Hàm VLOOKUP kết hợp VALUE
- Hàm VLOOKUP kết hợp hàm MID
- Hàm Vlookup kết hợp hàm AND
- Cách dụng hàm VLOOKUP
- Hàm VLOOKUP kết hợp hàm len
