Hướng dẫn hàm vlookup nâng cao

Chia sẻ bài viết tới bạn bè

Hàm Vlookup là một hàm sử dụng rất phổ biến và có nhiều ứng dụng khác nhau. Vlookup càng phát huy sức mạnh hơn nữa khi kết hợp với các hàm khác. Trong bài viết này ADO sẽ giới thiệu với các bạn những kiến thức về hàm vlookup nâng cao mà không phải ai cũng biết.

Nhưng trước hết nếu bạn còn chưa biết hàm Vlookup và những kiến thức cơ bản về hàm này thì hãy tìm hiểu ở bài viết Hàm VLOOKUP trong excel cơ bản mà ADO đã giới thiệu.

Ôn tập kiến thức về hàm VLOOKUP một chút

Phần này chúng ta chỉ lướt qua nhanh phần cơ bản nhất của hàm Vlookup đó là cú pháp hàm.

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Trong đó:

• Lookup_value: Giá trị dùng để dò tìm; Có thể nhập trực tiếp hoặc tham chiếu tới một ô
• Table_array: Phạm vị giá trị bảng tính chứa giá trị dò tìm và giá trị trả về
• Col_index_num: Số thứ tự của cột chứa giá trị trả về của hàm trong phạm vi bảng tính được chọn, tính từ cột đầu tiên từ trái sang
• Range_lookup: Kiểu tìm kiếm chính xác (2- False) hoặc tương đối (1- True). Đây là đối số không bắt buộc, nếu bỏ qua thì mặc định sẽ là lựa chọn 1.

Hàm VLOOKUP 2 điều kiện

Ở mức cơ bản ta biết hàm Vlookup là hàm tìm kiếm dữ liệu từ một bảng, vùng tham chiếu dữa vào 1 dữ liệu dò tìm có trước. Như vậy hàm Vlookup chỉ có thể dò tìm từ 1 ô tham chiếu và trả về kết quả một giá trị hay một chuỗi ký tự.

Vậy để dùng hàm Vlookup dò tìm dựa trên hai hay nhiều điều kiện thì phải làm thế nào?

Chúng ta sẽ có 2 cách thức để triển khai với hàm Vlookup 2 điều kiện đó là:

• Dùng cột phụ tổng hợp điều kiện: Phương pháp này dễ tư duy, dễ thực hiện hơn, nhưng bạn phải tạo thêm 1 cột phụ. Trong trường hợp chúng ta không muốn dùng cột dữ liệu thêm hoặc yêu cầu không được chèn thêm bất cứ dữ liệu nào thì bạn phải sử dụng cách khác.
• Dùng công thức mảng: Phương pháp thứ 2 chính là phương pháp giúp bạn đạt được kết quả mà không cần phải thêm, chèn bất cứ dữ liệu gì. Tuy nhiên đây lại là cách nâng cao hơn một chút. Đòi hỏi bạn phải nắm vững kiến thức về hàm và công thức mảng.

Đầu tiên chúng ta sẽ cùng thao tác với phương pháp thứ nhất.

via GIPHY

Ở ứng dụng trên các bạn lưu ý, chúng ta cần tìm số lượng của các sản phẩm cụ thể ví dụ Kindle PPW 2 Black. Như vậy đối tượng cần tìm có 2 đặc điểm. Đầu tiền là loại sản phẩm, thứ hai là đặc trưng.

Vậy chúng ta sẽ tạo cột phụ cho bảng dữ liệu.

Tuy nhiên để có thể sử dụng hàm Vlookup thì các bạn biết rằng dữ liệu dò tìm bắt buộc phải nằm ở cột đầu tiên của vùng chọn. Chính vì vậy chúng ta sẽ chèn một cột ngay trước cột Product.

Tiếp theo các bạn sẽ nối ký tự ở hai cột Product và Feature với nhau. Tại đây có thể dụng cách đơn giản nhất giống như trong phần mình hoà là nối các chuỗi bằng dầu &. Hoặc các bạn có thể dùng hàm Concatinate.

Tiếp theo bạn chỉ cần áp dụng công thức Vlookup thông thường cho vùng dữ liệu từ B1 tới E8:

=VLOOKUP(D11&” “&D12,$B$1:$E$8,4,0)

• D11&” “&D12 chính là công thức nối để ra dữ liệu dò tìm là : Kindle PPW 2 Black
• 4 là số thứ tự cột chứa giá trị trả về

Hàm VLOOKUP với mảng dữ liệu

via GIPHY

Đối với trường hợp bạn không muốn dùng cột phụ thì có thể sử dụng công thức mảng kết hợp trong hàm Vlookup có nhiều điều kiện.

Ở đây chúng ta sẽ vừa sử dụng công thức mảng vừa kết hợp với hàm CHOOSE lồng bên trong. Nếu các bạn chưa biết hàm này là gì thì trước tiên hãy tìm hiểu Hàm CHOOSE trong excel để hiểu chức năng cụ thể.

Hàm choose sẽ trả về kết quả là giá trị từ một danh sách dựa theo số thứ tự của nó. VD, =CHOOSE(2,”Học”,”Viện”,”ADO”) sẽ trả về kết quả là “Viện”.

Nâng cấp lên 1 chút, khi dùng hàm CHOOSE ở dạng công thức mảng, chúng ta sẽ chọn một mảng dữ liệu chữ không phải 1 giá trị.

