Cách dùng hàm Vlookup nâng cao

Hàm Vlookup trong Excel là một hàm dò tìm giá trị phổ biến và được sử dụng rộng rãi. Tuy nhiên, nếu sử dụng đơn thuần hàm Vlookup, bạn sẽ không tận dụng hết tính năng của hàm này. TinoHost sẽ hướng dẫn bạn cách dùng hàm Vlookup nâng cao bằng cách kết hợp với nhiều hàm khác trong bài viết này nhé.

Giới thiệu về hàm Vlookup

Hàm Vlookup là gì?

Hàm Vlookup trong Excel có chức năng cho phép người dùng tìm kiếm giá trị trong một vùng dữ liệu đã cho trước và sau đó xuất ra giá trị thích hợp tương ứng.

Hàm Vlookup nâng cao là một phiên bản được cải tiến từ hàm Vlookup cơ bản với nhiều tính năng nổi trội hơn bằng cách kết hợp với những hàm khác.

Công thức của hàm Vlookup

Cú pháp của hàm Vlookup trong excel sẽ được viết như sau: = VLOOKUP(LOOKUP_VALUE, TABLE_ARRAY, COL_INDEX_NUM, [RANGE_LOOKUP] )

Cú pháp trên bao gồm các thành phần như sau:

• LOOKUP_VALUE: Giá trị bạn cần tìm, giá trị này có thể là ô tham chiếu, một giá trị hoặc chuỗi văn bản. Đây là giá trị bắt buộc phải có trong lệnh hàm.
• TABLE_ARRAY: Bảng giá trị chứa dữ liệu tìm kiếm thường gồm có hai cột giá trị trở lên. Đây cũng là yếu tố bắt buộc phải có trong lúc viết hàm
• COL_INDEX_NUM: Số thứ tự của cột chứa giá trị trong bảng giá trị
• RANGE_LOOKUP (tuỳ chọn): Một phương pháp dò tìm logic cho biết hàm VLOOKUP cần phải tìm kết quả chính xác hay tương đối.

Các kết quả có thể xảy ra:

• Nếu bạn dùng TRUE hoặc bỏ qua, kết quả khớp tương đối được trả về. Điều này có nghĩa là nếu kết quả tìm kiếm khớp chính xác không được tìm thấy, hàm Vlookup sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn lookup value. Ngoài ra, bạn còn có thể quy ước “1” là dò tìm tương đối
• Nếu bạn dùng FALSE, chỉ kết quả khớp chính xác được trả về. Nếu không giá trị nào trong cột giá trị 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. Ngoài ra, bạn còn có thể quy ước “0” là dò tìm tuyệt đối

Cách dùng Vlookup nâng cao trong Excel

Cách dùng hàm Vlookup nâng cao với nhiều điều kiện

Hàm Vlookup với nhiều điều kiện chính là hàm Vlookup thông thường, nhưng trong trường hợp này chúng 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 ứng từng ca. Vậy bạn phải dùng cách 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?

Với bài toán bên trên, bạn ta tìm sản lượng của từng sản phẩm trong từng ca (có nghĩa là có hai đ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 bạn phải chuyển hai điều kiện đầu thành một đ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 lại với nhau.

QUẢNG CÁO

Các bước thực hiện chi tiết như sau:

Bước 1: Tạo cột phụ:

Bạn 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.

Bạn viết công thức ghép như: [Sản phẩm]&[Ca]

Với hàng đâu tiền thì công thức sẽ là: G2&G3

Sau khi tạo công thức cho hàng đầu, bạn copy công thức đó cho các hàng tiếp theo để hoàn thành cột.

Bước 2: Viết công thức tìm kiếm kết hợp hàm Vlookup với giá trị trong cột phụ

Để tìm kiến sản lượng của 1 sản phẩm theo ca tại ô G4 bạn soạn công thức như sau: = VLOOKUP(G2&G3,$A$3:$D$9,4,0)

Trong đó:

• G2&G3: Là giá trị cần tham chiếu
• $A$3:$D$9: Là bảng dữ liệu chứa giá trị 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 tuyệt đối

Bạn có thể 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.

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ẽ dòm tìm giá trị sang bên phải để lấy giá trị tương ứng và cột tìm kiếm phải đượ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ọc giá trị tương ứng thì hàm Vlookup là biện pháp không khả thi.

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 tên vùng. Chúng ta cần tìm mã vùng của tỉnh thành cho trước thì phải làm sao?

Chúng ta có thể thấy trong hình, 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 đó trong trường hợp này chúng phải dùng Lookup trong Excel để lấy mã vùng của tỉnh thành cho trước.

Công thức của hàm Lookup được diễn giải: =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ả)

Công thức bao gồm các thành phần:

• Giá trị cần tìm: Giá trị này 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: Giá trị trong vùng này có thể là văn bản, số hoặc giá trị logic. Ta nên chú ý phạm vi dữ liệu chỉ có thể là 1 hàng hoặc 1 cột.

Như đã nói ở trên, các giá trị trong vùng chứa 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 . Các ký tự trong 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ả: Vùng giá trị này 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.

