Dùng Excel hoặc Google Sheets đủ lâu, bạn chắc chắn gặp thuật ngữ VLOOKUP. Nó chính xác là gì và làm gì? Trong hướng dẫn này, thảo luận VLOOKUP làm gì, cách dùng để công việc dễ dàng hơn, và hướng dẫn tận dụng tốt nhất trong cả Excel và Google Sheets.
Cũng giải quyết các câu hỏi quanh VLOOKUP và cạm bẫy thường gặp khi dùng lệnh này.
VLOOKUP là gì?
VLOOKUP – viết tắt "vertical lookup" (tra cứu dọc) – là hàm khởi nguồn từ Microsoft Excel cho phép tìm giá trị cụ thể trong một cột, sau đó dùng thông tin đó kéo giá trị khác cùng hàng.
Ví dụ: bạn có ba cột "tên", "số", "địa chỉ", điền đầy thông tin. Dùng VLOOKUP, tìm tên cụ thể trong cột "tên", rồi hiển thị số hoặc địa chỉ tương ứng cùng hàng với tên đó. Nhớ VLOOKUP không phân biệt chữ hoa/thường (not case sensitive).
Mặc dù có vẻ không đặc biệt hữu ích với tập dữ liệu nhỏ như ví dụ, nó rất tiện khi bạn có lượng lớn thông tin trên sheet và muốn dùng giá trị đó ở nơi khác.
Bạn có thể tạo danh sách chính (master list) thông tin ở một sheet, rồi dùng VLOOKUP ở các sheet sau để kéo dữ liệu từ danh sách chính. Cách đó, chỉ cập nhật một sheet, giá trị tự động theo trên các sheet còn lại.
Đơn giản, chuỗi VLOOKUP như sau:
=VLOOKUP( giá_trị_cần_tra, phạm_vi_ô_tìm_kiếm, số_thứ_tự_cột_giá_trị_hiển thị, khớp_chính_xác_hay_xấp_xỉ )
Cách dùng VLOOKUP trong Excel và Google Sheets
Nhìn đầu tiên, chuỗi VLOOKUP có vẻ đáng sợ, nhưng thực tế đơn giản hơn trông, như hướng dẫn chi tiết sẽ chỉ.
- Cần bảng thông tin để tham chiếu. Dùng ý tưởng ví dụ trước, thêm thông tin dưới 3 cột: Tên, Địa chỉ, Số điện thoại.
- Muốn kéo số điện thoại tương ứng của tên nào đó ở trường nhất định. Trường hợp này, kéo số điện thoại cho "Iris Watson".
- Bắt đầu chuỗi bằng double-click ô trống, gõ
=VLOOKUP(. Sẽ thấy giá trị đầu yêu cầu là "lookup_value" – thông tin dùng tìm số điện thoại.
- Vì đã thêm tên "Iris Watson" ở ô trước ô VLOOKUP, ô đó dùng làm lookup_value. Chuỗi hiện tại:
=VLOOKUP(e12,
- Tiếp là table_array (mảng bảng). Là toàn bộ bảng thông tin lấy dữ liệu. Chỉ cần bôi đen toàn bộ bảng dữ liệu. Tại giai đoạn này, chuỗi trông như:
=VLOOKUP(e12,A1:C5,
- Giá trị thứ ba trong chuỗi là "col_index_number" (số chỉ mục cột). Là số thứ tự cột muốn trích xuất thông tin. Trong table_array chọn, "Tên" là 1, "Địa chỉ" là 2, "Số điện thoại" là 3. Vì tìm số điện thoại, chuỗi:
=VLOOKUP(e12,A1:C5,3,
- Cuối cùng, phần cuối chuỗi: khớp chính xác hay xấp xỉ. Gõ TRUE cho xấp xỉ, FALSE cho chính xác. Chọn FALSE. Chuỗi cuối:
=VLOOKUP(E12,A1:C5,3,FALSE)
- Như thấy, VLOOKUP kéo thành công số điện thoại tương ứng cho "Iris Watson".
Lưu ý: hướng dẫn dùng Microsoft Excel làm VLOOKUP, nhưng phương pháp tương tự áp dụng cho Google Sheets.
Cách lọc dữ liệu dùng nhiều tiêu chí
VLOOKUP thiết kế chỉ kéo một giá trị, có hàm khác cho tra cứu nhiều nguồn – không có nghĩa là không dùng VLOOKUP tìm nhiều tiêu chí. Bằng cột phụ (helper column), tạo định danh duy nhất lưu thông tin từ nhiều ô, rồi điều chỉnh VLOOKUP tìm định danh duy nhất đó.
Hữu ích khi nhiều ô cùng giá trị. Ví dụ: bảng có nhiều ô tên "Iris Watson". Bình thường VLOOKUP chỉ kéo "Iris Watson" đầu tiên tìm thấy, nhưng bạn có thể tìm cái khác.
Qua cột phụ, có định danh duy nhất giúp VLOOKUP phân biệt các "Iris Watson" khác nhau.
Ví dụ này, dùng VLOOKUP hiển thị địa chỉ thay vì số điện thoại. Thêm "Iris Watson" thứ hai với địa chỉ và số điện thoại khác.
- Đầu tiên, tạo cột phụ kết hợp ô "Tên" và "Số điện thoại" tạo định danh duy nhất. Dùng hàm CONCATENATE, đơn giản nối chuỗi từ ô khác. Chuỗi:
=CONCATENATE(B2," | ",D2). Thêm ký hiệu pipe"|"và khoảng trắng giữa tên và số điện thoại để dễ hiểu. Muốn biết thêm hàm này, xem bài cách dùng CONCATENATE.
- Sau khi concatenate thành công, click góc dưới phải ô highlight, kéo xuống các ô khác cột phụ. Sẽ áp dụng cùng concatenation cho chúng.
- Cần hai trường cho VLOOKUP. Thêm trường tra cứu "Tên" và "Số" cũng như trường "Địa chỉ" hiển thị kết quả VLOOKUP.
- Muốn VLOOKUP kết hợp thông tin từ trường tra cứu "Tên" và "Số" sao cho khớp định dạng cột phụ. Như vậy VLOOKUP phát hiện định danh duy nhất cột phụ và kéo địa chỉ tương ứng.
Chuỗi ban đầu: =VLOOKUP(F9&" | "&F10,
F9 và F10 là trường tra cứu "Tên" và "Địa chỉ", ký hiệu & hoạt động như concatenate nối hai trường. Phần " | " là cùng dấu phân cách dùng trong concatenation cột phụ.
- Chỉ còn làm theo phần còn lại công thức VLOOKUP. Chọn table_array, nhập số chỉ mục cột, đặt EXACT match. Chuỗi cuối:
=VLOOKUP(F9&" | "&F10,A1:D6,3,FALSE)
- Sẽ thấy không đủ chỉ nhập tên hoặc địa chỉ. Cả hai phải điền để tra cứu thành công. Dưới đây thấy hai kết quả VLOOKUP khác nhau vì hai "Iris Watson" khác nhau. Kết quả khác tùy số điện thoại nhập trường tra cứu thứ hai.
VLOOKUP có tốt hơn INDEX-MATCH không?
Câu hỏi tranh luận gay gắt từ những ngày đầu Excel. Trước khi trả lời, quan trọng hiểu INDEX-MATCH là gì. INDEX và MATCH là hai hàm riêng thường kết hợp tạo hệ thống tra cứu linh hoạt hơn VLOOKUP.
Hàm MATCH dùng khi muốn tìm số vị trí giá trị cụ thể trong phạm vi ô. INDEX, mặt khác, dùng hai định dạng tiềm năng hiển thị giá trị từ table_array hoặc phạm vi ô. Kết hợp cả hai, dùng MATCH xác định số vị trí thông tin cung cấp, rồi INDEX dùng số vị trí đó trả về giá trị.
Cái nào tốt hơn归结为 (boils down to) người dùng hàm. VLOOKUP thân thiện hơn với người mới và người dùng Excel/Google Sheets trung cấp, vì dễ thiết lập và dùng hơn. Tuy nhiên, INDEX-MATCH linh hoạt hơn nhiều và dùng được trong nhiều tình huống rộng hơn.
Cuối ngày, nhiều người biết VLOOKUP hơn INDEX-MATCH, nên VLOOKUP là lựa chọn tốt hơn nếu sheet nhiều người không chuyên dùng. Nhưng nếu sheet dành cho chuyên gia, INDEX-MATCH có lẽ là đường đi.
Những điều nên và không nên khi dùng VLOOKUP
Không có gì xấu hổ khi mắc vài lỗi mới học VLOOKUP. Hầu hết bậc thầy Excel từng trải qua. Đây vài điều nhớ khi thử VLOOKUP.
1. NÊN đảm bảo Lookup_Value là cột đầu tiên trong bảng.
VLOOKUP hoạt động giả định lookup_value ở cột đầu tiên table_array. Chỉ hiển thị thông tin nếu giá trị đó ở ô tiếp theo cùng hàng. Đặt lookup_value nơi khác ngoài đầu, hàm sẽ thất bại.
2. KHÔNG quên dùng FALSE cho khớp chính xác.
Phần cuối chuỗi VLOOKUP cho phép quy định FALSE cho khớp chính xác, TRUE cho khớp gần đúng. Nhiều người nhầm dùng TRUE dẫn kết quả sai, hoặc quên không đặt giá trị.
3. NÊN kiểm tra kỹ số chỉ mục cột (column index number).
Kết quả VLOOKUP phụ thuộc lớn vào việc bạn đặt đúng "col_index_num" khi gõ chuỗi. "col_index_num" là giá trị số đặt cho mỗi cột trong table_array. Cột đầu = 1, cột hai = 2, v.v. Nhập sai "col_index_num", hàm hiển thị kết quả hoàn toàn khác.
4. NÊN dùng F4 khi copy công thức sang ô khác.
Một trong những điều hữu ích nhất VLOOKUP: kéo công thức xuống copy cho các ô. Vấn đề: giá trị trong chuỗi hàm cũng dịch chuyển xuống, phá vỡ công thức. Để ngăn chặn, đặt con trỏ trên giá trị trong công thức và nhấn F4. Chuyển thành giá trị tuyệt đối (absolute) không dịch khi copy công thức.
Lỗi VLOOKUP thường gặp và cách khắc phục
Lỗi phổ biến nhất khi dùng VLOOKUP là lỗi "#NA", tuy nhiên lưu ý có nhiều lý do gây lỗi này.
1. Lookup_value không ở cột đầu tiên table_array.
Một trong những hạn chế lớn VLOOKUP: chỉ tìm giá trị trong cột đầu tiên table_array. Nếu lookup_value không ở đó, ra lỗi #NA. Khắc phục: điều chỉnh công thức tham chiếu cột khác, hoặc di chuyển cột để lookup_value ở đúng chỗ.
2. VLOOKUP không tìm thấy khớp chính xác.
Giá trị cuối công thức VLOOKUP là range_lookup argument: TRUE cho khớp gần đúng, FALSE cho chính xác. Nếu đặt FALSE mà VLOOKUP không tìm thấy khớp chính xác, ra lỗi #NA.
Nếu chắc chắn phải có khớp trong lookup_value, kiểm tra lại thông tin table_array đảm bảo định dạng đúng và không có khoảng trắng thừa. Ký tự không in (non-printing characters) cũng cản VLOOKUP tìm đúng.
3. Số dấu phẩy động quá lớn (Excessively large floating point numbers)
Số dấu phẩy động là số có phần thập phân. Với VLOOKUP, nếu số có quá nhiều chữ số sau dấu phẩy, ra lỗi #NA. Sửa dễ dàng: làm tròn số tối đa 5 chữ số thập phân, sẽ chạy. Dùng hàm ROUND.
Câu hỏi thường gặp
1. Điều gì xảy ra khi để trống đối số range_lookup?
Đối số thứ tư chuỗi VLOOKUP – nên đặt "TRUE" hoặc "FALSE" – được coi là tùy chọn. TRUE = tìm khớp gần đúng, FALSE = yêu cầu khớp chính xác. Vấn đề: nếu để trống, VLOOKUP mặc định "TRUE", có thể làm hỏng kết quả mong muốn.
2. Các lựa chọn thay thế VLOOKUP?
Lựa chọn tốt nhất thay VLOOKUP là combo INDEX-MATCH. Tuy nhiên, kết hợp này khó học hơn vì phải thành thạo hai hàm khác nhau và đảm bảo chúng phối hợp tốt.
3. Có tìm giá trị theo hàng thay vì cột không?
Có. Dùng HLOOKUP hoặc "horizontal lookup" (tra cứu ngang) trong Excel và Google Sheets. Hàm này tra giá trị từ hàng cụ thể, hiển thị giá trị từ hàng khác nhưng cùng cột.
Muốn giới hạn tìm kiếm trong một hàng hoặc cột đơn lẻ, dùng hàm LOOKUP.