Tương tự như người anh em VLOOKUP, hàm HLOOKUP trong Excel cũng là một hàm cực kì quan trọng và cần thiết mà người sử dụng Excel cần biết đến. Khi thành thạo HLOOKUP, bạn sẽ thấy mọi chuyện dường như trở nên đơn giản hơn rất nhiều dù là bảng tính có tới bao nhiêu dòng hay cột đi chăng nữa. Nếu bạn chưa biết rõ về hàm này thì chần chừ gì mà chưa đọc ngay bài viết này của ThuthuatOffice?
Bạn đang đọc: Dễ dàng chinh phục hàm HLOOKUP trong Excel trong 5 phút
Tóm tắt nội dung bài viết
- Hàm HLOOKUP trong Excel là gì?
- Sử dụng hàm LOOKUP trong Excel khi nào?
- Công thức hàm HLOOKUP trong Excel
- Cách sử dụng hàm HLOOKUP trong Excel
- Cách sử dụng hàm HLOOKUP và LEFT
- Hàm HLOOKUP kết hợp hàm IF
- Lưu ý khi áp dụng hàm HLOOKUP tìm kiếm giá trị
- Ví dụ hàm HLOOKUP trong Excel
- Sử dụng hàm HLOOKUP trong Excel để trích bảng lương cho nhân viên
- Sử dụng hàm HLOOKUP trong Excel để xếp loại học sinh dựa trên điểm
- Hàm HLOOKUP và hàm VLOOKUP
- Các lý do chính khiến hàm HLOOKUP không chạy được
Hàm HLOOKUP trong Excel là gì?
Trong Excel, hàm HLOOKUP là hàm sẽ giúp bạn dò tìm giá trị như mong ước từ một bảng tính và tự động hóa điền sang một bảng tính mới hoặc một vị trí độc lạ. Điều này giúp bạn hoàn toàn có thể tiết kiệm chi phí được vô số thời hạn khi không cần phải tự mình dò tìm và copy từng tài liệu một .
HLOOKUP là từ viết tắt của H – Horizontal – hàng ngang và LOOKUP – Look Up – Dò tìm trong tiếng Anh. Tức là nó sẽ dò tìm dữ liệu theo dạng hàng ngang, tuy nhiên khi trả về sẽ trả về theo dạng hàng dọc.
Sử dụng hàm LOOKUP trong Excel khi nào?
Hàm HLOOKUP được sử dụng khi bạn cần dò tìm dữ liệu trong một bảng, theo phạm vi theo hàng ngang (từ trái qua phải) và trả về dữ liệu tương ứng theo hàng dọc (từ trên xuống dưới).
Bạn hoàn toàn có thể sử dụng HLOOKUP khi muốn tìm tên mẫu sản phẩm, đơn giá, số lượng, .. dựa trên mã có sẵn hoặc tìm tên nhân viên cấp dưới, xếp loại nhân viên cấp dưới dựa một vài tiêu chuẩn cố định và thắt chặt .
Công thức hàm HLOOKUP trong Excel
Hàm HLOOKUP có cú pháp như sau :
= HLOOKUP (Lookup_value, Table_array, Row_index_ num, Range_lookup)
Trong đó, bạn cần chăm sóc tới :
- Lookup_value: Giá trị cần dò tìm
- Table_array: Vùng giới hạn dò tìm
- Row_index_num: Số thứ tự của hàng lấy dữ liệu trong bảng cần dò tìm, tính từ trên xuống dưới
- Range_lookup (TRUE hoặc FALSE): tìm kiếm chính xác hay tương đối trong bảng giới hạn. Có thể bỏ qua, và khi bỏ qua thì Excel mặc định là TRUE.
Với Range_lookup = TRUE : Excel sẽ trả về hiệu quả dò tìm tương đối .
Với Range_lookup = FALSE : Excel sẽ trả về hiệu quả dò tìm đúng chuẩn. Nếu không tìm thấy thì Excel sẽ trả về tác dụng # N / A .
Cách sử dụng hàm HLOOKUP trong Excel
Cách sử dụng hàm HLOOKUP và LEFT
Cú pháp của hàm LEFT là :
= LEFT(text; n)
Đây là hàm sẽ giúp bạn lấy được kí tự thứ n tính từ bên trái của văn bản trong phần text. Khi kết hợp hàm HLOOKUP và LEFT, công việc điền dữ liệu của bạn sẽ diễn ra nhẹ nhàng và nhanh chóng hơn bao giờ hết!
Chẳng hạn, bạn cần điền tên ngành khi biết mã lớp. Mã lớp gồm có cả phần chữ và số, nhưng mà ngành chỉ gồm có phần chữ. Khi đó bạn sẽ cần đến sự tích hợp của HLOOKUP và LEFT .
Công thức bạn điền vào sẽ là :
=HLOOKUP(LEFT(C4;4);$A$12:$F$13;2;FALSE)
Trong đó :
- C4: địa chỉ ô cần tách lấy ký tự
- 4: cần tách đến ký tự thứ mấy tính từ trái qua
- LEFT(C4;4): tách lấy 4 ký tự đầu tiên (từ trái qua) của dữ liệu trong ô Mã lớp
- $A$12:$F$13: vùng dữ liệu cần tìm kiếm
- 2: số thứ tự của dòng Tên ngành cần điền vào. Dòng Mã ngành sẽ được tính là 1 nên dòng Tên ngành sẽ là 2
- FALSE: dữ liệu điền vào cần chính xác tuyệt đối
Kết quả nhận được trong ô :
Copy công thức đến những dòng còn lại và bạn đã hoàn thành xong :
Hàm HLOOKUP kết hợp hàm IF
Khi bạn phối hợp HLOOKUP và IF, bạn sẽ thuận tiện hoàn toàn có thể xếp loại và điền thông tin dựa trên điều kiện kèm theo mà bạn đưa ra .
Chẳng hạn, bạn cần đánh giá xem học sinh của bạn đạt hay không đạt trong kì thi các cấp với dữ liệu ở hai bảng sau đây.
Khi đó, nếu học viên thi cấp Trường và đạt từ 500 điểm trở lên, học viên sẽ được điền chữ “ Đạt ” trong cột nhìn nhận, ngược lại, học viên sẽ được điền chữ “ Không đạt ”. Tương tự như vậy cho kỳ thi cấp Thành phố và Tỉnh .
Công thức điền vào cột Đánh giá sẽ là:
=IF(E4>=HLOOKUP(D4;$C$12:$F$13;2;FALSE);”Đạt”;”Không đạt”)
Trong đó ,
- E4: địa chỉ ô tính chứa điểm của học sinh
- D4: địa chỉ ô tính chứa điều kiện cần dò tìm, trong trường hợp này là tên kỳ thi
- $C$12:$F$13: địa chỉ vùng dữ liệu cần dò tìm
- 2: số thứ tự của dòng dữ liệu cần điền so với dữ liệu cần tìm. Dòng tên kỳ thi là dòng cần tìm, nó sẽ là dòng thứ 1, vậy nên dòng điểm đậu là dòng cần điền sẽ là ở vị trí thứ 2
- FALSE: dữ liệu cần tìm phải chính xác tuyệt đối
- “Đạt”: nếu điều kiện đúng thì ghi chữ “Đạt”. Trong trường hợp này, nếu điểm của học sinh lớn hơn hoặc bằng với số điểm đậu của kỳ thi tương ứng thì học sinh sẽ đạt
- “Không đạt”: nếu điều kiện sai thì ghi chữ “Không đạt”. Trong trường hợp này, nếu điểm của học sinh nhỏ hơn số điểm đậu của kỳ thi tương ứng thì học sinh sẽ không đạt
Kết quả nhận được ở ô tính đầu tiên:
Xem thêm: one size là bao nhiêu kg mặc vừa
Copy công thức đến các dòng còn lại để hoàn thành điền thông tin cho cột Đánh giá:
Lưu ý khi áp dụng hàm HLOOKUP tìm kiếm giá trị
Khi sử dụng hàm HLOOKUP trong Excel nhằm mục đích mục tiêu tìm kiếm giá trị, bạn cần quan tâm một số ít điều sau để bảo vệ tài liệu tìm kiếm nhận được đúng như mong ước và không xảy ra bất kỳ lỗi nào .
- Đảm bảo dữ liệu ở ô cần tìm và dòng đầu tiên trong bảng dữ liệu ở cùng một dạng (văn bản hoặc số).
- Đảm bảo khóa vùng dữ liệu tìm kiếm để địa chỉ không bị nhảy trong quá trình copy công thức.
- Đảm bảo lựa chọn đúng thứ tự của dòng cần điền so với dòng cần tìm. Dòng đầu tiên trong vùng dữ liệu bạn chọn tìm kiếm sẽ là 1.
Ví dụ hàm HLOOKUP trong Excel
Sử dụng hàm HLOOKUP trong Excel để trích bảng lương cho nhân viên
Giả sử, bạn có một bảng tài liệu tổng và bạn muốn trích ra tài liệu của một vài nhân viên cấp dưới như thế này :
Khi đó, bạn nên sử dụng hàm HLOOKUP để thực thi điền những thông tin vào bảng tài liệu mới thay vì copy từng dòng một. Chẳng hạn, nếu muốn điền vào ô số ngày công, bạn sẽ vận dụng công thức sau :
= HLOOKUP(B13;$A$1:$I$7;6;FALSE)
Trong đó :
- B13: là địa chỉ ô của dữ liệu cần tìm kiếm. Trong trường hợp này là tên của nhân viên.
- $A$1:$I$7: là địa chỉ vùng dữ liệu bạn cần tìm kiếm. Sau khi lựa chọn xong vùng dữ liệu, bạn hãy nhấn F4 để khóa vùng dữ liệu này lại. Khi đó ký hiệu $ sẽ hiện lên và dù bạn có copy công thức xuống các dòng dưới thì vùng dữ liệu này cũng không bị thay đổi.
- 6: là số thứ tự của dòng cần điền kí tự kể từ dòng có chứa dữ liệu cần tìm trong bảng tính. Trong trường hợp này, dữ liệu cần tìm là họ tên nên dòng họ tên trong bảng tính sẽ tính là 1, dòng cần điền dữ liệu là dòng Số ngày công, tính từ dòng họ tên xuống là dòng thứ 6, nên dữ liệu này sẽ cần điền số 6.
- FALSE: vì thông tin cần điền phải chính xác tuyệt đối nên tại đây điền FALSE.
Tương tự như vậy với phần Lương, bạn chỉ cần biến hóa số 6 thành số 7, tức là phần số thứ tự của tài liệu cần điền là ở dòng Lương thay vì dòng Số ngày công. Copy công thức này tới những ô còn lại để triển khai xong bảng tính :
Sử dụng hàm HLOOKUP trong Excel để xếp loại học sinh dựa trên điểm
Chẳng hạn bạn cần xếp loại học lực của học viên dựa trên điểm trung bình, bạn cần chuẩn bị sẵn sàng một bảng tài liệu chứa những con điểm như bên dưới :
Lúc này, trong cột xếp loại, bạn sẽ sử dụng công thức như sau :
= HLOOKUP(D4;$B$11:$F$12;2;TRUE)
Trong đó :
- D4: là địa chỉ ô của dữ liệu cần tìm kiếm. Trong trường hợp này là số điểm của học sinh.
- $B$11:$F$12: là địa chỉ vùng dữ liệu bạn cần tìm kiếm. Sau khi lựa chọn xong vùng dữ liệu, bạn hãy nhấn F4 để khóa vùng dữ liệu này lại. Khi đó ký hiệu $ sẽ hiện lên và dù bạn có copy công thức xuống các dòng dưới thì vùng dữ liệu này cũng không bị thay đổi.
- 2: là số thứ tự của dòng cần điền kí tự kể từ dòng có chứa dữ liệu cần tìm trong bảng tính. Trong trường hợp này, dữ liệu cần tìm là điểm nên dòng điểm trong bảng tính bên dưới sẽ tính là 1, dòng cần điền dữ liệu là dòng xếp loại, tính từ dòng điểm xuống là dòng thứ 2, nên dữ liệu này sẽ cần điền số 2.
- TRUE: vì bảng tính bên dưới của bạn đã được chia khoảng nên dữ liệu cần điền chỉ cần chính xác tương đối. Nghĩa là nếu điểm trên 0 và dưới 5, Excel sẽ điền Yếu, trên 5 và dưới 7, Excel sẽ điền Trung bình,…
Copy công thức xuống những dòng còn lại và hiệu quả là :
Hàm HLOOKUP và hàm VLOOKUP
Cả HLOOKUP và VLOOKUP đều có mang đặc thù dùng để tìm kiếm. Tuy nhiên hai hàm này đều có những sự khác nhau mà bạn cần phân biệt để thuận tiện sử dụng cho việc làm của mình .
| TÊN HÀM | HLOOKUP | VLOOKUP |
| Định nghĩa | Dò tìm dữ liệu theo dạng hàng ngang | Dò tìm dữ liệu theo dạng hàng dọc |
| Lookup_value ( Giá trị tìm kiếm, thành phần tiên phong trong công thức ) |
Tìm trong dòng đầu tiên của vùng dữ liệu | Tìm trong cột đầu tiên của vùng dữ liệu |
| Row/Column_index_ num ( Vị trí của giá trị cần điền so với giá trị cần tìm ) |
Nằm ở vị trí dòng thứ mấy kể từ dòng giá trị cần tìm trong bảng tính, với dòng giá trị cần tìm là 1 | Nằm ở vị trí cột thứ mấy kể từ cột giá trị cần tìm trong bảng tính, với cột giá trị cần tìm là 1 |
Sau khi đã phân biệt được kĩ càng hai hàm này, bảo vệ việc làm với bảng tính của bạn sẽ trở nên thuận tiện hơn khi nào hết .
Các lý do chính khiến hàm HLOOKUP không chạy được
Khi sử dụng hàm HLOOKUP trong Excel, sẽ có đôi lúc bạn gặp phải trường hợp hàm không chạy được. Trong đó, hầu hết là lỗi xảy ra bởi 3 nguyên do sau :
- Sai định dạng
Lỗi này tức là khi ô tính mà bạn trỏ địa chỉ tới trong công thức đang chứa tài liệu ở dạng số nhưng dòng tiên phong lại chứa tài liệu ở dạng chữ ( hoặc ngược lại ). Để khắc phục, bạn hoàn toàn có thể sử dụng kèm hàm TEXT trong công thức hoặc chỉnh sửa lại giá trị những ô tính cho trùng khớp .
- Thừa dấu cách ở cuối cùng trong điều kiện tìm kiếm
Nếu bạn đã sử dụng hàm HLOOKUP trong Excel và kiểm tra thật kĩ lưỡng để không có lỗi nào xảy ra nhưng tác dụng nhận được vẫn sai hoặc không có hiệu quả nào thì nguyên do hoàn toàn có thể nằm ở việc tài liệu của bạn đang thừa dấu cách ở cuối. Có nghĩa là, thay vì là “ Thanh Minh ” thì tài liệu trong ô tính lại là “ Thanh Minh “, điều này dẫn đến việc Excel không hề tìm kiếm được đúng chuẩn tài liệu như bạn mong ước .
Để khắc phục lỗi này, bạn chỉ cần kiểm tra để chắc như đinh 100 % ô tính của bạn không hề có dấu cách nào ở cuối. Hoặc sử dụng công cụ Text To Columns ở thẻ Data để xóa hàng loạt khoảng cách .
- Không khóa bảng dữ liệu tìm kiếm
Việc khóa bảng dữ liệu tìm kiếm là điều đặc biệt quan trọng khi bạn sử dụng hàm VLOOKUP hay HLOOKUP trong Excel. Bởi Excel có tính năng tự động thay đổi địa chỉ ô khi bạn copy công thức xuống các dòng khác, nên nếu bạn không khóa bảng dữ liệu, khi copy công thức thì Excel sẽ thay đổi giá trị địa chỉ của bảng dò tìm này. Điều này dẫn đến việc bạn không tìm ra được giá trị như mong muốn hoặc giá trị bị sai hoặc gặp lỗi #VALUE!.
Để khóa bảng dữ liệu, trong công thức, sau khi lựa chọn vùng tìm kiếm, bạn chỉ cần nhấn F4 trên bàn phím (hoặc một số laptop là tổ hợp Fn + F4).
Xem thêm :
Đừng quên Like, Share nếu thấy những cách sử dụng hàm HLOOKUP trong Excel này hữu dụng nhé ! Nếu ghé thăm ThuthuatOffice liên tục, bạn cũng sẽ có thời cơ được biết thêm cực kỳ nhiều thủ pháp mê hoặc khác .
Source: http://wp.ftn61.com
Category: Tin Tức
Để lại một bình luận