Hướng dẫn VLOOKUP Excel cho người mới bắt đầu
Charlotte Miller
⚡ Tóm tắt thông minh
Hướng dẫn sử dụng hàm VLOOKUP trong Excel giải thích cách hàm tra cứu theo chiều dọc tìm kiếm trong cột đầu tiên của bảng và trả về giá trị phù hợp từ một cột khác. Hướng dẫn này bao gồm cú pháp, kết quả khớp chính xác và gần đúng, tra cứu chéo trang tính, các lỗi thường gặp và hàm thay thế XLOOKUP hiện đại.
VLOOKUP là gì?
VLOOKUP (chữ V viết tắt của Vertical - dọc) là một hàm tích hợp sẵn trong Excel thiết lập mối quan hệ giữa các cột trong bảng tính. Nó cho phép bạn tra cứu một giá trị trong một cột và trả về giá trị tương ứng từ một cột khác trong cùng một hàng.
Cú pháp và đối số của hàm VLOOKUP
Trước khi sử dụng hàm VLOOKUP, việc hiểu cấu trúc công thức là rất hữu ích. Hàm này nhận bốn tham số và tuân theo một cấu trúc nhất quán trong mọi phiên bản Excel.
- lookup_value — Giá trị bạn muốn tìm (tham chiếu ô hoặc giá trị cố định).
- table_array — Phạm vi ô chứa cột tra cứu và cột trả về.
- col_index_num — Số thứ tự cột trong table_array mà từ đó trả về giá trị (1 là cột ngoài cùng bên trái).
- phạm vi_lookup — FALSE nếu khớp chính xác, TRUE (hoặc bỏ qua) nếu khớp gần đúng trên dữ liệu đã được sắp xếp.
Quan trọng: Giá trị cần tìm phải nằm ở cột ngoài cùng bên trái của mảng table_array, và hàm VLOOKUP chỉ tìm kiếm từ trái sang phải.
Cách sử dụng VLOOKUP
Khi cần tìm kiếm thông tin cụ thể trong một bảng tính lớn, hoặc cần truy xuất cùng một loại giá trị nhiều lần, hàm VLOOKUP giúp tiết kiệm đáng kể thời gian so với việc lọc thủ công.
Xem xét một Bảng lương công ty Được duy trì bởi nhóm tài chính. Bạn bắt đầu với một thông tin đã biết — một chỉ mục — và sử dụng VLOOKUP để truy xuất giá trị chưa biết.
Ví dụ, bạn đã biết Tên nhân viên:
Và bạn muốn tra cứu mức lương của nhân viên:
Bảng tính Excel cho ví dụ trên:
Để tìm mức lương của nhân viên chưa biết, chúng ta nhập thông tin nhân viên. Code Điều đó đã có sẵn rồi.
Bằng cách sử dụng hàm VLOOKUP, giá trị lương tương ứng với nhân viên đó sẽ được tìm thấy. Code Xuất hiện tự động.
Cách sử dụng hàm VLOOKUP trong Excel
Hãy làm theo hướng dẫn từng bước này để sử dụng hàm VLOOKUP trong Excel:
Bước 1) Điều hướng đến ô đích
Nhấp chuột vào ô mà bạn muốn hiển thị mức lương của nhân viên đã chọn — trong ví dụ này, đó là ô H3.
Bước 2) Nhập hàm VLOOKUP =VLOOKUP()
Nhập hàm vào ô. Bắt đầu bằng dấu bằng (để báo cho Excel biết có công thức theo sau) và sau đó là từ khóa VLOOKUP: =VLOOKUP().
Dấu ngoặc đơn chứa tập hợp các đối số (các dữ liệu mà hàm cần).
VLOOKUP yêu cầu bốn đối số:
Bước 3) Tham số đầu tiên — giá trị tra cứu
Tham số đầu tiên là tham chiếu ô chứa giá trị bạn muốn tìm kiếm. Trong trường hợp này, đó là trường Nhân viên. Code là giá trị cần tra cứu, vì vậy đối số đầu tiên là H2 — ô mà Excel cần khớp nội dung.
Bước 4) Đối số thứ hai — mảng bảng
Điều này đề cập đến khối giá trị cần tìm kiếm, được gọi trong Excel là mảng bảng hoặc bảng tra cứu. Trong ví dụ của chúng tôi, bảng tra cứu chạy từ B2 đến E25.
LƯU Ý: Cột tra cứu phải là cột ngoài cùng bên trái trong mảng bảng của bạn.
Bước 5) Tham số thứ ba — col_index_num
Điều này cho VLOOKUP biết cột nào trong mảng bảng chứa giá trị trả về. Lương nhân viên nằm ở cột thứ tư, vì vậy chỉ số cột là 4.
Bước 6) Luận điểm thứ tư — sự trùng khớp chính xác hoặc gần đúng
Tham số cuối cùng là cờ tra cứu phạm vi. Nó kiểm soát việc hàm VLOOKUP trả về kết quả khớp chính xác hay gần đúng. Ở đây chúng ta muốn kết quả khớp chính xác (FALSE).
- KHÔNG ĐÚNG — Hoàn toàn trùng khớp.
- TRUE — gần đúng.
Bước 7) Nhấn Enter
Nhấn Enter để hoàn thành công thức. Ban đầu bạn sẽ thấy lỗi vì không có Nhân viên nào. Code Đã được đưa vào H2.
Sau khi bạn nhập thông tin Nhân viên hợp lệ Code Trong ô H2, ô này hiển thị mức lương tương ứng của nhân viên.
Tóm lại, công thức này cho Excel biết rằng các giá trị đã biết nằm ở cột ngoài cùng bên trái của dữ liệu (Nhân viên). CodeHàm VLOOKUP sau đó quét bảng và trả về giá trị ở cột thứ tư trên hàng khớp — đó là Lương của nhân viên.
Ví dụ này đề cập đến các kết quả khớp chính xác (từ khóa FALSE). Phần tiếp theo sẽ giải thích về các kết quả khớp gần đúng.
VLOOKUP cho các kết quả khớp gần đúng (Từ khóa TRUE là tham số cuối cùng)
Hãy xem xét một tình huống trong đó một bảng tính toán mức giảm giá cho những khách hàng không mua chính xác hàng chục hoặc hàng trăm mặt hàng.
Như hình minh họa bên dưới, một công ty áp dụng chiết khấu cho số lượng từ 1 đến 10,000:
Khách hàng hiếm khi mua chính xác 100 hoặc 1,000 đơn vị. Chế độ tìm giá trị gần đúng cho phép hàm VLOOKUP tìm giá trị thấp hơn gần nhất thay vì yêu cầu con số chính xác. Các bước:
Bước 1) Nhấp chuột vào ô mà bạn muốn sử dụng hàm VLOOKUP — ô tham chiếu I2.
Bước 2) Nhập =VLOOKUP() vào ô và thêm các đối số bên trong dấu ngoặc đơn.
Bước 3) Luận điểm 1: Nhập số tham chiếu ô cần so khớp giá trị với bảng tra cứu.
Bước 4) Luận điểm 2: Chọn bảng tra cứu — ở đây là các cột Số lượng và Chiết khấu.
Bước 5) Luận điểm 3: Nhập chỉ số cột trong bảng tra cứu để tìm giá trị phù hợp.
Bước 6) Luận điểm 4: Đặt đối số cuối cùng thành TRUE để tìm các kết quả phù hợp gần đúng.
Bước 7) Nhấn Enter. Công thức giờ đã được áp dụng cho ô. Khi bạn nhập bất kỳ số lượng nào, Excel sẽ trả về mức chiết khấu dựa trên sự trùng khớp gần đúng.
LƯU Ý: Nếu bạn để trống đối số thứ tư, Excel sẽ mặc định là TRUE (khớp gần đúng). Đối với các kết quả khớp gần đúng, cột tìm kiếm phải được sắp xếp theo thứ tự tăng dần.
Hàm Vlookup được áp dụng giữa 2 sheet khác nhau đặt trong cùng một sổ làm việc
Bây giờ hãy xem xét một bảng tính có hai trang tính. Trang tính 1 liệt kê Nhân viên. CodeTên và chức danh; Trang 2 liệt kê Nhân viên Code và Lương nhân viên.
BẢNG 1:
BẢNG 2:
Mục tiêu là tổng hợp tất cả dữ liệu trên Trang tính 1, như hình bên dưới:
VLOOKUP có thể tổng hợp dữ liệu, ví dụ như dữ liệu của nhân viên. CodeTên, và lương được ghi cùng nhau trên một tờ giấy.
Chúng ta bắt đầu với Trang tính 2 vì nó cung cấp hai đối số — cột Lương nhân viên nằm ở đây, và... Chỉ số cột là 2.
Chúng tôi muốn tìm mức lương phù hợp với từng nhân viên. Code.
Dữ liệu trải dài từ A2 đến B25 — đó chính là mảng dữ liệu của bảng chúng ta.
Bước 1) Chuyển sang Trang tính 1 và nhập các tiêu đề được hiển thị.
Bước 2) Nhấp chuột vào ô bên cạnh "Lương nhân viên" — ô F3 — nơi bạn sẽ nhập công thức VLOOKUP.
Nhập hàm VLOOKUP: =VLOOKUP().
Bước 3) Luận điểm 1: Nhập F2 — ô chứa thông tin Nhân viên Code để khớp trong bảng tra cứu.
Bước 4) Luận điểm 2: Bảng tra cứu nằm trên trang tính khác, vì vậy hãy tham chiếu nó bằng tên trang tính: Trang 2!A2:B25.
Bước 5) Luận điểm 3: Nhập chỉ số cột bên trong bảng tra cứu chứa giá trị trả về.
Bước 6) Luận điểm 4: Sử dụng FALSE nếu muốn tìm kiếm sự trùng khớp chính xác, vì chúng ta cần mức lương chính xác tương ứng với từng nhân viên. Code.
Bước 7) Nhấn Enter. Khi bạn nhập thông tin Nhân viên CodeÔ này trả về mức lương tương ứng được lấy từ Trang tính 2.
Các lỗi thường gặp khi sử dụng VLOOKUP và cách khắc phục
Ngay cả người dùng có kinh nghiệm cũng gặp lỗi VLOOKUP. Các lỗi phổ biến nhất và cách khắc phục nhanh chóng:
- # N / A — Hàm VLOOKUP không tìm thấy giá trị cần tìm. Hãy kiểm tra xem có khoảng trắng thừa, kiểu dữ liệu không khớp (số được lưu dưới dạng văn bản) hoặc giá trị đó có thực sự tồn tại trong cột đầu tiên của mảng table_array hay không.
- #REF! — col_index_num lớn hơn số cột trong table_array. Hãy giảm chỉ số cột hoặc mở rộng phạm vi.
- #GIÁ TRỊ! — col_index_num nhỏ hơn 1 hoặc một đối số không hợp lệ. Kiểm tra cú pháp công thức.
- Trả về kết quả không chính xác — Tham số thứ tư là TRUE hoặc bị bỏ qua nhưng cột tra cứu không được sắp xếp. Chuyển sang FALSE hoặc sắp xếp cột theo thứ tự tăng dần.
- Các tham chiếu bị khóa — Khi sao chép công thức xuống, hãy sử dụng tham chiếu tuyệt đối (ví dụ: $B$2:$E$25) để mảng bảng không bị lệch.
VLOOKUP hay XLOOKUP: Nên sử dụng hàm nào?
Microsoft đã giới thiệu hàm XLOOKUP trong Microsoft Hàm này được hỗ trợ trong Office 365 và Excel 2021 như một sự thay thế hiện đại cho hàm VLOOKUP. Nó loại bỏ một số hạn chế của VLOOKUP và hiện là lựa chọn được khuyến nghị trong các phiên bản được hỗ trợ.
| Tính năng | VLOOKUP | Xlookup |
|---|---|---|
| Hướng tìm kiếm | Chỉ từ trái sang phải | Bất kỳ hướng nào (trái, phải, lên, xuống) |
| Loại khớp mặc định | Xấp xỉ (ĐÚNG) | Chính xác |
| Xử lý trường hợp không tìm thấy | Hàng trả về #N/A | Đối số if_not_found tích hợp sẵn |
| Chỉ mục cột | Số được mã hóa cứng | Tham chiếu phạm vi cột trả về |
| Sự có sẵn | Tất cả các phiên bản Excel | Microsoft 365, Excel 2021, Excel dành cho web |
Khi nào nên chọn VLOOKUP: Tệp bảng tính phải chạy trên Excel 2019 trở xuống, hoặc bạn đang sử dụng các công thức cũ. Khi nào nên chọn XLOOKUP: Bạn đang tạo sổ làm việc mới trong Excel hiện đại và muốn sử dụng tra cứu bên trái, xử lý lỗi gọn gàng hơn và khớp chính xác theo mặc định. Tìm hiểu thêm về các hàm tra cứu trong phần tiếp theo. Hướng dẫn Excel series.
Kết luận
Ba trường hợp trên giải thích cách VLOOKUP hoạt động đối với các kết quả khớp chính xác, khớp gần đúng và tham chiếu chéo giữa các trang tính. Hãy thực hành trên tập dữ liệu của riêng bạn để thành thạo. VLOOKUP vẫn là một tính năng quan trọng trong MS Excel Để quản lý dữ liệu hiệu quả, hàm XLOOKUP mở rộng bộ công cụ đó trong Excel hiện đại.