Ở đây trong công thức =CHOOSE({1,2},$B$2:$B$8&” “&$C$2:$C$8,$D$2:$D$8 nghĩa là sẽ chọn mảng gồm 2 cột mà nó vừa ghép lại tạo thành như trong hình ảnh bên dưới.

Phần hình ảnh bên trên chính là hàm Choose khi sử dụng công thức mảng. Bạn quan sát trên thanh công thức sẽ thấy có cặp dấu ngoặc nhọn bao bên ngoài hàm. Đó là ký hiệu của công thức mảng, khi bạn hoàn thành công thức và ấn CTRL+SHIFT+ENTER. Excel sẽ tự động hiển thị thêm cặp dấu đó trên thanh công thức.

Mặt khác, ô B3 chính là ô gõ công thức, tuy nhiên vì là mảng dữ liệu nên nó không thể hiển thị hết trong một ô. Để nhìn rõ hơn cách hàm hoạt động, bạn chỉ cần bôi đen toàn bộ công thức trên thanh công cụ và ấn F9 hoặc Fn+F9 để xem công thức chạy như thế nào.

Ảnh bên dưới bạn sẽ nhìn thấy phần diễn giải cụ thể. Mỗi một cặp ngăn cách bởi dấu phẩy nghĩa là thuộ 2 ô trên cùng 1 dòng. Dấu chấm phẩy thể hiện dữ liệu tiếp theo thuộc dòng bên dưới.

Như vậy ở đây có tổng cộng 7 cặp tương ứng 7 dòng và 2 cột. Nó được thể hiển minh hoạ bằng bảng mà ADO tạo ở bên cạnh.

Như vậy khi sử dụng công thức:

=VLOOKUP(C11&” “&C12,CHOOSE({1,2},$B$2:$B$8&” “&$C$2:$C$8,$D$2:$D$8),2,0)

Nghĩa là chúng ta đang dò tìm dữ liệu trong phạm vị bảng mà hàm CHOOSE vừa tạo ra. Và kết quả trả về là giá trị 21 tương ứng trong cặp ở vị trí số 5.

Hàm VLOOKUP kết hợp với hàm INDIRECT

Ứng dụng hàm Vlookup nâng cao kết hợp với INDIRECT vô cùng mạnh mẽ. Tuy nhiên  nếu bạn chưa hiểu về hàm này thì nên tìm hiểu rõ hơn về cách sử dụng hàm INDIRECT trong excel.

Trong ứng dụng này chúng ta có 2 sheet dữ liệu. Một sheet là báo cáo tập trung còn sheet thứ hai là nơi lưu trữ các bảng báo cáo của các dịch vụ riêng biệt.

Bây giờ làm thế nào để chỉ cần 1 lần sử dụng công thức chúng ta có thể trả về kết quả số lượng của tất cả các mã Class khác nhau thuộc các nhóm khác nhau. Bạn cũng có thể ứng dụng trong trường hợp mỗi bảng nhỏ trong sheet2 thuộc một sheet khác.

Trước tiên, bạn cần thao tác 1 bước đó chính là đặt tên cho các bảng nhỏ này.

via GIPHY

Việc đặt tên bảng để giúp chúng ta dễ dàng hơn khi sử dụng hàm INDIRECT để kéo ra mảng dữ liệu với điều kiện cho trước.

Ở đây chúng ta đặt tên lần lượt là EX,WO,PRE,SHO. Lý do chúng ta đăt tên như vậy là vì, tất cả các mã trong cùng một nhóm đều có dạng: “Tiền tố”-“Mã số”. Ví dụ, EX-101.

Vì vậy chúng ta lấy luôn phần tiền tố đầu tiên là tên của bảng để sử dụng.

Tiếp theo chúng ta sẽ kết hợp 2 hàm chính trong trường hợp này là hàm VLOOKUP và hàm INDIRECT. Bên cạnh đó còn lồng thêm hàm LEFT và hàm SEARCH bên trong.

Hai hàm LEFT và SEARCH rất thường xuyên đi cùng với nhau. Nếu bạn chưa biết về ứng dụng kết hợp này thì có thể tìm hiểu kỹ hơn TẠI ĐÂY.

via GIPHY

Để các bạn dễ hình dùng hơn vì sao hàm có thể cho kết quả chính xác và cách hàm chạy thế nào thì hãy cùng xem phần diễn giải bên dưới.

via GIPHY

Các bạn lưu ý đối với hàm INDIRECT là hàm tham chiếu và tìm kiếm, nó sẽ trả về kết quả là một mảng dữ liệu. Nên các bạn sẽ nhìn thấy trong ô sử dụng hàm INDIRECT sẽ hiện lỗi #VALUE. Bởi vì kết quả trả về đang là một vùng nhiều ô, trong khi chỉ hiển thị trong 1 ô excel.

Để rõ hơn, bạn hãy chọn vào phần công thức trên thanh công thức, bôi đen toàn bộ và ấn F9 để xem kết quả chính xác.

Như vậy bạn đã được tìm hiểu lần lượt những ứng dụng hàm VLOOKUP nâng cao vô cùng hay và thực tế. Với những ứng dụng nâng cao bạn không chỉ hiểu về Vlookup mà có nắm vững cả về những hàm khác trong excel để có thể tuỳ biến, kết hợp linh hoạt. Nếu các bạn thấy còn bối rối với những ví dụ cụ thể trên thì hãy bình luận ngay trong bài viết này để ADO sẽ cùng bạn giải đáp nhé!

Chia sẻ bài viết tới bạn bè