Bằng cách kết hợp hàm VLOOKUP của Excel với hàm COLUMN, bạn có thể tạo công thức tra cứu trả về nhiều giá trị từ một hàng của cơ sở dữ liệu hoặc bảng dữ liệu. Tìm hiểu cách tạo công thức tra cứu trả về nhiều giá trị từ một bản ghi dữ liệu.
Hướng dẫn trong bài viết này áp dụng cho Excel 2019, 2016, 2013, 2010; và Excel cho Microsoft 365.
Bottom Line
Công thức tra cứu yêu cầu hàm COLUMN được lồng vào bên trong hàm VLOOKUP. Việc lồng một hàm liên quan đến việc nhập hàm thứ hai làm một trong các đối số cho hàm đầu tiên.
Nhập Dữ liệu Hướng dẫn
Trong hướng dẫn này, hàm COLUMN được nhập làm đối số chỉ số cột cho hàm VLOOKUP. Bước cuối cùng trong hướng dẫn liên quan đến việc sao chép công thức tra cứu vào các cột bổ sung để truy xuất các giá trị bổ sung cho phần đã chọn.
Bước đầu tiên trong hướng dẫn này là nhập dữ liệu vào trang tính Excel. Để làm theo các bước trong hướng dẫn này, hãy nhập dữ liệu được hiển thị trong hình ảnh bên dưới vào các ô sau:
- Nhập dải dữ liệu trên cùng vào các ô từ D1 đến G1.
- Nhập dải ô thứ hai vào các ô từ D4 đến G10.
Tiêu chí tìm kiếm và công thức tra cứu được tạo trong hướng dẫn này được nhập vào hàng 2 của trang tính.
Hướng dẫn này không bao gồm định dạng Excel cơ bản được hiển thị trong hình ảnh, nhưng điều này không ảnh hưởng đến cách hoạt động của công thức tra cứu.
Tạo Dải ô đã Đặt tên cho Bảng Dữ liệu
Dải ô được đặt tên là một cách dễ dàng để tham chiếu đến một dải dữ liệu trong công thức. Thay vì nhập tham chiếu ô cho dữ liệu, hãy nhập tên của phạm vi.
Ưu điểm thứ hai của việc sử dụng phạm vi đã đặt tên là các tham chiếu ô cho phạm vi này không bao giờ thay đổi ngay cả khi công thức được sao chép sang các ô khác trong trang tính. Tên phạm vi là một giải pháp thay thế cho việc sử dụng tham chiếu ô tuyệt đối để tránh lỗi khi sao chép công thức.
Tên phạm vi không bao gồm các tiêu đề hoặc tên trường cho dữ liệu (như được hiển thị trong hàng 4), chỉ có dữ liệu.
-
Đánh dấu ô D5 đến G10trong trang tính.
Image -
Đặt con trỏ vào Hộp Tên phía trên cột A, nhập Bảng, sau đó nhấn Nhập. Các ô từ D5 đến G10 có tên phạm vi của Bảng.
Image - Tên phạm vi cho đối số mảng bảng VLOOKUP được sử dụng sau trong hướng dẫn này.
Mở Hộp thoại VLOOKUP
Mặc dù có thể nhập công thức tra cứu trực tiếp vào một ô trong trang tính, nhưng nhiều người cảm thấy khó giữ cú pháp thẳng - đặc biệt là đối với một công thức phức tạp như công thức được sử dụng trong hướng dẫn này.
Để thay thế, hãy sử dụng hộp thoại Đối số Hàm VLOOKUP. Hầu như tất cả các hàm của Excel đều có hộp thoại trong đó mỗi đối số của hàm được nhập trên một dòng riêng biệt.
-
Chọn ô E2của trang tính. Đây là vị trí hiển thị kết quả của công thức tra cứu hai chiều.
Image -
Trên ruy-băng, đi tới tab Công thứcvà chọn Tra cứu & Tham khảo.
Image -
Chọn VLOOKUP để mở hộp thoại Hàm Đối số.
Image - Hộp thoại Đối số Hàm là nơi nhập các tham số của hàm Vlookup.
Nhập Đối số Giá trị Tra cứu
Thông thường, giá trị tra cứu khớp với một trường dữ liệu trong cột đầu tiên của bảng dữ liệu. Trong ví dụ này, giá trị tra cứu đề cập đến tên của bộ phận mà bạn muốn tìm kiếm thông tin. Các loại dữ liệu được phép cho giá trị tra cứu là dữ liệu văn bản, giá trị lôgic, số và tham chiếu ô.
Tham chiếu ô tuyệt đối
Khi công thức được sao chép trong Excel, các tham chiếu ô sẽ thay đổi để phản ánh vị trí mới. Nếu điều này xảy ra, D2, tham chiếu ô cho giá trị tra cứu, sẽ thay đổi và tạo ra lỗi trong ô F2 và G2.
Tham chiếu ô tuyệt đối không thay đổi khi công thức được sao chép.
Để tránh lỗi, hãy chuyển đổi tham chiếu ô D2 thành tham chiếu ô tuyệt đối. Để tạo tham chiếu ô tuyệt đối, hãy nhấn phím F4. Điều này thêm các ký hiệu đô la xung quanh tham chiếu ô, chẳng hạn như $ D $ 2.
-
Trong hộp thoại Đối số Hàm, đặt con trỏ vào hộp văn bản lookup_value. Sau đó, trong trang tính, chọn ô D2 để thêm tham chiếu ô này vào lookup_value. Ô D2 là nơi tên bộ phận sẽ được nhập.
Image -
Không di chuyển điểm chèn, nhấn phím F4để chuyển D2 thành tham chiếu ô tuyệt đối $ D $ 2.
Image - Để hộp thoại hàm VLOOKUP mở cho bước tiếp theo trong hướng dẫn.
Nhập Đối số Mảng trong Bảng
Mảng bảng là bảng dữ liệu mà công thức tra cứu sẽ tìm kiếm thông tin bạn muốn. Mảng bảng phải chứa ít nhất hai cột dữ liệu.
Cột đầu tiên chứa đối số giá trị tra cứu (đã được thiết lập trong phần trước), trong khi cột thứ hai được tìm kiếm theo công thức tra cứu để tìm thông tin bạn chỉ định.
Đối số mảng bảng phải được nhập dưới dạng dải ô chứa tham chiếu ô cho bảng dữ liệu hoặc dưới dạng tên dải ô.
Để thêm bảng dữ liệu vào hàm Vlookup, hãy đặt con trỏ vào hộp văn bản table_arraytrong hộp thoại và nhập Tableđể nhập tên dải ô cho đối số này.
lồng Hàm COLUMN
Thông thường, hàm VLOOKUP chỉ trả về dữ liệu từ một cột của bảng dữ liệu. Cột này được đặt bởi đối số chỉ số cột. Tuy nhiên, trong ví dụ này, có ba cột và số chỉ mục cột cần được thay đổi mà không cần chỉnh sửa công thức tra cứu. Để thực hiện điều này, hãy lồng hàm COLUMN bên trong hàm VLOOKUP dưới dạng đối số Col_index_num.
Khi lồng các hàm, Excel không mở hộp thoại của hàm thứ hai để nhập các đối số của nó. Hàm COLUMN phải được nhập theo cách thủ công. Hàm COLUMN chỉ có một đối số, đối số Tham chiếu, là một tham chiếu ô.
Hàm COLUMN trả về số cột được cung cấp làm đối số Tham chiếu. Nó chuyển đổi ký tự cột thành một số.
Để tìm giá của một mặt hàng, hãy sử dụng dữ liệu trong cột 2 của bảng dữ liệu. Ví dụ này sử dụng cột B làm Tham chiếu để chèn 2 vào đối số Col_index_num.
-
Trong hộp thoại Function Arguments, đặt con trỏ vào hộp văn bản Col_index_numvà nhập COLUMN (. (Hãy chắc chắn bao gồm dấu ngoặc tròn mở.)
Image -
Trong trang tính, chọn ô B1để nhập tham chiếu ô đó làm đối số Tham chiếu.
Image - Nhập dấu ngoặc tròn đóng để hoàn thành hàm COLUMN.
Nhập Đối số Tra cứu Phạm vi VLOOKUP
Đối số Range_lookup củaVLOOKUP là một giá trị logic (TRUE hoặc FALSE) cho biết liệu hàm VLOOKUP sẽ tìm một kết quả khớp chính xác hay gần đúng với giá trị Lookup_value.
- TRUE hoặc Bỏ qua: Hàm VLOOKUP trả về giá trị khớp gần giống với giá trị Tra cứu. Nếu không tìm thấy kết quả khớp chính xác, hàm VLOOKUP trả về giá trị lớn nhất tiếp theo. Dữ liệu trong cột đầu tiên của Table_array phải được sắp xếp theo thứ tự tăng dần.
- FALSE: Hàm VLOOKUP sử dụng đối sánh chính xác với giá trị Tra cứu. Nếu có hai hoặc nhiều giá trị trong cột đầu tiên của Table_array khớp với giá trị tra cứu, giá trị đầu tiên tìm được sẽ được sử dụng. Nếu không tìm thấy kết quả khớp chính xác, sẽ trả về lỗiN / A.
Trong hướng dẫn này, thông tin cụ thể về một mục phần cứng cụ thể sẽ được tra cứu, vì vậy Phạm vi_lookup được đặt thành FALSE.
Trong hộp thoại Đối số Hàm, đặt con trỏ vào hộp văn bản Range_lookup và nhập Falseđể yêu cầu hàm VLOOKUP trả về kết quả khớp chính xác cho dữ liệu.
Chọn OKđể hoàn thành công thức tra cứu và đóng hộp thoại. Ô E2 sẽ có lỗiN / A vì tiêu chí tra cứu chưa được nhập vào ô D2. Lỗi này là tạm thời. Nó sẽ được sửa chữa khi các tiêu chí tra cứu được thêm vào trong bước cuối cùng của hướng dẫn này.
Sao chép Công thức Tra cứu và Nhập Tiêu chí
Công thức tra cứu lấy dữ liệu từ nhiều cột của bảng dữ liệu cùng một lúc. Để thực hiện việc này, công thức tra cứu phải nằm trong tất cả các trường mà bạn muốn có thông tin.
Để truy xuất dữ liệu từ các cột 2, 3 và 4 của bảng dữ liệu (giá, số bộ phận và tên nhà cung cấp), hãy nhập một phần tên là Lookup_value.
Vì dữ liệu được trình bày theo mẫu thông thường trong trang tính, hãy sao chép công thức tra cứu trong ô E2 đến ô F2và G2Khi công thức được sao chép, Excel sẽ cập nhật tham chiếu ô tương đối trong hàm COLUMN (ô B1) để phản ánh vị trí mới của công thức. Excel không thay đổi tham chiếu ô tuyệt đối (chẳng hạn như $ D $ 2) và phạm vi đã đặt tên (Bảng) khi công thức được sao chép.
Có nhiều cách để sao chép dữ liệu trong Excel, nhưng cách dễ nhất là sử dụng Fill Handle.
-
Chọn ô E2, nơi chứa công thức tra cứu, để biến nó thành ô hoạt động.
Image -
Kéo chốt điền sang ô G2. Các ô F2 và G2 hiển thị lỗiN / A có trong ô E2.
Image -
Để sử dụng công thức tra cứu để truy xuất thông tin từ bảng dữ liệu, trong trang tính, chọn ô D2, nhập Widgetvà nhấn Nhập.
Image Thông tin sau sẽ hiển thị trong các ô từ E2 đến G2.
- E2:$ 14,76 - giá của một phụ tùng
- F2:PN-98769 - số bộ phận của tiện ích con
- G2:Widgets Inc. - tên nhà cung cấp vật dụng
-
Để kiểm tra công thức mảng VLOOKUP, hãy nhập tên của các phần khác vào ô D2 và quan sát kết quả trong các ô từ E2 đến G2.
Image - Mỗi ô chứa công thức tra cứu chứa một phần dữ liệu khác nhau về mục phần cứng mà bạn đã tìm kiếm.
Hàm VLOOKUP với các hàm lồng nhau như COLUMN cung cấp một phương pháp mạnh mẽ để tra cứu dữ liệu bên trong bảng, sử dụng dữ liệu khác làm tham chiếu tra cứu.
