Hàm Hlookup trong excel là hàm khá thông dụng cho những người hay sử dụng Excel để thống kê, hiểu theo cách đơn giản thì đây là hàm tìm kiếm thông tin theo hàng trên một bảng dữ liệu từ một điều kiện tìm kiếm cho trước trong Microsoft Excel. Với hai giá trị tìm kiếm là giá trị tương đối và giá trị tuyệt đối, sử dụng hàm này công việc tìm kiếm và tra cứu dữ liệu của bạn sẽ nhanh chóng, hiệu quả hơn bao giờ hết.
Hàm Hlookup trong excel
Cú pháp hàm Hlookup: Khi dùng excel đa phần là ta sử dụng giao diện bằng tiếng Anh. Hơn nữa từ Excel 2007 trở đi mỗi khi viết hàm thì Excel sẽ có phần gợi ý tên hàm bằng tiếng Anh, vì thế việc đưa ra hai cú pháp tiếng Anh và tiếng Việt dưới đây, sẽ giúp bạn hiểu rõ về hàm Hlookup hơn và hiệu suất làm việc tốt hơn.
Cú pháp tiếng Anh: = Hlookup([Lookup_value, Table_array, Row_index_num,[Range_lookup])
Trong đó:
- Lookup_value (bắt buộc): Là giá trị cần lấy để đem tìm kiếm trong bảng dò, có thể là ô tham chiếu, hay một giá trị hoặc là chuỗi văn bản.
- Table_arry (bắt buộc): Vùng chứa dữ liệu trong bảng phụ mà bạn cần tìm, 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á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): Là số hàng trong bảng có chứa dữ liệu nếu row_index_num là 1 kết quả sẽ trả về giá trị hàng thứ nhất, nếu là 2 sẽ trả về giá rị hàng thứ 2 trong bảng,… nếu nhỏ hơn 1 thì hàng Hlookup sẽ trả về giá trị lỗi #Value, nếu lớn hơn số hàng trong table_array sẽ trả về giá trị lỗi #Ref.
- Range_lookup (không bắt buộc): Tùy chọn lập luận logic bạn có thể để True hoặc False. Trong đó: True (điền số 1 hoặc để trống): Để hàm Hlookup dò tìm giá trị tương đối, và False (điền số 0): Để hàm Hlookup dò tìm giá trị tuyệt đối.
Cú pháp tiếng Việt: = Hlookup (Giá trị đem dò, bảng giá trị dò, số thứ tự hàng cần lấy, phạm vi tìm kiếm).
Trong đó:
- Bảng giá trị dò: phải để ở dạng địa chỉ tuyệt đối bằng cách chọn bảng và nhấn F4, lưu ý rằng khi chọn không quét tiêu đề.
- Phạm vi tìm kiếm: Nếu là True (1) là dò tìm tương đối, ngược lại là False(0) là thực hiện dò tìm chính xác.
Cách dùng hàm Hlookup trong Excel
Là hàm phức tạp bởi có rất nhiều thành phần trong công thức, để hiểu rõ hơn về cách dùng hãy tham khảo bài tập dưới đây. Đây là bảng thống kê nhập nhiên liệu trong tháng 6 của công ty xăng dầu.
Cách sử dụng hàm Vlookup trong Excel
Trên thực tế “Hãng sản xuất” sẽ được để ở một sheet riêng, tuy nhiên để dễ theo dõi thì nó sẽ được để cùng với bảng thống kê nhập. Trên bảng trên bạn chỉ cần điền mã hàng còn tên hàng, hãng sản xuất, đơn giá thì không cần điền mà sử dụng hàm dể tìm kiếm từ các danh mục tương ứng. Theo bảng trên thì mã hàng sản xuất được nhập ở dòng thứ 10, còn tên hãng sản xuất được nhập ở dòng thứ 11, vậy để tìm tên hãng sản xuất bạn cần phải dùng hàm tìm kiếm theo dòng, đó chính là hàm Hlookup.
Kết quả trả về khi sử dụng hàm Hlookup
Có hai cách tìm bạn cần chú ý để tìm được tên hàng, hãng sản xuất, hay đơn giá đó là:
Cách 1: Tìm kiếm thủ công bằng hàm Hlookup
Bạn nhập công thức Hlookup để tìm kiếm tên hãng sản xuất cho mặt hàng “D00BP”:
Tại ô E4: =Hlookup(“BP”,B10:F11,2,0)
Trong đó:
- BP: Mã hãng sản xuất.
- B10:F11: Là vùng tìm kiếm chính là bảng “Hãng sản xuất”.
- 2: Chính là số thứ tự của hãng sản xuất tính từ dòng chứa mã hàng.
- 0: Thực hiện dò tìm chính xác.
Cách 2: Tìm kiếm theo công thức bằng Hlookup
Nhiệm vụ bây giờ của bạn là cần tách được 2 ký tự cuối cùng của mã hàng để có thể tìm ra mã hàng sản xuất. Vì thế, ta không phải nhìn rồi sử dụng nguyên Hlookup như cách trên mà dùng hàm Hlookup kết hợp hàm Right với cú pháp:
Tại E4: =Hlookup(Right(B4,2),$B$10:$F$11,2,0)
Trong đó:
- Right(B4,2): Tách chuỗi văn bản trong hàng B4 và lấy 2 ký tự cuối.
- $B$10:$F$11: bảng dò tìm (bảng phụ).
- 2: Là số thứ tự hàng trên bảng dò.
- 0: Là kiểu dò tìm chính xác được quy định trong hàm.
Tìm kiếm kết quả hàng loạt theo công thức sử dụng hàm Hlookup
Những lỗi thường gặp khi sử dụng hàm Hlookup và cách khắc phục
- Hàm Hlookup không thể tìm kiếm được các hàng phía trên: Theo quy tắc, hàm Hlookup trong excel chỉ có thể tìm hàng đầu tiên trong bảng, vì thế nếu bạn sử dụng tìm kiếm các giá trị ở các hàng khác sẽ bị lỗi N/A trả ngược lại. Để khắc phục trường hợp này bạn có thể sử dụng công thức INDEX/MATCH.
- Tìm kiếm tương đối cho kết quả không chính xác: Khi tìm kiếm trong bảng với kết quả tương đối bạn cần xếp các giá trị trong hàng theo thứ tự tăng dần.
- Khi sao chép công thức bảng tham chiếu bị thay đổi: Để tránh xảy ra trường hợp trên bạn cần khóa tham chiếu table_array.
- Giá trị tìm kiếm vượt quá 255 ký tự: Tất các hàm hoạt động khi giá trị tìm kiếm dưới 255 ký tự, nếu dài hơn nó sẽ tự trả về kết quả #Value. Trong trường hợp này bạn cần sử dụng công thức INDEX/MATCH để không bị giới hạn ký tự.
- Hàm Hlookup chứa khoảng trắng: Khi cú pháp của bạn đúng nhưng kết quả trả về vẫn là #N/A hãy kiểm tra lại bảng và giá trị tìm kiếm xem có khoảng trắng không. Để loại bỏ khoảng trắng này hãy sử dụng hàm TRIM.
Cách kết hợp hàm Hlookup và hàm If, Right, Left
Để biết cách sử dụng hàm Hlookup kết hợp hàm if, right, left trong excel bạn hãy tham khảo ví dụ dưới đây để hiểu rõ hơn:
Ví dụ: Với bảng thanh toán tiền điện như hình, hãy tính số điện tiêu thụ trong và ngoài định mức dựa trên bảng định mức trong hình.
Cách kết hợp hàm Hlookup với hàm if, left, right trong excel
Nếu số tiêu thụ nhỏ hơn số định mức thì số trong định mức = số tiêu thụ, nếu lớn hơn thì số trong định mức = số định mức và ngoài định mức thì số tiêu thụ = số định mức.
Nhiệm vụ của chúng ta sẽ là đi tìm số tiêu thụ định mức để so sánh với số tiêu thụ như vậy sẽ biết được số trong và ngoài định mức là bao nhiêu.
Để giải quyết vấn đề này ta sử dụng hàm Hlookup nâng cao đó là kết hợp với hàm if, left, right để tìm ra kết quả như sau:
=Hlookup(Left(D3,2),$I$18:$N$22,IF(RIGHT(D3,1)=”A”,2,IF(RIGHT(D3,1)=””B”,3,IF(RIGHT(D3,1)=”C”,4,5))),0)
Trong đó:
- Left(D3,2), (RIGHT(D3,1): Tách chuỗi văn bản trong hàng D3 lấy 1 hoặc 2 ký tự.
- $I$18:$N$22: Bảng dò tìm (bảng phụ).
- 2,3: Là số thứ tự trên bảng dò.
- A: Là dòng thứ 2 tính từ dòng 18.
- B: Là dòng thứ 3 tính từ dòng 18.
- C: Là dòng thứ 4 tính từ dòng 18.
- D: Là dòng thứ 5 tính từ dòng 18.
- 0: Là kiểu dò chính xác được quy định trong hàm.
Kết quả sử dụng hàm hlookup kết hợp với if, left, right để tìm số điện tiêu thụ
Khi sử dụng hàm hlookup thì dòng đầu tiên là dòng 18 của vùng dữ liệu được cố định trong bảng dò tìm chứa 2 ký tự đầu của mã hộ NN hoặc BC do đó chúng ta cần sử dụng điều kiện tìm kiếm để tách ra 2 ký tự đầu của mã từng hộ dân. Để tách ra ký tự thứ 1 tính từ bên phải mã hộ dân ta chỉ cần dùng hàm right để tách ký tự sau đó dùng hàm If để so sánh, tương tự như vậy để tách ký tự tính từ bên trái mã hộ dân chỉ cần dùng hàm left sau đó sử dụng If. Ví dụ trên không chỉ là sự kết hợp với hàm IF mà còn cho chúng ta thấy được cách sử dụng hàm Hlookup kết hợp hàm Left và Right trong Excel.
Sự khác nhau giữa hàm Vlookup và hàm Hlookup
Hàm Hlookup và Vlookup trong excel cùng để tìm kiếm một giá trị tuy nhiên chúng khác nhau trong cách hoạt động. Bạn sử dụng Vlookup để tìm giá trị theo cột ở bên trái của dữ liệu muốn tìm kiếm, còn Hlookup dùng để tìm kiếm giá trị theo hàng ngang nó dò tìm các giá trị ở hàng đầu tiên của bảng sau đó trả lại giá trị ở hàng được chỉ định trong cùng một cột.
Để thấy rõ sự khác biệt giữa hai hàm này, chúng ta tìm hiểu ví dụ đơn giản dưới đây với bảng thông tin cơ bản về hành tinh của hệ mặt trời, tìm đường kính của hành tinh có tên trong ô hành tinh “Sao Hỏa”.
Sự khác nhau giữa hàm Hlookup và Vlookup trong Excel
Cũng cùng một kết quả cho giá trị giống nhau nhưng cách hoạt động của hai hàm này lại hoàn toàn khác biệt:
Sử dụng hàm Vlookup tìm giá trị
Tại ô B8: =VLOOKUP(B7, A2:B5,2,FALSE)
Trong đó:
- Lookup_Value là B7 chứa tên hành tinh muốn tìm.
- Table_Array là A2:B5 bảng chứa giá trị cần tìm (bảng phụ).
- Col_index_num là 2: Thứ tự cột cần lấy dữ liệu trên bảng dò, trong bảng đường kính ở cột thứ 2.
- Range_lookup là False vì đường kính sẽ không bị thay đổi dù xuất hiện ở các cột mới, ô mới vì thế ta sử dụng giá trị tuyệt đối trong trường hợp này là chính xác.
Sử dụng hàm Hlookup để tìm giá trị
Tại B6: =HLOOKUP(B5, A2:B15, 2,FALSE)
Trong đó:
- Lookup_Value là B5 ô chứa tên hành tinh muốn tìm.
- Table_Array là A2:B15: Bảng chứa giá trị cần tìm (bảng phụ).
- Row_index_num là 2: vì đường kính ở hàng thứ 2 trong bảng.
- Range_lookup là False bởi hàng thứ nhất của bảng không được sắp xếp theo thứ tự A->Z nên bạn chỉ có thể tìm giá trị chính xác.
Kết quả sẽ cho chúng ta thấy ở bảng hình bên trên.
Bài viết trên chúng tôi đã giới thiệu tới các bạn về hàm Hlookup, cách sử dụng, cách kết hợp hàm, và sự khác nhau giữa hàm hlookup vàvlookup. Để tìm hiểu rõ hơn về sự khác nhau này bạn có thể tham khảo hàm vlookup trong excel và cách sử dụng giúp nâng cao kiến thức văn phòng của bản thân, thúc đẩy hiệu quả công việc được cao hơn.
Thường xuyên theo dõi website của chúng tôi để tìm hiểu thêm các hàm khác như hàm sum, hàm index/match, hàm sumif… và tất cả các thủ thuật sử dụng excel để hỗ trợ công việc đạt kết quả cao.
