Hàm Vlookup Nâng Cao, Cách Sử Dụng Hàm Vlookup Nâng Cao. - Excel

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.

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 đó:

• Lookup_value(bắt buộc): Giá trị cần tìm, có thể là ô tham chiếu, một giá trị hoặc chuỗi văn bản.
• Table_array(bắt buộc): Bảng tìm kiếm giá trị gồm hai cột dữ liệu trở lên. Có thể là mảng thường, được đặt tên hoặc bảng Excel. Cột chứa giá trị tìm kiếm phải được đặt đầu tiên của Table_array.
• Row_index_num(bắt buộc): Số thứ tự của cột chứa kết quả trả về trong Table_array.
• Range_lookup(tuỳ chọn): Một giá trị logic (Boolean) cho biết hàm VLOOKUP cần phải tìm kết quả chính xác hay tương đối.
  • Nếu TRUE hoặc bỏ qua, kết quả khớp tương đối được trả về. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Vlookupcủa bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value.
  • Nếu FALSE, chỉ kết quả khớp chính xác được trả về. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếm, hàm Vlookup sẽ trả về lỗi #N/A.

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 đó:

• F4: Là giá trị cần đối chiếu.
• $B$4:$D$10: Là bảng dò tìm, địa chỉ của bảng dò tìm phải là địa chỉ tuyệt đối.
• 3: Số thứ tự cột dữ liệu trên bảng dò tìm.
• 0: Kiểu tìm kiếm chính xác.

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 đó:

• H4&H5: Là giá trị cần đối chiếu.
• $B$4:$E$8: Là bảng dò tìm bao gồm cả cột phụ
• 4: Số thứ tự cột dữ liệu trên bảng dò tìm.
• 0: Kiểu tìm kiếm chính xác.

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 đó:

• Giá trị cần tìm: Có thể là số, văn bản, giá trị logic, tên hoặc tham chiếu tới 1 giá trị.
• Vùng chứa giá trị cần tìm: Có thể là văn bản, số hoặc giá trị logic. Phạm vi chỉ có thể là 1 hàng hoặc 1 cột. Như đã nói ở trên, các giá trị trong vùng cần tìm phải sắp xếp tăng dần: 0, 1, 2… hoặc theo A, B, C…để hàm trả về giá trị chính xác. Văn bản không phân biệt chữ hoa hay chữ thường.
• Vùng chứa giá trị kết quả: Phạm vi chỉ có thể là 1 hàng hoặc 1 cột và phải có cùng kích cỡ với vùng chứa giá trị cần tìm.

Với ví dụ trên, tại ô F6 ta nhập công thức: =LOOKUP(F4,C4:C10,B4:B10)

Trong đó:

• F4: Là giá trị cần tìm.
• C4:C10: Là vùng chứa giá trị cần tìm.
• B4:B10: Là vùng chưa kết quả trả về.

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ỉ:

• Địa chỉ tương đối: Là địa chỉ bị thay đổi tương ứng với mỗi dòng và cột khi chúng ta thực hiện sao chép công thức. (VD: B5 là địa chỉ của hàng 5 cột B).
• Địa chỉ tuyệt đối: Là địa chỉ được cố định lại, không thay đổi khi ta copy công thức. (VD: $A$1- địa chỉ tuyệt đối của 1 ô, $B$17:$C$20 – địa chỉ tuyệt đối của 1 vùng)
  • Để tạo địa chỉ tuyệt đối, thì bạn nhấn phím F4, lúc này sẽ có dấu đô la ($) ở trước chỉ số cột và dòng.
  • Tóm lại nếu là địa chỉ tuyệt đối thì bạn thấy có dấu đô la ($) trước chỉ số cột và dòng.
• Địa chỉ hỗn hợp: Địa chỉ hỗn hợp là địa chỉ chỉ cố định dòng hoặc cột mà thôi.
  • Cố định cột: Ví dụ: $A1, thì bạn thấy chỉ số cột được cố định, còn chỉ số dòng không được cố định.
  • Cố định dòng: Ví dụ: A$1 thì bạn thấy chỉ số cột không được cố định, còn chỉ số dòng cố định.

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. Hàm VLOOKUP trong Google Sheet 0 0 đánh giá Đánh giá bài viết hàm vlookuphàm VLOOKUP 2 điều kiệnHàm vlookup nâng caoHàm vlookup ngượchàm vlookup nhiều điều kiệnHàm vlookup trong excel