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. 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.
Về 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.
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ư:
Cách sử dụng hàm vlookup kết hợp hàm Match khi tìm theo nhiều cột chứa kết quả
Hướng dẫn sử dụng kết hợp VLOOKUP và CHOOSE: Một cách khác để tìm kiếm từ phải qua trái
Việc xác định đơn giá [hay tính đơn giá] theo nhiều điều kiện thường khiến chúng ta gặp nhiều khó khăn. Bời việc này đòi hỏi bạn phải sử dụng tốt các hàm, biết cách tổ chức bảng dữ liệu và có tư duy logic tốt. Để giúp các bạn rèn luyện thêm những điều đó, chúng ta hãy cùng Học Excel Online tìm hiểu 4 cách xác định đơn giá theo nhiều điều kiện trong excel nhé.
Ví dụ chúng ta có bảng dữ liệu bán hàng như sau:
Đơn giá của mỗi sản phẩm được xác định dựa theo 2 yếu tố: Mã hàng và Mã công ty.
Yêu cầu là hãy xác định đơn giá dựa vào mã công ty kết hợp với mã hàng tra trong bảng đơn giá để có giá trị tương ứng tại vùng G3:G9.
Cách thứ 1: Dùng hàm VLOOKUP kết hợp hàm MATCH
Tham khảo thêm bài viết: Cách sử dụng hàm vlookup kết hợp hàm Match khi tìm theo nhiều cột chứa kết quả
Khi nghĩ tới việc tham chiếu 1 giá trị [cụ thể ở đây là đơn giá] theo nhiều điều kiện [từ 2 điều kiện trở lên], chúng ta nghĩ tới việc sử dụng hàm VLOOKUP kết hợp hàm MATCH.
Trọng tâm của cách làm này là các bạn phải xác định VLOOKUP theo giá trị nào, MATCH theo giá trị nào. Để làm điều đó, chúng ta chú ý cấu trúc của bảng đơn giá:
- Mã công ty nằm ở cột đầu tiên trong bảng đơn giá => Theo nguyên tắc hàm VLOOKUP thì giá trị tìm kiếm phải nằm ở cột đầu tiên. Vì vậy VLOOKUP sẽ tìm theo mã công ty.
- Kết quả cần tìm của hàm VLOOKUP là đơn giá của các mã hàng, tương ứng theo cột thứ mấy trong bảng. Vì vậy để xác định cột thứ mấy, chúng ta dùng hàm MATCH để xét giá trị Mã hàng.
Cấu trúc như sau: VLOOKUP[Mã công ty, Bảng đơn giá, MATCH[mã hàng, dòng mã hàng, 0], 0]
Khi thay các nội dung thành tọa độ tham chiếu ta có:
G3=VLOOKUP[C3,$B$14:$F$17,MATCH[B3,$B$13:$F$13,0],0]
Cách thứ 2: Dùng hàm HLOOKUP kết hợp hàm MATCH
Hàm HLOOKUP cũng là 1 hàm tìm kiếm tương tự với VLOOKUP, chỉ thay đổi về phương hướng, chiều tìm kiếm. Trong trường hợp này chúng ta biện luận như sau:
- Đối tượng tìm kiếm của hàm HLOOKUP phải nằm ở dòng đầu tiên của bảng tìm kiếm. Vì vậy chúng ta thấy dòng Mã hàng [dòng 13] là dòng đầu tiên của vùng bảng đơn giá hàng hóa. Do đó đối tượng tìm kiếm của hàm HLOOKUP trong trường hợp này là Mã hàng.
- Khi đó kết quả của hàm HLOOKUP sẽ lấy tương ứng xuống bao nhiêu dòng? Căn cứ vào mã công ty để xác định dòng. Vì vậy chúng ta dùng hàm MATCH tìm theo Mã công ty.
Cấu trúc như sau: HLOOKUP[Mã hàng, Bảng đơn giá, MATCH[Mã công ty, cột Mã công ty, 0], 0]
Khi thay các nội dung thành tọa độ tham chiếu ta có:
G3=VLOOKUP[C3,$B$14:$F$17,MATCH[B3,$B$13:$F$13,0],0]
Cách thứ 3: Dùng hàm INDEX và MATCH
Bạn có biết rằng dùng Index + Match thì tốt hơn dùng Vlookup hay Hlookup? Nếu không tin thì bạn hãy xem lại bài viết:
Vì sao dùng INDEX và MATCH tốt hơn dùng VLOOKUP trong Excel
Trong trường hợp này, chúng ta cùng tìm hiểu cách sử dụng INDEX MATCH như thế nào nhé.
Khi nhắc tới INDEX MATCH, các bạn hãy ghi nhớ 3 yếu tố:
- Vùng dữ liệu nào? Chúng ta cần tính đơn giá, do đó xét vùng B13:F17 là Bảng đơn giá.
- Kết quả ở dòng nào? Xác định dòng dựa theo hàm MATCH, tìm theo Mã công ty.
- Kết quả ở cột nào? Xác định cột dựa theo hàm MATCH, tìm theo Mã hàng.
Cấu trúc cụ thể: INDEX[Bảng dữ liệu, MATCH[Mã công ty, Cột Mã công ty,0], MATCH[Mã hàng, dòng Mã hàng,0]]
Khi thay các nội dung thành tọa độ tham chiếu ta có:
G3=INDEX[$B$13:$F$17,MATCH[C3,$B$13:$B$17,0],MATCH[B3,$B$13:$F$13,0]]
Cách thứ 4: Dùng hàm SUMPRODUCT xác định đơn giá theo nhiều điều kiện
Hàm SUMPRODUCT là một hàm sử dụng được trong rất nhiều trường hợp. Và chúng ta hoàn toàn có thể ứng dụng hàm này trong việc xác định đơn giá theo nhiều điều kiện. Cụ thể như sau:
- Khi nào áp dụng được? Khi bảng đơn giá không có các mã trùng nhau.
- Cách dùng: Xét 2 điều kiện dạng mảng 2 chiều: Mã hàng [vùng C13:F13] và Mã công ty [vùng B14:B17], kết quả tương ứng lấy trong vùng C14:F17
Khi thay các nội dung thành tọa độ tham chiếu ta có:
G3=SUMPRODUCT[[$B$14:$B$17=C3]*[$C$13:$F$13=B3]*$C$14:$F$17]
Cả 4 cách đều cho ra kết quả giống nhau.
Bạn thấy đó, để thực hiện 1 yêu cầu trong Excel chúng ta có thể có nhiều cách làm khác nhau. Có rất nhiều hàm có thể sử dụng, kết nối với nhau giúp chúng ta đạt được kết quả như mong muốn. Hãy tìm hiểu thêm nhiều kiến thức của Excel cùng Học Excel Online bạn nhé.