HLOOKUP trong Excel – Cách dùng và ví dụ minh hoạ chi tiết

IIE » Microsoft Office » HLOOKUP trong Excel – Cách dùng và ví dụ minh hoạ chi tiết

HLOOKUP trong Excel là gì, sử dụng khi nào, cú pháp, cách dùng, ví dụ minh hoạ, hàm HLOOKUP và VLOOKUP, lý do khiến HLOOKUP không chạy được.

Content

HLOOKUP trong Excel là gì?

HLOOKUP trong Excel chính là hàm giúp dò tìm một giá trị mà bạn mong muốn từ một bảng tính, sau đó tự động điền giá trị này vào một bảng tính hoặc một ô tính khác. Nhờ đó mà công việc của bạn có thể hoàn thành một cách nhanh và chính xác hơn, thay vì bạn phải tự dò tìm rồi copy từng giá trị một.

HLOOKUP trong Excel chính là viết tắt của hai từ tiếng anh: 

H – Horizontal – hàng ngang

LOOKUP – Look Up – Dò tìm

Cách ghép này sẽ giúp bạn dễ dàng gợi nhớ công dụng của HLOOKUP trong Excel là dò tìm dữ liệu theo dạng hàng ngang và trả về giá trị theo dạng hàng dọc (ngược lại với hàm VLOOKUP).

Sử dụng hàm LOOKUP trong Excel khi nào?

Khi có nhu cầu dò tìm dữ liệu theo dạng hàng ngang (từ trái qua phải) và trả về giá trị theo dạng hàng dọc (từ trên xuống dưới), hàm HLOOKUP trong Excel chính là sự lựa chọn phù hợp nhất.

Đối với việc ứng dụng HLOOKUP trong Excel vào công việc, dễ thấy nhất là hàm này giúp bạn dễ dàng tìm được tên sản phẩm, số lượng hoặc đơn giá,… dựa trên các thông tin có sẵn như mã sản phẩm; hoặc tìm tên nhân viên và xếp loại công việc của họ dựa trên thông tin có sẵn là các tiêu chí đánh giá cố định.

Cú pháp hàm HLOOKUP trong Excel

Hàm HLOOKUP trong Excel có cú pháp như sau:

HLOOKUP(Lookup_value,Table_array,Row_index_ num,Range_lookup)

Trong đó,

Nếu bạn nhập vào Range_lookup là TRUE: Excel sẽ trả về kết quả dò tìm tương đối.

Nếu bạn nhập vào Range_lookup là FALSE: Excel sẽ trả về kết quả dò tìm chính xác. Và nếu không tìm thấy giá trị chính xác đúng như yêu cầu, Excel sẽ trả về kết quả là lỗi #N/A.

Cách sử dụng hàm HLOOKUP trong Excel

Cách kết hợp hàm HLOOKUP và hàm LEFT

Cú pháp của hàm LEFT trong Excel là:

= LEFT(text;n)

Công dụng của hàm LEFT chính là lấy ra ký tự thứ n tính từ bên trái của text. Như vậy, nếu biết cách kết hợp hàm LEFT và HLOOKUP trong Excel, việc tìm kiếm dữ liệu của bạn chắc chắn sẽ trở lên đơn giản và nhanh chóng hơn rất nhiều.

Xét ví dụ bạn đang cần điền vào cột tên ngành căn cứ vào mã lớp cho trước. Mã lớp bao gồm các thành phần chữ và số, ngành chỉ bao gồm chữ. 

Để thực hiện yêu cầu này, bạn chỉ cần kết hợp hàm LEFT và HLOOKUP trong Excel với công thức như sau:

=HLOOKUP(LEFT(C4;4);$A$12:$F$13;2;FALSE)

Trong đó:

Sau khi nhấn phím Enter, chúng ta nhận được kết quả như sau:

Copy công thức này xuống các ô còn lại của cột, chúng ta được kết quả như sau:

Cách kết hợp hàm HLOOKUP và hàm IF

Khi kết hợp hai hàm HLOOKUP và IF, chúng ta có thể dễ dàng thực hiện yêu cầu xếp loại và điền thông tin căn cứ vào những điều kiện đã đưa ra.

Xét ví dụ bạn cần xếp loại học sinh là Đạt hay Không đạt trong kỳ thi vừa rồi căn cứ vào dữ liệu từ hai bảng sau:

Biết rằng học sinh nào thi cấp Trường và tổng điểm đạt từ 500 trở lên sẽ được xếp loại Đạt. Ngược lại, học sinh sẽ được xếp loại Không đạt. Tiêu chí này được áp dụng cho cả các kỳ thi cấp Thành phố và Tỉnh.

Để thực hiện yêu cầu này, bạn chỉ cần kết hợp hàm IF và HLOOKUP trong Excel với công thức như sau:

=IF(E4>=HLOOKUP(D4;$C$12:$F$13;2;FALSE);”Đạt”;”Không đạt”)

Trong đó,

Sau khi nhấn phím Enter, chúng ta nhận được kết quả như sau:

Copy công thức này xuống các ô còn lại của cột, chúng ta được kết quả như sau:

Lưu ý khi áp dụng hàm HLOOKUP tìm kiếm giá trị

Để đảm bảo dữ liệu tìm kiếm bạn nhận được đáp ứng đúng yêu cầu đề ra mà không có bất kỳ sai sót nào, khi ứng dụng hàm HLOOKUP trong Excel vào công việc, bạn cần lưu ý một số điều dưới đây:

Ví dụ minh hoạ cách dùng hàm HLOOKUP trong Excel

Trích bảng lương cho nhân viên

