Bài viết: VLOOKUP với Cột động

[kyo]

VLOOKUP với Cột động

Bạn cần phải xử lý bảng dữ liệu lớn. Bạn muốn sử dụng hàm VLOOKUP nhưng phải tự động cập nhật vị trí các cột mỗi khi bạn Copy - Paste dữ liệu vào. Hàm VLOOKUP với hàm COLUMNS là những hàm bạn cần lúc này. Điều này có nghĩa là: đối số col_index_num của hàm VLOOKUP sẽ tự động cập nhật khi bạn thêm cột dữ liệu vào bảng tính.
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Ta có ví dụ về top 10 cuốn sách bán chạy nhất hiện tại từ Tiki:

Hãy tưởng tượng trong danh sách này là hàng trăm, hoặc thậm chí là hàng ngàn, dài thiệt dài và bạn muốn tìm 1 cách nhanh chóng để có được thông tin giá bán và tác giả theo yêu cầu (tất nhiên trừ cách tìm CTRL + F)
Tạo 1 bảng khác, trong đó có Tựa sách, Tác giả và Giá bán:

Tựa sách được tìm thấy bằng cách sử dụng Data Validation với lựa chọn là List. Lập tức sẽ có 1 danh sách các Tựa sách được thả xuống như bạn thấy trong hình. Tiếp đến, bạn cần tìm Tác giả và Giá bán tương ứng.

Bạn có thể làm việc này bằng cách dùng hàm VLOOKUP bình thường như thế này :
Tại ô C16 : =VLOOKUP($B16,$B$4:$D$13,2,FALSE)
Và tại ô D16 : =VLOOKUP($B16,$B$4:$D$13,3,FALSE)

Nhưng bạn cần phải tự đếm số thứ tự của cột và chỉnh sửa cho khớp mỗi lần Copy - Paste hàm VLOOKUP. Giả sử có rất nhiều cột bạn muốn tìm kiếm dữ liệu, thay vì phải mò mẫm mã hóa từng số tham chiếu cột, sẽ nhanh hơn nhiều nếu sử dụng hàm COLUMNS như sau :
Tại ô C16 : =VLOOKUP($B16,$B$4:$D$13,COLUMNS($B4:B4)+1,0)
Và tại ô D16 : =VLOOKUP($B16,$B$4:$D$13,COLUMNS($B4:C4)+1,0)

Chú ý: Hàm COLUMNS và hàm COLUMN có cách hoạt động khác nhau.

ĐIỀU GÌ ĐÃ XẢY RA VỚI HÀM COLUMNS?
Hàm COLUMNS sẽ trả về kết quả là số các cột trong mảng.
Cú pháp : =COLUMNS(array) với "array” là vùng cột.
Ví dụ: =COLUMNS(B4:B4) cho chúng ta kết quả là 1, tức mảng rộng 1 cột.

Vì vậy, sử dụng hàm COLUMNS nếu bạn cần đếm số cột.
Chú ý : B4:B4 còn có cách dễ dàng hơn, là B:B: =COLUMNS(B:B), vì nó không thực sự quan trọng, do bạn không cần đến tham chiếu dòng.

SỬ DỤNG HÀM COLUMNS TRONG VLOOKUP
Trong hàm VLOOKUP ở hình trên (tại ô C16), hàm COLUMNS được sử dụng như sau : COLUMNS($B4:B4)+1, vì :
- Cột chúng ta muốn đếm là cột thứ 2 trong bảng tính.
- COLUMNS($B4:B4) sẽ cho giá trị là 1, chúng ta cần thêm 1 để có được 2 cho cột thứ 2.

Trong hàm VLOOKUP tại ô D16, hàm COLUMNS có cú pháp sau : COLUMNS($B4:C4)+1 và cho giá trị là : 2 + 1 = 3
Với cú pháp trên, bạn đã sử dụng địa chỉ tuyệt đối cho $B4:C4 để khi Copy hàm VLOOKUP từ C16 đến D16, hàm COLUMNS sẽ tự động tăng thêm 1 (từ B4:B4 đến B4:C4) và bạn sẽ có số cột chính xác.

SỬ DỤNG HÀM MATCH THAY CHO HÀM COLUMNS:
Có 1 cách khác để tạo tham chiếu cột động đó là sử dụng hàm INDEX và hàm MATCH. Bạn có thể xem thêm hàm INDEX và hàm MATCH trong diễn đàn.

HÀM HLOOKUP VỚI ROWS :
Tương tự như cách sử dụng hàm VLOOKUP và COLUMNS, ta có thể sử dụng hàm ROWS trong hàm HLOOKUP để đạt được hiệu quả tương tự, nhưng không phải là số cột, mà là số dòng nhé.
Cú pháp của hàm ROWS : =ROWS(array) với "array” là vùng dòng.
Ví dụ: =ROWS(B4:B4), kết quả là 1, tức mảng cao 1 dòng.

Chúc các bạn thành công.

Nguyễn Bảo Khanh.

Một số bài viết có liên quan:
1/ 23 điều bổ ích về VLOOKUP có thể bạn muốn biết (phần 2)
2/ 23 điều bổ ích về VLOOKUP có thể bạn muốn biết (phần 1)
3/ Cách viết hàm hiệu quả
4/ Ebook: Công thức và hàm Excel 97-2013
5/ 29 cách tiết kiệm thời gian với các công thức Excel (phần 3)
6/ 29 cách tiết kiệm thời gian với các công thức Excel (phần 2)
7/ 29 cách tiết kiệm thời gian với các công thức Excel (phần 1)
8/ Tra cứu hàm theo tên A-Z
9/ Hàm Dò tìm và Tham chiếu
10/ Chiêu thứ 13: Sao chép công thức giữ nguyên tham chiếu tương đối

 

[tucuulobi]

Bài viết rất hữu ích. Cảm ơn bạn rất nhiều!