Nội dung chính
Hàm Hlookup về cơ bản có cú pháp và chức năng tương tự như hàm Vlookup và được thiết kế để cho phép người dùng tìm kiếm dữ liệu trong bảng tính Excel có chứa tiêu chí hoặc thông tin cụ thể. Khác với Vlookup tra cứu theo cột thì hàm Hlookup sẽ tra cứu theo hàng.
1. Hàm Hlookup trong excel là gì?
Hàm Hlookup là hàm được sử dụng phổ biến trong Excel. Hàm được sử dụng dưới nhiều phiên bản Microsoft Excel từ đời mới nhất đến cũ hơn.
Công thức thực hiện hàm Hlookup:
=HLOOKUP (lookup_value, table array, row_index_num, [range_lookup])
Trong đó:
- Lookup_value (bắt buộc phải có): Là giá trị cần tìm hay con gọi là ô tham chiếu.
- Table_array (bắt buộc phải có): Một bảng tra cứu chứa hai hoặc nhiều hàng dữ liệu. Nó có thể là một mảng được đặt tên thông thường hoặc một bảng tính Excel. Giá trị tra cứu phải được đặt trong hàng đầu tiên của table_array bảng tìm kiếm giá trị gồm hai hàng dữ liệu trở lên. Có thể là mảng thường, được đặt tên hoặc bảng Excel. Các giá trị tìm kiếm phải được đặt ở hàng đầu tiên của table_array
- Row_index_num (bắt buộc): Số hàng trong table_array mà các giá trị được trả về. Ví dụ: đặt row_index_num thành 2 để trả về các kết quả phù hợp từ hàng thứ hai.
- Range_lookup (không bắt buộc): trả về giá trị logic True hoặc False.
Nếu TRUE hoặc bị bỏ qua, các kết quả phù hợp gần đúng sẽ được trả về. Nghĩa là, nếu không tìm thấy kết quả phù hợp chính xác, hàm hlookup trả về giá trị lớn nhất tiếp theo nhỏ hơn giá trị look_up.
Nếu FALSE, chỉ các kết quả phù hợp chính xác mới được trả về. Nếu giá trị hàng được chỉ định không khớp chính xác với giá trị tra cứu, hàm hlookup trả về lỗi # N/A.
2. Những điều bạn cần biết về hàm Hlookup trong excel
Bạn cần lưu ý điều này 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. Sử dụng công thức Index hoặc Match, nếu bạn cần tìm ở những vị trí khác.
- Hlookup trong Excel sẽ không phân biệt được chữ thường và chữ hoa vì thế bạn nên dùng thêm hàm EXACT để kiểm tra.
- Nếu range_lookup được đặt thành TRUE hoặc để trống (kết quả khớp tương đối), các giá trị ở hàng đầu của table_array phải được xếp thành thứ tự tăng dần (A-Z) từ trái sang phải.
3. Sự khác nhau giữa Vlookup và Hlookup
Cả hai hàm Hlookup và Vlookup đều được sử dụng để tra cứu các giá trị. Nhưng chúng hoạt động khác nhau. Tên của hai chức năng khác nhau có thể dễ dàng nhận biết bằng tên viết tắt của chúng. “H” là viết tắt của chiều ngang (horizontal ) và “V” là viết tắt của chiều dọc ( vertical).
Vì vậy, hãy sử dụng hàm Vlookup để tìm giá trị cột ở bên trái của dữ liệu bạn đang tìm kiếm. Hàm Hlookup được sử dụng để tìm các giá trị theo chiều ngang. Tìm giá trị trong hàng đầu tiên của bảng và trả về giá trị trong hàng được chỉ định của cùng một cột.
4. Khi nào sử dụng Vlookup, khi nào sử dụng Hlookup
Khi nào bạn nên sử dụng Vlookup và khi nào thì bạn nên sử dụng Hlookup? Chắc hẳn bạn đang rất bối rối trước câu hỏi này. Có một cách dễ dàng để xác định nó dựa vào đối tượng lookup_value và cấu trúc của mảng bảng Table_Array (bảng chứa dữ liệu tra cứu và kết quả tìm kiếm).
- Nếu lookup_value nằm trong cột đầu tiên của bảng, hãy sử dụng Vlookup
- Sử dụng hlookup nếu lookup_value nằm ở hàng đầu tiên của bảng.
5. Ứng dụng của Hlookup trong excel
Tìm các giá trị theo từng hàng với đối sánh gần đúng và chính xác.
Hàm HLOOKUP trong Excel có thể tìm các kết quả phù hợp chính xác và không chính xác dựa trên giá trị của tham số range_lookup.
- TRUE hoặc bỏ trống: so sánh kết quả có độ khớp tương đối
- FALSE: kết quả độ khớp chính xác
Đặt tham số thành FALSE cho phép hàm trả về giá trị khớp tương đối, đó là các giá trị gần nhất nhỏ hơn giá trị tìm kiếm. Nếu không tìm thấy kết quả phù hợp chính xác nào. ĐÚNG hoặc để trống #N/A.
Mặt khác, trò chơi trên tường dễ sử dụng hơn vì bạn không phải sắp xếp dữ liệu ở hàng đầu tiên. Nếu không, lỗi #N/A được trả về nếu không tìm thấy kết quả phù hợp chính xác.
Mẹo: Để ngăn người dùng hoảng sợ khi họ gặp lỗi #N/A, hãy bọc chức năng hlookup trong một chức năng IFERROR để gửi tin nhắn.
=INFERROR (HLOOKUP(B4,B1:I2,2,FALSE),“Xin lỗi, không tìm thấy kết quả”)
6. Cách dùng hàm Hlookup từ worksheet hoặc workbook khác
Nói chung, điều này có nghĩa là để truy xuất giá trị từng hàng từ một trang tính hoặc sổ làm việc khác, bạn cần cung cấp một tham chiếu ngoại tuyến đến hàm HLOOKUP
Để đối sánh từ một trang tính khác, bạn cần phải đặt trước tên của trang tính bằng “!”. nhận biết. Ví dụ:
=HLOOKUP (B$1, Duongkinh! $B$1:$I2, 2, FALSE)
Nếu tên trang tính chứa các ký tự không phải là chữ cái, bạn hãy đặt nó trong dấu ngoặc đơn như sau:
=HLOOKUP (B$1, ‘Đường kính’!$B$1:$I$2, 2, FALSE)
Tên bảng tính đặt trong dấu ngoặc vuông nếu chọn ở bảng tính khác:
=HLOOKUP (B$1, [BOOK1.xlsx]Đườngkính!$B$1:$I$2, 2, FALSE)
Khi truy xuất các giá trị từ một sổ làm việc đã đóng, bạn phải chỉ định đường dẫn đến sổ làm việc:
=HLOOKUP (B$1, ‘D:\Reports\[Book1.xlsx]Đườngkính’!$B$1:$I$2, 2, FALSE)
Mẹo: Thay vì gõ tên trang tính và bảng tính thủ công, bạn có thể chọn các ô trong trang tính khác và Excel sẽ tự động chèn tham chiếu ngoại tuyến vào hàm của bạn.
HLOOKUP với kết quả khớp một phần (sử dụng ký tự đại diện)
Như với Vlookup, hàm HLOOKUP chấp nhận các ký tự đại diện sau cho tham số lookup_value:
- Bất kỳ dấu chấm hỏi ký tự đơn nào (?)
- Dấu hoa thị (*) để khớp với chuỗi
Ví dụ: hàng 1 có danh sách tên khách hàng và hàng 2 có danh sách số thứ tự. Tôi muốn tìm số thứ tự của một khách hàng cụ thể, nhưng tôi không thể nhớ chính xác tên của khách hàng. Chữ cái “La”.
Cho rằng dữ liệu của bạn ở ô B1:I2 (table_array) và thứ tự các số ở hàng 2 (row_index_num), công thức như sau:
=HLOOKUP (“La*”, B1:I2, 2, FALSE)
Để làm cho công thức linh hoạt hơn, bạn có thể sử dụng các giá trị tra cứu cho các ô cụ thể. Ví dụ: trong B4, hãy thêm một ký tự đại diện vào ô này như sau:
=HLOOKUP (B4&“*”, B1:I2, 2, FALSE)
Chú ý:
- Để hàm HLOOKUP với kí tự đại diện đưa kết quả đúng, range_lookup phải được đặt chế độ FALSE
- Nếu table_array chứa nhiều hơn một giá trị khớp với kí tự đại diện, giá trị đầu tiên tìm được sẽ được trả về
7. Ô tham chiếu tuyệt đối và tương đối trong HLookup
Khi viết hàm hlookup cho một ô, bạn có thể không quan tâm đến việc bạn sử dụng tham chiếu ô tuyệt đối hay tương đối. Tuy nhiên, khi sao chép công thức trên nhiều tham chiếu ô, bạn phải có khả năng phân biệt giữa hai tham chiếu ô.
- Một table_array luôn được cố định bởi các tham chiếu ô tuyệt đối bằng cách sử dụng ký hiệu ‘$’ (ví dụ:$B$1:$I$2).
- Các tham chiếu lookup_value là tương đối hoặc hỗn hợp dựa trên logic nghiệp vụ
Để rõ ràng, chúng ta hãy xem xét kỹ hơn hàm hlookup khi lấy dữ liệu từ một trang tính khác. Cú pháp như sau:
=HLOOKUP(B$1,Duongkinh!$B$1:$I$2,2,FALSE)
Công thức trên sử dụng tham chiếu tuyệt đối ($B$1:$I$2) trong các trường bảng. Điều này là do các công thức cần phải không đổi khi được sao chép từ các ô khác.
Trong lookup_value (B$1) giá trị tham chiếu là tên hành tinh nằm trong cùng một hàng (hàng 1) nhưng ở một cột khác (B thành I) và chúng ta cần thay đổi cột tra cứu, do đó, tham chiếu cột tương đối và hàng tuyệt đối Kết hợp và khớp các tài liệu tham khảo. Dựa trên vị trí tương đối của ô chứa công thức đã sao chép.
8. Index/Match, một công thức mang hiệu quả khác so sánh với Hlookup
Tuy nhiên, hàm HLOOKUP có một số bất tiện. Bất tiện chính là nó không thể tìm thấy các giá trị bên ngoài giới hạn trên và phải sắp xếp các giá trị một cách tuần tự khi tìm thấy một kết quả phù hợp tương đối. Và để bù đắp nhược điểm đó, tạ sử dụng kết hợp hàm INDEX và MATCH sẽ giải quyết được những vấn đề này.
Cú pháp:
=INDEX (nơi chứa giá trị về, MATCH (giá trị tìm kiếm, nơi tìm giá trị, 0))
Ví dụ minh họa: Giả sử bạn đang tìm một kết quả khớp ở hàng 2 (B2:I2), giá trị tìm kiếm ở ô B7, và muốn giá trị trả về từ hàng 1 (B1:I1).
Cú pháp thực hiện như sau: =INDEX (B1:I1, MATCH (B7, B2:I2, 0))
9. Phân biệt dạng chữ trong Hlookup
Như đã biết, hàm HLOOKUP không phân biệt được chữ thường và chữ in hoa. Chính vì thế bạn nên dùng hàm EXACT để so sánh các ô và đặt hàm này bên trong các hàm INDEX, MATCH. (Sử dụng trong khi ký tự chữ quan trọng).
Công thức được viết như sau:
=INDEX (hàng chứa giá trị trả về, MATCH (TRUE, EXACT (hàng tìm kiếm, giá trị tìm kiếm), 0))
Ví dụ minh họa: Giả sử mảng tìm kiếm là B1:I1, giá trị tìm kiếm của bạn ở ô B4, mảng giá trị trả về là B2:I2. Ta viết cú pháp như sau: =INDEX (B2:I2, MATCH (TRUE, EXACT (B1:I1, B4), 0))
Chú ý: Thao tác thực hiện trên sử dụng công thức mảng, vì thế bạn phải ấn tổ hợp Ctrl + Shift + Enter sau khi gõ công thức.
10. Các lỗi phổ biến thường gặp làm cho hlookup không chạy được
Hàm Hlookup là một hàm phức tạp. Điều này là do công thức có quá nhiều thành phần nên lỗi #N/A, #VALUE hoặc #REF thường xuyên xảy ra. Nếu hàm HLOOKUP không hoạt động, có thể do một trong các lỗi sau
HLOOKUP không thể tìm thấy dòng trên
Ngay cả khi bạn quên tất cả các chi tiết về tra cứu theo chiều ngang trong Excel, hãy luôn nhớ rằng hlookup chỉ có thể tìm kiếm hàng đầu tiên của bảng tính. Lỗi N/A được trả về nếu giá trị tra cứu nằm trong bất kỳ hàng nào khác. Để tránh lỗi này, bạn nên sử dụng công thức INDEX/MATCH.
Đối sánh tương đối và chính xác
Khi bạn tìm kiếm các giá trị theo chiều ngang hoặc chiều dọc trong Excel, hầu hết thời gian bạn đang tìm kiếm một giá trị cụ thể và bạn muốn một kết quả khớp chính xác. Khi tìm kiếm theo đối sánh tương đối (range_lookup được đặt thành TRUE hoặc trống), hãy nhớ đặt các giá trị hàng theo thứ tự tăng dần.
Sao chép công thức sẽ thay đổi bảng tham chiếu
Nếu bạn sử dụng nhiều HLOOKUP để truy xuất thông tin về các hàng chứa giá trị tra cứu, bạn phải khóa tham chiếu table_array.
Thêm hoặc bớt các hàng mới
Để hiểu lý do tại sao việc thêm một hàng mới lại phá vỡ biểu thức Hlookup, chúng ta cần hiểu cách HLOOKUP truy xuất thông tin về giá trị tra cứu dựa trên chỉ mục của hàng được chỉ định.
Giả sử bạn muốn nhận được số tiền bán hàng dựa trên ID sản phẩm. Các số này ở hàng 4, vì vậy hãy nhập 4 cho tham số row_index_num.
Nhưng khi tôi thêm một dòng mới, nó sẽ trở thành dòng 5 và hlookup ngừng hoạt động. Tôi gặp vấn đề tương tự khi xóa một hàng trong bảng.
Một cách giải quyết là khóa bảng để ngăn người dùng thêm hàng mới hoặc sử dụng biểu thức INDEX / MATCH thay vì hlookup. INDEX / MATCH yêu cầu bạn chỉ định các hàng để tìm và trả về giá trị dưới dạng một mảng tham chiếu và Excel có thể khớp các tham chiếu đó theo cách thủ công.
Vì vậy, bạn không phải lo lắng về việc thêm hoặc xóa các hàng như bạn làm với công thức Hlookup.
Trùng lặp trong bảng
Hàm HLOOKUP trong Excel chỉ có thể trả về giá trị là giá trị đầu tiên trong bảng khớp với giá trị tra cứu.
Nếu bạn có một số giá trị tương tự trong bảng, hãy chọn một trong những giải pháp phù hợp nhất:
- Loại bỏ các bản sao bằng công cụ Excel
- Nếu bạn muốn giữ dữ liệu trùng lặp, hãy tạo một bảng tổng hợp để nhóm và lọc dữ liệu theo ý muốn
- Loại bỏ các giá trị trùng lặp khỏi mảng tra cứu bằng cách sử dụng công thức mảng
Khoảng trắng
Nếu biểu thức hlookup của bạn đúng và nó trả về lỗi # N / A, hãy kiểm tra lại bảng của bạn và tìm kiếm các giá trị cho khoảng trắng. Bạn có thể sử dụng hàm TRIM của Excel để xóa nhanh khoảng trắng.
Đườ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ó.
Sai tham số
Hàm hlookup là một hàm phức tạp và cần được sử dụng một cách thận trọng. Dưới đây là một số lỗi thường gặp khi nhập sai thông số:
- #VALUE! Được trả về nếu row_index_num nhỏ hơn 1
- #REF! Được trả về nếu row_index_num lớn hơn số hàng trong bảng tra cứu
- Nếu bạn tìm kiếm theo đối sánh tương đối và giá trị tra cứu nhỏ hơn giá trị tối thiểu trong hàng đầu tiên của table_array, thì sẽ trả về lỗi # N / A.
Trên đây là toàn bộ các kiến thức về sử dụng hàm Hlookup. Chúc các bạn thực hiện thành công.