Hướng dẫn dùng hàm vlookup nân cao
Hàm Vlookup nâng cao trong Excel xuất phát từ hàm Vlookup thông thường nhưng được kết hợp với một số hàm khác để giải quyết những tình huống nâng cao mà hàm Vlookup thông thường không giải quyết được. Bản chất hàm Vlookup nâng cao là hàm tìm kiếm giá trị theo cột và trả về phương thức hàng dọc (theo cột) giúp chúng ta thống kê, dò tìm dữ liệu theo cột một cách nhanh chóng và tiện lợi. Trong bài viết này, Học excel cơ bản sẽ giúp bạn hiểu và sử dụng hàm Vlookup một cách thành thạo qua các ví dụ thực tế nhất. Show
1. Chức năng của hàm Vlookup nâng cao.Trong Excel chúng ta sử dụng hàm Vlookup để tìm kiếm dữ liệu trong bảng hoặc một phạm vi theo cột trong một bảng dò tìm đã định nghĩa trước. Như vậy, chức năng chính của hàm Vlookup là dùng để tìm kiếm giá trị trong một bảng giá trị cho trước. Hàm Vlookup nâng cao giúp ta tìm kiếm với nhiều điều kiện, trên nhiều trang sheet và từ phải qua trái hoặc từ trái qua phải… một cách dễ dàng bằng cách kết hợp thêm một số hàm khác. 2. Cú pháp hàm vlookup trong excel.VLOOKUP(LOOKUP_VALUE, TABLE_ARRAY, COL_INDEX_NUM, [RANGE_LOOKUP] ) Trong đó:
3. Ví dụ sử dụng hàm Vlookup nâng cao trong Excel.3.1. Sử dụng hàm Vlookup cơ bản.Ví dụ: Giả sử, bạn có một bảng dữ liệu nhân viên, lưu trữ mã nhân viên, họ tên, quê quán. Một bảng khác có 2 cột là mã nhân viên và quê quán. Giờ bạn muốn điền thông tin quê quán cho từng nhân viên ở bảng 2 thì phải làm như thế nào? Hàm Vlookup nâng cao Hình 1: Hàm Vlookup nâng cao. Để điền thông tin quê quán cho nhân viên, tại ô G4, ta nhập công thức dò tìm chính xác như sau: =VLOOKUP(F4,$B$4:$D$10,3,0) Trong đó:
Sau khi điền xong công thức cho ô G4, tiếp tục kéo xuống copy công thức cho những nhân viên còn lại. Hàm Vlookup nâng cao Hình 2: Hàm Vlookup nâng cao Xem thêm về hàm Vlookup: Tại đây 3.2. Sử dụng hàm Vlookup nâng cao với nhiều điều kiện.Hàm vlookup nhiều điều kiện thực chất là hàm Vlookup thông thường, nhưng để sử dụng được thì ta gộp nhiều điều kiện thành 1 điều kiện bằng cách tạo cột phụ để hàm Vlookup hiểu và tìm kiếm. Ví dụ: Bạn có một danh sách sản lượng sản xuất cho từng sản phẩm, từng ca. Làm thế nào để biết được sản lượng của 1 sản phẩm nào đó trong từng ca là bao nhiêu? Hàm Vlookup nâng cao Hình 3: Hàm Vlookup nâng cao Với bài toán tìm kiến dữ liệu theo hàng ngang trong Excel chắc chắn bạn sẽ nghĩ ngay đến hàm Vlookup, nhưng ở đây ta cần tìm sản lượng của từng sản phẩm trong từng ca (2 điều kiện) mà hàm vlookup thông thường lại chỉ dùng được với 1 điều kiện. Nên ta cần biến đổi điều kiện đầu vào từ 2 điều kiện thành 1 điều kiện bằng cách tạo ra một cột phụ mới từ việc ghép Sản phẩm và Ca. Các bước thực hiện: B1: Tạo cột phụ. B2: Viết hàm với điều kiện tìm kiếm là cột phụ vừa tạo. Chi tiết các bước: B1: Tạo cột phụ: Ta tạo thêm cột mới, cột này đứng ở trước cột Sản phẩm và được tạo ra bằng cách ghép cột Sản phẩm và cột Ca. Công thức ghép: [Sản phẩm]&[Ca] Với hàng đâu tiền thì công thức sẽ là: C4&D4 Sau khi tạo công thức cho hàng đầu ta copy công thức đó cho các hàng tiếp theo để hoàn thành cột. Sau khi hoàn thành thì cột phụ sẽ có dạng như hình sau: Hàm Vlookup nâng cao Hình 4: Hàm Vlookup nâng cao. Bước 2: Viết công thức tìm kiếm với cột phụ vừa tạo. Để tìm kiến sản lượng của 1 sản phẩm theo ca tại ô H6 ta nhập công thức: =VLOOKUP(H4&H5,$B$4:$E$8,4,0) Trong đó:
Quan sát hình dưới để hiểu hơn về các thành phần của công thức chúng ta vừa tạo. Hàm Vlookup nâng cao Hình 5: Hàm Vlookup nâng cao. Nếu muốn tìm sản lượng của sản phẩm khác hoặc ca khác bạn chỉ cần nhập sản phẩm và ca cần tìm vào bảng tìm kiếm như trên hình mà không cần tạo lại công thức. Để hiểu hơn về hàm Vlookup nhiều điều kiện, bạn tham khảo: Hàm Vlookup 2 điều kiện, Hàm Vlookup nhiều điều kiện. 3.3. Sử dụng hàm Vlookup nâng cao để dò tìm từ phải qua trái.Thông thường hàm Vlookup sẽ đối chiếu sang bên phải để lấy giá trị trả tương ứng và cột tìm kiếm phải dược đặt đầu tiên. Nhưng trong một số trường hợp chùng ta cần tìm kiếm, đối chiếu ngược lại từ phải qua trái để lấy giá trị tương ừng thì hàm Vlookup thông thường không thể dùng được. Ví dụ: Ta có bảng mã vùng các tỉnh trên cả nước gồm cột mã vùng và cột Địa chỉ. Chúng ta cần tìm mã vùng của tỉnh thành cho trước? Dò tìm từ phải qua trái trong Excel Hình 6: Hàm Vlookup nâng cao. Cột địa chỉ nằm ở phía bên phải của cột mã vùng, chiều tham chiếu là từ phải qua trái. Điều này ngược với cách tham chiếu thông thường của hàm vlookup, do đó chúng ta dùng hàm Lookup trong Excel để lấy mã vùng của tỉnh thành cho trước. Cú pháp của hàm Lookup: =LOOKUP(Giá trị cần tìm,Vùng chứa giá trị cần tìm,Vùng chứa giá trị kết quả) Trong đó:
Với ví dụ trên, tại ô F6 ta nhập công thức: =LOOKUP(F4,C4:C10,B4:B10) Trong đó:
Sau khi nhập xong công thức ta sẽ được kết quả như hình dưới. Hàm Vlookup nâng cao Hình 7: Hàm Vlookup nâng cao. Xem thêm về hàm Vlookup ngược: Tại đây 4. Một số lưu ý khi sử dụng hàm Vlookup nâng cao.4.1. Sử dụng địa chỉ tuyệt đối khi dùng hàm Vlookup nâng cao.Trong Excel có 3 loại địa chỉ:
Khi sử dùng hàm Vlookup trong Excel bạn thường phải tìm kiếm cho cả cột nên việc copy công thức là không tránh khỏi. Lúc này bạn cần lưu ý để địa chỉ của vùng tìm kiếm là địa chỉ tuyệt đối để khi ta copy công thức cho những hàng khác thì vùng tìm kiếm của ta không bị thay đổi. 4.2. Hàm Vlookup nâng cao trả về giá trị đầu tiền được tìm thấy.Nếu cột ngoài cùng bên trái của bảng chứa các giá trị trùng lặp nhau thì sẽ lấy giá trị đầu tiên được tìm thấy. Ví dụ, hãy xem hình minh họa bên dưới. Tìm kiếm từ phải qua trái trong Excel Hình 8: Hàm Vlookup nâng cao. Giải thích: Hàm VLOOKUP trả về mã vùng của Hà Nội là 30, không phải là 31 vì 30 là giá trị đầu tiên được tìm thấy. 4.3. Hàm Vlookup nâng cao không phân biệt chữ hoa chữ thường.Thực hiện tra cứu không phân biệt chữ hoa chữ thường. Ví dụ, ở bên dưới tra cứu NGUYỄN HUY (ô G4) ở cột ngoài cùng bên trái của bảng. Lưu ý khi sử dụng hàm Vlookup nâng cao Hình 9: Hàm Vlookup nâng cao. Giải thích: Hàm VLOOKUP không phân biệt chữ hoa chữ thường nên nó sẽ tra cứu NGUYỄN HUY hoặc Nguyễn Huy hoặc nguyễn huy… Kết quả là, hàm VLOOKUP trả về tiền quê của Nguyễn Huy Tưởng (trường hợp đầu tiên). Tổng kết. Vlookup là một hàm cơ bản trong Excel hỗ trợ rất mạnh trong việc thống kê và dò tìm dữ liệu nhưng nếu chỉ dừng ở mức cơ bản thì nhiều trường hợp với những yêu cầu thực tế thì không phải lúc nào hàm Vlookup cùng giải quyết được. Trên đây là hướng dẫn chi tiết cách sử dụng hàm Vlookup nâng cao để tìm kiếm nhiều điều kiện, dò tìm từ phải quả trái kèm theo là một số ví dụ cụ thể hi vọng giúp ích cho các bạn. Chúc các bạn thành công! Gợi ý học tập mở rộng. Trọn bộ khoá học Excel cơ bản miễn phí: Học Excel cơ bản Các bài viết liên quan: Hàm VLOOKUP 2 điều kiện trong Excel Hàm VLOOKUP nhiều điều kiện trong Excel Hàm VLOOKUP kết hợp hàm IF trong Excel Hàm VLOOKUP kết hợp hàm SUM và SUMIF Hàm VLOOKUP ngược và cách dùng hàm VLOOKUP ngược trong Excel [Hàm VLOOKUP bị lỗi N/A, nguyên nhân, cách khắc phục.](https://hocexcelcoban.com/ham-vlookup-bi-loi-cach-sua-loi-na-name-value/)Hàm VLOOKUP trong Google Sheet 0 0 đánh giá Đánh giá bài viết Nguyễn Danh TúHọc Excel cơ bản tổng hợp các bài giảng video từ căn bản đến nâng cao theo đúng trình tự, giúp bạn học Excel cơ bản một cách hiệu quả và chia sẻ những kinh nghiệm, sai lầm đã được! |