Tải xuống sổ làm việc mẫu
Hướng dẫn này trình bày cách sử dụng Hàm HLOOKUP trong Excel trong Excel để tra cứu một giá trị.
Tổng quan về hàm HLOOKUP
Hàm HLOOKUP Hlookup là viết tắt của tra cứu theo chiều ngang. Nó tìm kiếm một giá trị ở hàng trên cùng của bảng. Sau đó, trả về một giá trị một số hàng được chỉ định giảm từ giá trị tìm thấy. Nó giống như vlookup, ngoại trừ nó tìm kiếm các giá trị theo chiều ngang thay vì chiều dọc.
(Chú ý cách các đầu vào công thức xuất hiện)
Cú pháp và đầu vào của hàm HLOOKUP:
| 1 | = HLOOKUP (lookup_value, table_array, row_index_num, range_lookup) |
lookup_value - Giá trị bạn muốn tìm kiếm.
table_array -Bảng từ đó lấy dữ liệu.
row_index_num - Số hàng để lấy dữ liệu.
range_lookup - [tùy chọn] Một boolean để chỉ ra đối sánh chính xác hoặc đối sánh gần đúng. Mặc định = TRUE = đối sánh gần đúng.
Hàm HLOOKUP là gì?
Là một trong những hàm cũ hơn trong thế giới bảng tính, hàm HLOOKUP được sử dụng để NStheo chiều ngang Các bản tra cứu. Nó có một số hạn chế thường được khắc phục với các chức năng khác, chẳng hạn như INDEX / MATCH. Ngoài ra, hầu hết các bảng đều được xây dựng theo kiểu dọc, nhưng có một vài trường hợp hữu ích khi tìm kiếm theo chiều ngang.
Ví dụ cơ bản
Hãy xem một mẫu dữ liệu từ sổ điểm. Chúng tôi sẽ giải quyết một số ví dụ để trích xuất thông tin cho các sinh viên cụ thể.
Nếu chúng ta muốn tìm Bob thuộc lớp nào, chúng ta sẽ viết công thức:
| 1 | = HLOOKUP ("Bob", A1: E3, 2, FALSE) |
Điều quan trọng cần nhớ là mục chúng tôi đang tìm kiếm (Bob), phải ở hàng đầu tiên trong phạm vi tìm kiếm của chúng tôi (A1: E3). Chúng tôi đã nói với hàm rằng chúng tôi muốn trả về một giá trị từ 2NS hàng của phạm vi tìm kiếm, trong trường hợp này là hàng 2. Cuối cùng, chúng tôi đã chỉ ra rằng chúng tôi muốn thực hiện kết hợp chuẩn xác bằng cách đặt False làm đối số cuối cùng. Ở đây, câu trả lời sẽ là “Đọc”.
Mẹo phụ: Bạn cũng có thể sử dụng số 0 thay vì Sai làm đối số cuối cùng, vì chúng có cùng giá trị. Một số người thích điều này vì nó viết nhanh hơn. Chỉ biết rằng cả hai đều có thể chấp nhận được.
Dữ liệu đã thay đổi
Để thêm một số làm rõ cho ví dụ đầu tiên của chúng tôi, mục tra cứu không nhất thiết phải ở hàng 1 của bảng tính của bạn, chỉ là hàng đầu tiên trong phạm vi tìm kiếm của bạn. Hãy sử dụng cùng một tập dữ liệu:
Bây giờ, hãy tìm điểm cho lớp Khoa học. Công thức của chúng tôi sẽ là
| 1 | = HLOOKUP ("Khoa học", A2: E3, 2, FALSE) |
Đây vẫn là một công thức hợp lệ, vì hàng đầu tiên trong phạm vi tìm kiếm của chúng tôi là hàng 2, là nơi tìm thấy cụm từ tìm kiếm “Khoa học” của chúng tôi. Chúng tôi đang trả về một giá trị từ 2NS hàng của phạm vi tìm kiếm, trong trường hợp này là hàng 3. Câu trả lời sau đó là “A-“.
Sử dụng ký tự đại diện
Hàm HLOOKUP hỗ trợ việc sử dụng các ký tự đại diện “*” an “?” khi thực hiện tìm kiếm. Ví dụ: giả sử chúng tôi đã quên cách đánh vần tên của Frank và chỉ muốn tìm kiếm một cái tên bắt đầu bằng “F”. Chúng tôi có thể viết công thức
| 1 | = HLOOKUP ("F *", A1: E3, 2, FALSE) |
Điều này sẽ có thể tìm thấy tên Frank trong cột E và sau đó trả về giá trị từ 2NS hàng tương đối. Trong trường hợp này, câu trả lời sẽ là "Khoa học".
Đối sánh không chính xác
Hầu hết thời gian, bạn sẽ muốn đảm bảo rằng đối số cuối cùng trong HLOOKUP là Sai (hoặc 0) để bạn có được một đối sánh chính xác. Tuy nhiên, có một vài lần bạn có thể tìm kiếm kết quả không chính xác. Nếu bạn có danh sách dữ liệu được sắp xếp, bạn cũng có thể sử dụng HLOOKUP để trả về kết quả cho mục giống nhau hoặc nhỏ nhất tiếp theo. Điều này thường được sử dụng khi xử lý các dải số ngày càng tăng, chẳng hạn như trong bảng thuế hoặc tiền thưởng hoa hồng.
Giả sử bạn muốn tìm thuế suất cho thu nhập được nhập vào ô H2. Công thức trong H4 có thể là:
| 1 | = HLOOKUP (H2, B1: F2, 2, TRUE) |
Sự khác biệt trong công thức này là đối số cuối cùng của chúng ta là "Đúng". Trong ví dụ cụ thể của chúng ta, chúng ta có thể thấy rằng khi cá nhân của chúng ta đầu vào thu nhập là $ 45,000 thì họ sẽ có thuế suất là 15%.
Ghi chú: Mặc dù chúng tôi thường muốn đối sánh chính xác với False làm đối số, nhưng bạn quên chỉ định 4NS đối số trong HLOOKUP, mặc định là True. Điều này có thể khiến bạn nhận được một số kết quả không mong muốn, đặc biệt là khi xử lý các giá trị văn bản.
Hàng động
HLOOKUP yêu cầu bạn đưa ra một đối số cho biết bạn muốn trả về giá trị từ hàng nào, nhưng có thể xảy ra trường hợp bạn không biết hàng đó sẽ ở đâu hoặc bạn muốn cho phép người dùng của mình thay đổi hàng để trả về từ đó. Trong những trường hợp này, có thể hữu ích khi sử dụng hàm MATCH để xác định số hàng.
Chúng ta hãy xem xét lại ví dụ về sách điểm của chúng ta, với một số đầu vào trong G2 và G4. Để lấy số cột, chúng ta có thể viết công thức
| 1 | = MATCH (G2, A1: A3, 0) |
Điều này sẽ cố gắng tìm vị trí chính xác của "Lớp" trong phạm vi A1: A3. Câu trả lời sẽ là 3. Biết được điều này, chúng ta có thể cắm nó vào một hàm HLOOKUP và viết một công thức trong G6 như sau:
| 1 | = HLOOKUP (G4, A1: E3, MATCH (G2, A1: A3, 0), 0) |
Vì vậy, hàm MATCH sẽ đánh giá là 3 và điều đó yêu cầu HLOOKUP trả về kết quả từ 3rd hàng trong phạm vi A1: E3. Nhìn chung, sau đó chúng tôi nhận được kết quả mong muốn của chúng tôi là “C”. Công thức của chúng tôi giờ đây rất linh hoạt ở chỗ chúng tôi có thể thay đổi hàng cần xem hoặc tên để tìm kiếm.
Giới hạn của HLOOKUP
Như đã đề cập ở phần đầu của bài viết, nhược điểm lớn nhất của HLOOKUP là nó yêu cầu cụm từ tìm kiếm phải được tìm thấy ở cột bên trái nhất của phạm vi tìm kiếm. Mặc dù có một số thủ thuật ưa thích bạn có thể làm để khắc phục điều này, nhưng giải pháp thay thế phổ biến là sử dụng INDEX và MATCH. Sự kết hợp đó mang lại cho bạn sự linh hoạt hơn và đôi khi nó thậm chí có thể là một phép tính nhanh hơn.
HLOOKUP trong Google Trang tính
Hàm HLOOKUP hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel:
Ghi chú bổ sung
Sử dụng Hàm HLOOKUP để thực hiện tra cứu theo đường ngang. Nếu bạn đã quen thuộc với Hàm VLOOKUP, thì một hàm HLOOKUP hoạt động theo cùng một cách ngoại trừ việc tra cứu được thực hiện theo chiều ngang thay vì chiều dọc. HLOOKUP tìm kiếm một kết hợp chính xác (range_lookup = FALSE) hoặc so khớp gần nhất bằng hoặc nhỏ hơn giá trị lookup_value (range_lookup = TRUE, chỉ các giá trị số) trong hàng đầu tiên của table_array. Sau đó, nó trả về một giá trị tương ứng, n số hàng bên dưới so khớp.
Khi sử dụng HLOOKUP để tìm đối sánh chính xác, trước tiên, bạn xác định một giá trị nhận dạng mà bạn muốn tìm kiếm là lookup_value. Giá trị nhận dạng này có thể là SSN, ID nhân viên, tên hoặc một số định danh duy nhất khác.
Tiếp theo, bạn xác định phạm vi (được gọi là table_array) có chứa các số nhận dạng ở hàng trên cùng và bất kỳ giá trị nào mà bạn cuối cùng muốn tìm kiếm trong các hàng bên dưới nó. QUAN TRỌNG: Các số nhận dạng duy nhất phải ở hàng trên cùng. Nếu không, bạn phải di chuyển hàng lên trên cùng hoặc sử dụng MATCH / INDEX thay vì HLOOKUP.
Thứ ba, xác định số hàng (row_index) sau đó table_array mà bạn muốn trở lại. Hãy nhớ rằng hàng đầu tiên, chứa các số nhận dạng duy nhất là hàng 1. Hàng thứ hai là hàng 2, v.v.
Cuối cùng, bạn phải cho biết nên tìm kiếm kết hợp chính xác (FALSE) hay kết hợp gần nhất (TRUE) trong range_lookup. Nếu tùy chọn đối sánh chính xác được chọn và không tìm thấy đối sánh chính xác, thì sẽ trả về lỗi (# N / A). Để công thức trả về trống hoặc “không tìm thấy” hoặc bất kỳ giá trị nào khác thay vì giá trị lỗi (# N / A), hãy sử dụng Hàm IFERROR với HLOOKUP.
Để sử dụng Hàm HLOOKUP để trả về một tập hợp so khớp gần đúng: range_lookup = ĐÚNG. Tùy chọn này chỉ có sẵn cho các giá trị số. Các giá trị phải được sắp xếp theo thứ tự tăng dần.
Ví dụ về HLOOKUP trong VBA
Bạn cũng có thể sử dụng hàm HLOOKUP trong VBA. Kiểu:application.worksheet Chức năng.hlookup (lookup_value, table_array, row_index_num, range_lookup)
Thực thi các câu lệnh VBA sau
| 123456 | Phạm vi ("G2") = Application.WorksheetFunction.HLookup (Phạm vi ("C1"), Phạm vi ("A1: E3"), 1)Range ("H2") = Application.WorksheetFunction.HLookup (Phạm vi ("C1"), Phạm vi ("A1: E3"), 2)Phạm vi ("I2") = Application.WorksheetFunction.HLookup (Phạm vi ("C1"), Phạm vi ("A1: E3"), 3)Phạm vi ("G3") = Application.WorksheetFunction.HLookup (Phạm vi ("D1"), Phạm vi ("A1: E3"), 1)Phạm vi ("H3") = Application.WorksheetFunction.HLookup (Phạm vi ("D1"), Phạm vi ("A1: E3"), 2)Phạm vi ("I3") = Application.WorksheetFunction.HLookup (Phạm vi ("D1"), Phạm vi ("A1: E3"), 3) |
sẽ tạo ra kết quả sau
Đối với các đối số của hàm (lookup_value, v.v.), bạn có thể nhập chúng trực tiếp vào hàm hoặc xác định các biến để sử dụng thay thế.
Quay lại danh sách tất cả các hàm trong Excel
