Hàm HLOOKUP là một hàm thông dụng trong excel để tìm kiếm giá trị. Nhưng có nhiều bạn vẫn còn nhiều bối rối khi sử dụng hàm HLOOKUP vì chưa thực sự hiểu được cú pháp và các áp dụng cú pháp hàm vào trường hợp cụ thể. Ở bài viết này, chúng mình sẽ giới thiệu cho các bạn chi tiết về hàm HLOOKUP và phân biệt hàm HLOOKUP với hàm VLOOKUP để tránh nhầm lẫn khi sử dụng công thức.
Hàm HLOOKUP trong excel
Hàm HLOOKUP có tên đầy đủ là Horizontal Lookup, có nghĩa là tìm kiếm giá theo hàng ngang. Vậy nên hàm HLOOKUP được dùng để tìm kiếm một giá trị nhất định ở hàng ngang trong bảng dữ liệu hoặc mảng dữ liệu.
Hàm HLOOKUP có mặt trong các phiên bản Microsoft Excel 2016, Excel 2013, Excel 2010, Excel 2007 và cả những phiên bản cũ hơn.
Ứng dụng hàm HLOOKUP
Vì đây là hàm tìm kiếm giá trị cho nên khi bạn sắp xếp các dữ liệu theo hàng ngang thì bất cứ giá trị nào bạn cũng có thể tìm kiếm được khi nhập đúng cú pháp.
Hàm HLOOKUP được ứng nhiều trong công việc kế toán, tài chính, kinh doanh… và gần như là đa dạng lĩnh vực mà các lĩnh vực đó thường sử dụng số liệu và sắp xếp số liệu trong excel.
Cụ thể để tìm kiếm mặt hàng theo mã số được trình bày dạng dữ liệu hàng ngang thì người ta sử dụng hàm HLOOKUP.
Cú pháp hàm HLOOKUP
Cú pháp: HLOOKUP (lookup_value, table array, row_index_num, [range_lookup])
Trong đó:
– Lookup_value (giá trị bắt buộc): là giá trị cần tìm kiếm. Giá trị này có thể là ô tham chiếu, một giá trị cụ thể hoặc chuỗi văn bản.
– Table_array (giá trị bắt buộc): là bảng hoặc mảng tìm kiếm giá trị gồm hai hàng dữ liệu trở lên. Các giá trị tìm kiếm phải được đặt ở hàng đầu tiên của table_array.
– Row_index_num (giá trị bắt buộc): là vị trí hàng (số hàng) trong table_array nơi mà giá trị được trả về.
– Range_lookup (giá trị tuỳ chọn): là một giá trị logic cho biết HLOOKUP cần phải tìm kết quả khớp chính xác hay tương đối.
- Nếu Range_lookup là TRUE hoặc bỏ qua thì kết quả khớp tương đối được trả về. Nghĩa là nếu không tìm được kết quả chính xác thì hàm HLOOKUP sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value.
- Nếu Range_lookup là FALSE thì chỉ trả về kết quả khớp chính xác 100% với giá trị tìm kiếm.
- Nếu không có giá trị nào khớp với giá trị tìm kiếm thì hàm HLOOKUP sẽ trả về lỗi #N/A.
Lưu ý khi sử dụng hàm HLOOKUP
– Hàm HLOOKUP chỉ có thể tìm ở hàng trên cùng của table_array. Nếu bạn cần tìm ở những vị trí khác, hãy sử dụng công thức Index hoặc Match.
– Hàm HLOOKUP trong Excel không phân biệt được chữ hoa và thường, vậy nên chữ hoa và chữ thường đều được coi như 1 giá trị.
– Nếu range_lookup được đặt là TRUE hoặc bỏ trống (kết quả khớp tương đối) thì các giá trị ở hàng đầu của table_array phải được xếp theo thứ tự tăng dần và từ trái sang phải.
Ví dụ cách dùng hàm HLOOKUP
Cho bảng điểm học sinh dưới đây. Hãy xếp loại học sinh theo điểm trung bình được cho trong bảng.
Hướng dẫn:
– Bạn xác định các giá trị điểm và xếp loại được trình bày theo hàng ngang nên chúng ta có thể sử dụng hàm HLOOKUP để tìm kiếm những giá trị này.
– Sau đó bạn xác định giá trị bạn muốn tìm kiếm để trả về theo giá trị đó là gì. Ở đây, giá trị bạn muốn trả về là xếp loại, vậy dựa vào đâu để bạn xếp loại được học sinh, đó chính là dựa vào điểm trung bình. Vậy nên theo công thức thì lookup_value là giá trị điểm.
– Xác định bảng, hoặc mảng tìm kiếm chứa trị bạn muốn trả về. Bảng tìm kiếm ở đây là bảng điểm – xếp loại ở phía dưới.
– Hàng bạn muốn trả về là hàng xếp loại thuộc hàng số 2 của bảng.
– range_lookup là giá trị TRUE, tìm kiếm tương đối.
– Cụ thể công thức cho ô E2 được nhập như sau: =HLOOKUP(D2, $B$14:$F$15, 2, 1)
+ Giá trị bảng B14:F15 bạn nhấn F4 để cố định bảng cho ô xếp loại bạn học sinh tiếp theo.
– Kết quả trả về tìm kiếm được xếp loại của bạn Vũ Thanh Thúy theo điểm dựa trên bảng giá trị là Xếp loại Giỏi.
– Với những ô tiếp theo, bạn nhập công thức thủ công hoặc chọn dấu cộng (+) bên góc ô đầu tiên đã nhập rồi kéo chuột để áp dụng công thức cho toàn cột xếp loại.
Sự khác biệt giữa hàm HLOOKUP và VLOOKUP
– Hàm HLOOKUP và hàm VLOOKUP đều là hàm tìm kiếm giá trị, cú pháp gần như giống nhau. Chỉ có khác biệt là hàm VLOOKUP tìm kiếm giá trị theo cột còn hàm HLOOKUP tìm kiếm giá trị theo hàng.
– Cùng xem lại cú pháp của 2 hàm này bạn sẽ thấy rõ sự khác biệt.
+ HLOOKUP (lookup_value, table array, row_index_num, [range_lookup])
+ VLOOKUP(Lookup_value, table_ array, col_index_Num,[range_lookup])
– Như vậy bạn thấy được khác biệt một cái là tìm giá trị hàng row_index_num, một cái là tìm giá trị cột col_index_Num.
– Cùng xem ví dụ dưới đây, chỉ là cách trình bày khác đi theo hàng hay theo cột mà chúng ta lựa chọn sử dụng công thức.
10 lỗi sai khiến hàm HLOOKUP không chạy được
– Khi sử dụng hàm HLOOKUP, bạn sẽ thường mắc phải lỗi do sự nhầm lẫn giữa hàng và cột hay sai mảng tìm kiếm giá trị. Có một số lỗi thường gặp là lỗi #N/A, #VALUE hoặc #REF. Nếu hàm HLOOKUP của bạn không hoạt động được, nguyên nhân có thể là một trong các lỗi dưới đây:
- HLOOKUP không thể tìm kiếm các hàng phía trên
– Bạn nên nhớ rằng hàm HLOOKUP chỉ có thể tìm ở hàng đầu tiên trong bảng. Nếu các giá trị tìm kiếm nằm ở các hàng khác, lỗi N/A sẽ được trả lại. Để khắc phục lỗi này, bạn nên sử dụng công thức hàm INDEX/MATCH.
- Kết quả khớp tương đối và chính xác
– Khi bạn đang tìm giá trị theo hàng ngang hay dọc trong excel, đa số các trường hợp là bạn đang tìm kiếm một giá trị cụ thể nên cần kết quả khớp chính xác. Khi tìm kiếm với kết quả khớp tương đối (range_lookup được đặt TRUE hoặc bỏ trống), các bạn nên nhớ xếp các giá trị trong hàng theo thứ tự tăng dần.
- Bảng tham chiếu thay đổi khi sao chép công thức
– Khi sử dụng nhiều HLOOKUP để lấy thông tin về hàng chứa giá trị tìm kiếm, bạn phải khóa tham chiếu table_array bằng cách nhấn phím F4. Bảng sẽ được cố định có dạng $ $.
- Thêm vào hoặc xóa bớt hàng mới
– Lưu ý là nếu bạn thêm một hàng mới vào trong bảng hay mảng mà nó có thể thay đổi vị trí hàng bạn chọn kết quả trả về ban đầu thì bạn nên xác định lại chỉ số vị trí của hàng.
– Chẳng hạn bạn chèn giữa hàng Điểm và Xếp loại là hàng Hạnh kiểm thì lúc đó hàng Xếp loại sẽ là hàng số 3, hàng Điểm là hàng số 1 và hàng Hạnh kiểm là hàng số 2. Vậy nên bạn phải đổi công thức nhập.
– Cách giải quyết là khoá bảng để tránh việc người sử dụng thêm một hàng mới vào hoặc sử dụng công thức INDEX/MATCH thay cho HLOOKUP.
Xem thêm: Hướng dẫn cách sử dụng hàm HLOOKUP
- Trùng lặp trong bảng
– Hàm HLOOKUP trong Excel chỉ có thể trả về một giá trị là giá trị đầu tiên trong bảng khớp với giá trị tìm kiếm.
– Nếu có một vài giá trị giống nhau trong bảng, chọn một trong các giải pháp mà phù hợp với bạn nhất:
+ Loại bỏ trùng lặp bằng cách dùng công cụ của Excel
+ Nếu bạn muốn trùng lặp được giữ nguyên trong dữ liệu, tạo PivotTable để nhóm và lọc các dữ liệu theo yêu cầu của mình
+ Sử dụng mảng thay cho bảng để thu hẹp vùng tìm kiếm.
- Khoảng trắng
– Khi công thức HLOOKUP của bạn hoàn toàn đúng nhưng lỗi #N/A vẫn bị trả về, hãy kiểm tra lại bảng và giá trị tìm kiếm xem có kí tự trắng nào không. Bạn có thể nhanh chóng loại bỏ các khoảng trống bằng cách sử dụng hàm TRIM trong Excel.
- Số được cài đặt dạng văn bản
– Nếu chuỗi văn bản giống như dạng số thì là một khó khăn khác khi sử dụng công thức Excel.
- Giá trị tìm kiếm vượt quá 255 kí tự
– Tất cả các hàm tìm kiếm trong Excel chỉ hoạt động khi giá trị tìm kiếm dưới 255 kí tự. Giá trị tìm kiếm dài hơn sẽ trả lại kết quả là lỗi #VALUE!.
- Đường dẫn đầy đủ tới bảng tính không được nêu rõ
– Nếu bạn thực hiện hàm HLOOKUP từ một bảng tính khác, nên nhớ rằng bạn cần phải cung cấp đầy đủ đường dẫn tới nó. Vấn đề này bạn phải tìm hiểu cách sử dụng dữ liệu bảng từ 2 sheet khác nhau.
- Sai tham số
– Dưới đây là một vài lỗi phổ biến khi nhập sai tham số
- Nếu row_index_num nhỏ hơn 1, lỗi #VALUE! sẽ được trả lại
- Nếu row_index_num lớn hơn số hàng trong bảng tìm kiếm, lỗi #REF! sẽ được trả lại
- Nếu bạn tìm kiếm với kết quả khớp tương đối và lookup_value nhỏ hơn giá trị nhỏ nhất trong hàng đầu tiên của table_array, lỗi #N/A sẽ được trả lại
Vậy là ở bài viết này chúng mình đã chia sẻ cho bạn cách dùng làm HLOOKUP trong excel. Hy vọng với bài viết này bạn đã hiểu được công thức hàm HLOOKUP. Để sử dụng hàm HLOOKUP thành thạo hơn, bạn nên tìm kiếm bài tập về hàm HLOOKUP trên google để luyện tập nhé.
