Video: Hàm Vlookup - từ cơ bản đến nâng cao 2025
Phổ biến nhất của các chức năng tra cứu trong Excel 2013 là HLOOKUP (cho Horizontal Lookup) và VLOOKUP (cho Vertical Lookup) chức năng. Các chức năng này nằm trên trình đơn thả xuống Lookup & Reference trên tab Công thức của Ribbon cũng như trong danh mục Tra cứu & Tham chiếu trong hộp thoại Chèn Chức năng.
Chúng là một phần của nhóm chức năng mạnh mẽ có thể trả về giá trị bằng cách tìm kiếm chúng trong các bảng dữ liệu.
Vlookup chức năng tìm kiếm theo chiều dọc (từ trên xuống dưới) cột bên trái của bảng Lookup cho đến khi chương trình định vị một giá trị phù hợp hoặc vượt quá một trong những bạn đang tìm kiếm. Hàm HLOOKUP tìm kiếm theo chiều ngang (từ trái sang phải) hàng trên cùng của bảng Tra cứu cho đến khi nó định vị một giá trị phù hợp hoặc vượt quá giá trị mà bạn đang tìm kiếm.
Hàm VLOOKUP sử dụng cú pháp sau:
HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup]) Trong cả hai chức năng, đối sốlookup_value
là giá trị mà bạn muốn tra cứu trong bảng tra cứu và
table_array là dải ô hoặc tên của bảng tra cứu bao gồm cả giá trị để tra cứu và giá trị liên quan để trở lại. lookup_value
trong một bảng thẳng đứng. Đối số row_index_num trong hàm HLOOKUP là số hàng có giá trị được so sánh với lookup_value trong một bảng ngang. col_index_num hoặc row_index_num đối số trong các hàm VLOOKUP và HLOOKUP, bạn phải nhập một giá trị lớn hơn số không vượt quá tổng số cột hoặc hàng trong cột Bảng tra cứu.
Đối số tùy chọn range_lookup tùy chọn trong cả hai chức năng VLOOKUP và HLOOKUP là TRUE hoặc FALSE hợp lệ chỉ định bạn muốn Excel tìm một kết hợp chính xác hoặc tương đối cho lookup_value bảng table_array.
Khi bạn chỉ định TRUE hoặc bỏ qua đối số range_lookup trong hàm VLOOKUP hoặc HLOOKUP, Excel sẽ tìm thấy một kết hợp gần đúng. Khi bạn chỉ định FALSE là đối số range_lookup , Excel sẽ chỉ tìm ra các kết hợp chính xác.
Việc tìm kiếm các kết quả gần đúng chỉ liên quan đến khi bạn tìm kiếm các mục số (thay vì văn bản) trong cột hoặc hàng đầu tiên của bảng tra cứu dọc hoặc ngang.Khi Excel không tìm thấy một kết hợp chính xác trong cột hay hàng Lookup này, nó sẽ đặt giá trị cao nhất tiếp theo mà không vượt quá đối số lookup_value và sau đó trả về giá trị trong cột hoặc hàng được chỉ định bởi < col_index_num hoặc row_index_num
đối số. Khi sử dụng các chức năng VLOOKUP và HLOOKUP, các mục nhập văn bản hoặc số trong cột hoặc hàng Lookup (có nghĩa là cột bên trái của bảng tra cứu dọc hoặc hàng trên cùng của bảng Tra cứu ngang) phải là duy nhất. Các mục này cũng phải được sắp xếp hoặc sắp xếp theo thứ tự tăng dần; đó là, thứ tự chữ cái cho các mục nhập văn bản, và thứ tự thấp nhất đến cao nhất cho các mục nhập số. Hình minh hoạ cho thấy một ví dụ về việc sử dụng chức năng VLOOKUP để trả lại đầu 15% hoặc 20% từ bảng tip, tùy thuộc vào tổng số tiền trước khi kiểm tra. Cell F3 chứa hàm VLOOKUP: = VLOOKUP (Pretax_Total, Tip_Table, IF (Tip_Percentage = 0, 15, 2, 3)) Công thức này trả về lượng tip dựa trên phần trăm tip trong ô F1 và số tiền trước khi kiểm tra tế bào F2. Để sử dụng bảng mẹo này, nhập phần trăm đầu (15% hoặc 20%) vào ô F1 (có tên Tip_Percentage) và số tiền kiểm tra trước khi thuế trong ô F2 (tên Pretax_Total). Excel sau đó tìm kiếm giá trị mà bạn nhập vào ô Pretax_Total trong cột đầu tiên của bảng Tra cứu, bao gồm ô A2: C101 và có tên là Tip_Table. Excel sẽ di chuyển xuống các giá trị trong cột đầu tiên của Tip_Table cho đến khi tìm thấy một kết hợp, sau đó chương trình sử dụng đối số
col_index_num
trong hàm VLOOKUP để xác định số tiền tip nào từ hàng đó của bảng trở lại ô F3.
Nếu Excel thấy rằng giá trị đã nhập vào ô Pretax_Total (16,50 đô la trong ví dụ này) không khớp chính xác với một trong các giá trị trong cột đầu tiên của Tip_Table, chương trình sẽ tiếp tục dò tìm phạm vi so sánh cho đến khi nó gặp giá trị đầu tiên vượt quá tổng giá trị trước (17 00 trong ô A19 trong ví dụ này).
Excel di chuyển trở lại hàng trước trong bảng và trả về giá trị trong cột phù hợp với đối số
col_index_num
của hàm VLOOKUP. (Điều này là do đối số range_lookup tùy chọn đã bị bỏ qua trong hàm)
Lưu ý rằng ví dụ bảng tip sử dụng một hàm IF để xác định đối số
col_index_num cho hàm VLOOKUP trong ô F3. Hàm IF xác định số cột được sử dụng trong bảng tip bằng cách khớp phần trăm đã nhập vào trong Tip_Percentage (ô F1) với 0. 15. Nếu chúng trùng khớp, hàm trả về 2 là col_index_num đối số và hàm VLOOKUP trả về một giá trị từ cột thứ hai (cột 15% B) trong dãy Tip_Table.
Nếu không, hàm NẾU trả về 3 như là đối số col_index_num và hàm VLOOKUP trả về giá trị từ cột thứ ba (cột 20% C) trong dãy Tip_Table.
Hình dưới đây cho thấy một ví dụ sử dụng hàm HLOOKUP để tra cứu giá của mỗi mặt hàng bánh được lưu trữ trong bảng Tra cứu Giá riêng và sau đó trả lại giá đó cho cột Price / Doz của danh sách Daily Sales.Cell F3 chứa công thức ban đầu với chức năng HLOOKUP sau đó được sao chép xuống cột F: = HLOOKUP (mục, Price_table, 2, FALSE) Trong hàm HLOOKUP này, tên dải ô Item được gán cho cột Item trong phạm vi C3: C62 được định nghĩa là đối số
lookup_value và tên phạm vi ô Bảng giá cung cấp cho phạm vi ô I1: M2 là đối số table_array
Đối số
row_index_num là 2 vì bạn muốn Excel trả lại giá trong hàng thứ hai của bảng Giá Tra cứu và đối số range_lookup tùy chọn là FALSE bởi vì tên mục trong danh sách Hàng bán hàng phải khớp chính xác tên mặt hàng trong bảng Tra cứu Giá. Bằng cách sử dụng hàm HLOOKUP sử dụng phạm vi Bảng Giá để nhập giá cho mỗi hàng chục cho mỗi mặt hàng bánh ngọt trong danh sách Hàng Sales hàng ngày, bạn thực hiện việc này rất đơn giản để cập nhật bất kỳ doanh số bán hàng nào trong danh sách.
Tất cả bạn phải làm là thay đổi giá của nó / Doz trong phạm vi này, và chức năng HLOOKUP ngay lập tức cập nhật giá mới trong danh sách Hàng ngày bán hàng bất cứ nơi nào các mặt hàng được bán.