Video: Bài 10. Sử dụng Hàm VLOOKUP và HLOOKUP trong Microsoft Excel 2016 2025
Phổ biến nhất của Excel 2016 tra cứu chức năng là HLOOKUP (cho Lookup ngang) 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 một nhóm các 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. col_index_num
sẽ chỉ định cột của bảng tra cứu có chứa các giá trị được trả về bởi hàm VLOOKUP dựa trên khớp với giá trị của đối số lookup_value chống lại những người trong đối số table_array. Bạn xác định đối số col_index_num đếm bao nhiêu cột cột này qua bên phải từ cột đầu tiên của bảng tra cứu theo chiều dọc, và bạn bao gồm cột đầu tiên của bảng Tra cứu trong số này. Đối số row_index_num
chỉ định hàng chứa các giá trị được trả về bởi hàm HLOOKUP trong một bảng ngang. Bạn xác định đối số row_index_num bằng cách đếm có bao nhiêu hàng trong dãy này là từ hàng trên cùng của bảng Tra cứu ngang. Một lần nữa, bạn bao gồm hàng trên cùng của bảng Lookup trong số này. Khi nhập các đối số col_index_num
hoặc row_index_num trong các hàm VLOOKUP và HLOOKUP, giá trị bạn nhập không vượt quá tổng số cột hoặc hàng trong 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 chức năng VLOOKUP để trả lại số tiền của tip để thêm từ một bảng Lookup.
Để 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ố
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 tìm kiếm phạm vi so sánh cho đến khi nó gặp giá trị đầu tiên vượt quá tổng số tiền đặt trước (17 00 trong ô A19 trong ví dụ này). Excel di chuyển trở về 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. (Đó là vì đối số range_lookup tùy chọn đã bị bỏ qua trong hàm) Lưu ý rằng ví dụ về bảng tip trong hình sử dụng một hàm IF để xác định đối số col_index_num 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 trong Tip_Percentage (ô F1) với 0.15. Nếu chúng khớp, hàm sẽ trả về 2 như là đối số
col_index_num và hàm VLOOKUP trả về giá trị từ cột thứ hai (cột 15% B) trong dãy Tip_Table. Nếu không, hàm IF trả về 3 như là đối số col_index_num và hàm VLOOKUP trả về một 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 đó sao chép xuống cột F: Sử dụng hàm HLOOKUP để trả lại giá mặt hàng bánh từ một bảng Lookup. = HLOOKUP (mục, Price_table, 2, FALSE)
Trong hàm HLOOKUP này, tên phạm vi Item được đưa ra cho cột Item trong phạm vi C3: C62 được định nghĩa là đối số
và tên dãy ô Bảng giá cung cấp cho dãy ô 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 Bán hàng Hàng ngày danh sách phải khớp chính xác tên mục 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.