Trong ví dụ trên, tại ô F6 ta nhập công thức: = LOOKUP(N2,K3:K9,J3:J9)

Trong đó:

• N2: Là giá trị cần tìm.
• K3:K9: Là vùng chứa giá trị cần dò tìm.
• J3:J9: Là vùng chứa kết quả truy xuất .

Sau khi ta nhập xong công thức ta sẽ có được kết quả truy xuất như trong hình bên dưới.

Ứng dụng hàm Vlookup nâng cao cho dân văn phòng

Sau đây là một ví dụ thực tiễn mà bạn có thể áp dụng hàm Vlookup để giải quyết công việc như quản lý số liệu bán hàng, quản lý mã đơn hàng.

Trong ví dụ này, chúng ta sẽ có hai bảng dữ liệu cần xử lý bao gồm: bảng doanh thu bán quần áo và bảng thông tin hàng hóa.

Để hoàn thiện các cột trống như Tên hàng, Thành tiền, Chuyên chở, Tổng tiền. Chúng ta sử dụng hàm Vlookup kết hợp với một số hàm khác.

* Chúng ta có thể hoàn thành cột tên hàng với dữ liệu cho trước bằng cách sử dụng hàm Vlookup kết với hàm Left.

Chúng nhập vào ô B3 với công thức là = VLOOKUP(LEFT(A3,2),$H$3:$I$6,2,0)

Trong trường hợp này, chúng ta dùng hàm Left để lọc ra 2 ký tự mã hàng từ bảng doanh thu bán quần áo để tham chiếu cột tên hàng ở bảng thông tin hàng hóa. Tiếp đến, ta copy công thức xuống các hàng dưới để hoàn thành cả cột.

* Tiếp đến, chúng ta hoàn tất dữ liệu cột thành tiền bằng cách sử dụng tổ hợp 3 hàm Vlookup, If và Mid.

Chúng ta đều hiểu rằng cột thành tiền sẽ được tính như sau: Thành tiền = Số lượng * Đơn giá . Dữ liệu đơn giá bao gồm giá bán sỉ và giá bán lẻ đã được cho trước bên bảng thông tin hàng hóa. Mã đơn hàng quy định giá bản sỉ và giá bán lẻ là ký tự S-L, Trong trường hợp này chúng ta dùng hàm Mid, bởi vì hàm này hỗ trợ cắt ký tự ở giữa một sâu ký tự cho trước.

Chúng ta việt lệnh hàm toán vào ô D3 với công thức: = C3*VLOOKUP(B3,$I$3:$K$6,IF(MID(A3,3,1)= “L”,3,2),0).

Phần đơn giá, chúng ta có thể diễn giải như sau: Chúng ta rà soát đơn giá dành cho quần dài trong bảng thông tin hàng hóa, nếu ký tự ở trong mã hàng có chứa chữ L thì kết quả tra cứu ở cột thứ 3 tương ứng với giá bán lẻ, còn không thì là cột thứ 2 tương ứng với giá bán sỉ.

* Cột chuyên chở được tính đơn thuần như sau: Chuyên chở = Thành tiền *Phần trăm chuyên chở . Phần phần trăm chuyên chở của từng mặt hàng dựa vào 2 ký tự đầu tiền bên phải của mã hàng trong bảng thông tin hàng hóa.

Chúng áp dụng công thức để tính là: = D3*VLOOKUP(VALUE(RIGHT(A3,1)),$L$3:$M$6,2,0)

Vì là ô mã hàng A3 là một chuỗi ký tự nhưng bên mã chuyên chở lại là một số nên hệ thống sẽ không hiểu và kết quả sẽ bị lỗi. Vì vậy, chúng ta hàm Value vào hàm Right để đưa dữ liệu về giá trị số.

Cuối cùng, cột tổng tiền thì quá đơn giản rồi. Chúng ta chỉ cần cộng giá trị của hai thành tiền và chuyên chở lại với nhau là hoàn tất.

Công thức tính toán như sau: = E3 + D3

Trên đây là ví dụ những trường hợp thực tiễn mà bạn có thể áp dụng hàm Vlookup nâng cao để xử lý công việc.

Như vậy, bạn có thể thấy rằng hàm Vlookup chính là một công cụ đắc lực giúp giải quyết các công việc văn phòng cụ thể là các công việc phải xử lý nhiều số liệu. Vì vậy, bạn hãy tập sử dụng hàm Vlookup để có thể năng suất công việc được hiệu quả hơn.

FAQs về cách dùng hàm Vlookup nâng cao

Mọi thắc mắc và góp ý liên quan, xin vui lòng liên hệ ngay TinoHost để được tư vấn chi tiết hoặc Fanpage để cập nhật những thông tin mới nhất nhé!

Với bề dày kinh nghiệm hơn 5 năm cung cấp hosting, dịch vụ cho thuê máy chủ, các dịch vụ liên quan đến tên miền và bảo mật website, hãy để TinoHost đồng hành cùng bạn trên con đường khẳng định thương hiệu trên bản đồ công nghệ toàn cầu!