Xét ví dụ có một bảng dữ liệu tổng hợp như trong hình dưới đây. Yêu cầu đặt ra là sử dụng hàm HLOOKUP trong Excel để trích ra dữ liệu của 3 nhân viên trong bảng Danh sách trả lương.

Để thực hiện yêu cầu này, thay vì thực hiện thủ công là copy từng dòng một, chúng ta có thể sử dụng hàm HLOOKUP trong Excel với công thức như sau:

= HLOOKUP(B13;$A$1:$I$7;6;FALSE)

Trong đó:

Như vậy là chúng ta đã điền được cột Ngày công. Đối với cột Lương, bạn vẫn sử dụng hàm HLOOKUP trong Excel với công thức như trên, nhưng cần thay đổi số 6 thành số 7 vì lý do dòng cần điền dữ liệu là dòng Lương, tính từ dòng họ tên (dòng số 1) xuống sẽ là dòng thứ 7. Giờ thì chỉ cần copy công thức cho các dòng bên dưới là chúng ta có được kết quả như sau:

Xếp loại học sinh

Xét ví dụ bạn cần sử dụng hàm HLOOKUP trong Excel để xếp loại học sinh dựa trên số điểm trung bình của các em đã được chuẩn bị sẵn ở bảng số liệu dưới đây.

Để thực hiện yêu cầu này, chúng ta sẽ sử dụng hàm HLOOKUP trong Excel với công thức như sau:

= HLOOKUP(D4;$B$11:$F$12;2;TRUE)

Trong đó:

Giờ thì chỉ cần copy công thức cho các dòng bên dưới là chúng ta có được kết quả như sau:

Hàm VLOOKUP và hàm HLOOKUP trong Excel

Mục đích khi sử dụng hàm VLOOKUP và HLOOKUP trong Excel đều là để tìm kiếm giá trị. Tuy vậy kể cả những người dùng lâu năm nhiều khi vẫn bị nhầm lẫn giữa hai hàm này. Dưới đây là bảng so sánh chi tiết để bạn dễ phân biệt chúng.

Định nghĩa

Lookup_value (phần tử đầu tiên trong công thức và cũng chính là giá trị tìm kiếm)

Row/Column_index_ num (Vị trí của giá trị cần điền so với giá trị bạn muốn tìm)

Việc nắm vững và phân biệt rõ ràng sự khác biệt giữa hai hàm này chắc chắn sẽ giúp bạn thực hiện công việc của mình một cách trơn tru và hiệu quả hơn rất nhiều.

Các lý do chính khiến hàm HLOOKUP không chạy được

Thỉnh thoảng khi dùng hàm HLOOKUP trong Excel, bạn sẽ nhận thấy hàm không chạy được. Dưới đây là 3 nguyên nhân chính gây ra tình trạng này:

Để sai định dạng

Đây là một lỗi nhỏ nhưng lại đánh lừa được rất nhiều người dùng. Để sai định dạng tức là bạn để ô tính muốn trỏ địa chỉ tới trong công thức chứa dữ liệu ở dạng số, nhưng dòng đầu tiên bạn lại để dữ liệu ở định dạng chữ (hoặc có thể ngược lại). Để nhanh chóng khắc phục điều này, hãy sử dụng kèm hàm TEXT trong công thức của hàm HLOOKUP trong Excel, hoặc đơn giản là điều chỉnh lại định dạng của các ô cho trùng nhau là được.

Thừa dấu cách ở cuối cùng trong điều kiện tìm kiếm

Nếu bạn đã kiểm tra định dạng các ô nhưng không phát hiện ra sai lệch, hãy xem thử điều kiện tìm kiếm bạn nhập vào trong công thức có thừa ra một dấu cách ở cuối hay không. Ví dụ thay vì chỉ nhập vào “Hoàng hôn”, bạn lại nhập thành “Hoàng hôn “, dẫn đến việc Excel không tìm kiếm được dữ liệu chính xác mà bạn cần.

Để nhanh chóng khắc phục điều này, bạn đơn giản chỉ cần xóa dấu cách ở cuối các điều kiện tìm kiếm, hoặc sử dụng công cụ Text To Columns trong tab Data trên thanh công cụ để loại bỏ toàn bộ các dấu cách này.

Không khóa bảng dữ liệu tìm kiếm

Như chúng mình đã nhấn mạnh ở trên, việc khóa bảng dữ liệu tìm kiếm là thao tác rất quan trọng khi sử dụng hàm HLOOKUP trong Excel. Lý do là bởi Excel có tính năng tự động thay đổi địa chỉ ô mỗi khi bạn thực hiện copy công thức xuống các dòng bên dưới. Điều này kéo theo nếu bạn không khóa bảng dữ liệu, khi copy công thức, Excel sẽ lập tức thay đổi giá trị của địa chỉ bảng dò tìm, khiến bạn không thể tìm được giá trị mình cần, tìm ra giá trị sai, hoặc nhận được thông báo lỗi #VALUE!.

Để khóa bảng dữ liệu, sau khi lựa chọn vùng dữ liệu tìm kiếm, hãy nhấn phím F4 (hoặc tổ hợp phím Fn + F4) là được.

Vậy là trong bài viết trên, chúng ta đã cùng tìm hiểu toàn bộ từ A-Z về khái niệm và cách sử dụng hàm HLOOKUP trong Excel. Bạn nên dành nhiều thời gian để thực hành cho thật nhuần nhuyễn việc sử dụng hàm này vì nó thực sự rất hữu ích cho công việc. Cuối cùng, nếu thích bài viết, đừng quên bấm theo dõi trang ngay để liên tục cập nhật thêm nhiều bài viết thuộc cùng chủ đề thủ thuật MS Office bạn nhé!

Microsoft Office -