Như bạn đã biết Microsoft Excel có 3 hàm dùng để tìm giá trị VLOOKUP, LOOKUP và hàm HLOOKUP. Người dùng cảm thấy bối rối khi sử dụng 3 hàm này nhất. Trong bài viết này, Admin Edu sẽ tập trung vào các đặc điểm của hàm HLOOKUP trong Excel và đưa một vài ví dụ minh hoạ giúp bạn sử dụng hàm Hlookup một cách hiệu quả nhất.

1. HÀM HLOOKUP TRONG EXCEL LÀ GÌ?

Hàm HLOOKUP được sử dụng để tìm một giá trị nhất định trong hàng trên cùng của bảng và trả lại một giá trị khác trong cùng cột từ hàng mà bạn chỉ định.

Hàm HLOOKUP sử dụng được cho Microsoft Excel 2016, Excel 2013, Excel 2010, Excel 2007 và phiên bản cũ hơn.

CÚ PHÁP VÀ CÁCH SỬ DỤNG HLOOKUP

Cú pháp hàm Hlookup như sau

Nếu TRUE hoặc bỏ qua, kết quả khớp tương đối được trả về. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Hlookup của bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value.

Nếu FALSE, chỉ kết quả khớp chính xác được trả về. Nếu không giá trị nào trong hàng chỉ định  khớp chính xác với giá trị tìm kiếm, hàm Hlookup sẽ trả về lỗi #N/A.

Để dễ hiểu hơn, bạn có thể dịch cú pháp HLOOKUP của Excel sang tiếng Việt như sau

HLOOKUP (tìm kiếm giá trị này, trong bảng này, trả lại giá trị từ hàng này, [trả lại giá trị chính xác hoặc tương đối])

Để hiểu cách hoạt động, chúng ta hãy cùng xem một ví dụ đơn giản. Giả sử bạn có một bảng với thông tin cơ bản về các hành tinh của hệ Mặt trời. Bạn muốn hàm trả về đường kính của hành tinh có tên trong ô B5.

Trong công thức Hlookup, chúng ta sẽ sử dụng các tham số sau

Lookup_value là B5, ô chứa tên của hành tinh bạn muốn tìm

Table_arrray là B2:I3, bảng chứa giá trị cần tìm

Row_index_num là 2 vì Đường kính ở hàng thứ 2 trong bảng

Range_lookup  là FALSE vì hàng thứ nhất của bảng không được sắp xếp theo thứ tự từ A đến Z, nên chúng ta chỉ có thể tìm giá trị chính xác ở trong ví dụ này

Bây giờ xếp các tham số vào và chúng ta có công thức như sau:

=HLOOKUP (40, A2:B15, 2)

2. NHỮNG ĐIỀU BẠN CẦN BIẾT VỀ HÀM HLOOKUP TRONG EXCEL

Bất cứ khi nào bạn tìm giá trị trong hàng, hãy nhớ những điều sau:

1. 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
2. HLOOKUP trong Excel không phân biệt được chữ hoa và thường
3. Nếu range_lookup được đặt là TRUE hoặc bỏ 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 theo thứ tự tăng dần (A-Z) từ trái sang phải.

Hãy tham khảo video dưới đây để hiểu thêm về hàm HLOOKUP nhé:

3. SỰ KHÁC NHAU GIỮA VLOOKUP VÀ HLOOKUP

Như bạn đã biết, cả hai hàm HLOOKUP VÀ VLOOKUP dùng để tìm kiếm một giá trị. Nhưng chúng khác nhau trong cách hoạt động. Chúng ta dễ dàng nhận ra tên của hai hàm khác nhau ở chữ cái đầu – “H” là ngang (horizontal) và “V” là dọc (vertical)

Do đó, bạn sử dụng hàm VLOOKUP để tìm giá trị theo cột ở phía bên trái của dữ liệu bạn muốn tìm. Hàm HLOOKUP dùng để tìm giá trị theo hàng ngang. Nó tìm giá trị ở hàng đầu tiên của bảng và trả lại giá trị ở hàng được chỉ định ở trong cùng một cột.

Hình dưới đây thể hiện sự khác nhau giữa hai công thức Vlookup và Hlookup

4. KHI NÀO SỬ DỤNG HÀM VLOOKUP, KHI NÀO SỬ DỤNG HLOOKUP

Khi nào sử dụng VLOOKUP, khi nào sử dụng HLOOKUP? Hẳn bạn rất thắc mắc với câu hỏi này. Chúng ta có 1 bí quyết để dễ dàng nhận diện nó, đó là Căn cứ theo đối tượng Lookup_value và cấu trúc của vùng bảng Table_Array (Bảng chứa dữ liệu tìm kiếm và kết quả cần tìm)

• Nếu lookup_value nằm trên cột đầu tiên của bảng thì dùng Vlookup

• Nếu lookup_value nằm trên dòng đầu tiên của bảng thì dùng Hlookup

5. ỨNG DỤNG CỦA HLOOKUP TRONG EXCEL

5.1. ỨNG DỤNG CỦA HLOOKUP TRONG EXCEL

Hàm HLOOKUP trong Excel có thể tìm kết quả khớ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: kết quả khớp tương đối
• FALSE: kết quả khớp chính xác

Nên nhớ rằng mặc dù chúng ta thường nói là kết quả khớp tương đối, công thức Hlookup luôn tìm một kết quả khớp chính xác đầu tiên. Đặt tham số là FALSE cho phép hàm trả về kết quả khớp tương đối (giá trị gần nhất nhở hơn giá trị cần tìm kiếm) nếu kết quả khớp chính xác không tìm thấy; TRUE hoặc bị bỏ trống sẽ trả về lỗi #N/A

Chúng ta cùng xem ví dụ minh hoạ dưới đây

• HLOOKUP với kết quả khớp tương đối

Giả sử bạn có một danh sách các hành tinh ở hàng 2 (B2:I2) và nhiệt độ của chúng ở hàng 1 (B1:I1). Bạn muốn tìm hành tinh có nhiệt độ bằng nhiệt độ ở ô B4.

Trong trường hợp bạn không biết chính xác nhiệt độ cần tìm, bạn có thể xây dựng hàm Hlookup để đưa ra kết quả khớp gần nhất nếu giá trị chính xác không được tìm thấy.

Ví dụ, để biết hành tinh nào có nhiệt độ trung bình vào khoảng -340°F, bạn sử dụng công thức Hlookup như sau (range_lookup được đặt là TRUE trong trường hợp này):

=HLOOKUP (B4, B1:I2, 2)

Hãy nhớ rằng kết quả khớp tương đối yêu cầu sắp xếp các giá trị ở hàng đầu tiên từ nhỏ nhất đến lớn nhất hoặc từ A đến Z, nếu không hàm Hlookup sẽ đưa ra kết quả sai.

Trong hình chụp dưới đây, hàm trả về hành tinh Hải Vương, một trong những hành tinh lạnh nhất trong hệ Mặt trời luôn giữ nhiệt độ trung bình vào khoảng -346 độ F.

• Hlookup với kết quả khớp chính xác

Nếu bạn biết chính xác giá trị cần tìm, bạn có thể đặt tham số cuối cùng của hàm Hlookup là FALSE:

=HLOOKUP (B4, B1:I2, 2, FALSE)

Một mặt, kết quả khớp tường dối dễ dàng sử dụng hơn vì nó không yêu cầu sắp xếp dữ liệu ở hàng đầu tiên. Mặt khác, nếu kết quả khớp chính xác không được tìm thấy, lỗi #N/A sẽ được trả về.

Tip: Để giúp người dùng không cảm thấy hoảng sợ khi nhìn thấy lỗi N/A xuất hiện, bạn có thể đưa hàm Hlookup vào hàm INFERROR và gửi thông điệp của bạ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

Nhìn chung, tìm giá trị theo hàng từ một trang tính hoặc một bảng tính khác nghĩa là bạn phải cung cấp các tham chiếu ngoại tuyến cho hàm HLOOKUP

Để lấy giá trị khớp từ một trang tính khác, bạn phải chỉ rõ tên của trang tính đặt trước dấu “!”. Ví dụ:

=HLOOKUP (B$1, Duongkinh! $B$1:$I2, 2, FALSE)

Nếu tên trang tính chứa dấu cách hoặc kí tự không phải chữ cái, đặt nó trong dấu ngoặc đơn như sau:

=HLOOKUP (B$1, ‘Đường kính’!$B$1:$I$2, 2, FALSE)

Khi tham chiếu từ bảng tính khác, đặt tên bảng tính trong ngoặc vuông:

=HLOOKUP (B$1, [BOOK1.xlsx]Đườngkính!$B$1:$I$2, 2, FALSE)

Nếu bạn muốn lấy giá trị từ một bảng tính đóng, bạn cần chỉ ra đường dẫn đến bảng tính:

=HLOOKUP (B$1, ‘D:\Reports\[Book1.xlsx]Đườngkính’!$B$1:$I$2, 2, FALSE)

Tip: Thay vì đánh tên bảng tính và trang tính bằng tay, bạn có thể chọn các ô trong trang tính khác và Excel sẽ thêm tham chiếu ngoại tuyến vào hàm của bạn một cách tự động

HLOOKUP với kết quả khớp một phần (sử dụng kí tự đại diện)

Như trong trường hợp của Vlookup, hàm HLOOKUP cho phép sử dụng các kí tự đại diện sau cho tham số lookup_value:

• Dấu hỏi (?) để khớp với bất kì một kí tự đơn nào
• Dấu sao (*) để khớp với một chuỗi các kí tự

Các kí tự đại diện rất hữu ích khi bạn muốn lấy thông tin từ một cơ sở dữ liệu dựa trên một vài đoạn văn bản nằm trong nội dung của ô tìm kiếm

Ví dụ, bạn có một danh sách tên các khách hàng ở hàng 1 và số order ở hàng 2. Bạn muốn tìm số order của một khách hàng nhất định nào đó nhưng bạn không thể nhớ tên chính xác vị khách mà chỉ nhớ nó bắt đầu với chữ “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)

Để công thức linh hoạt hơn, bạn có thể đánh giá trị tìm kiếm vào một ô cụ thể, ví dụ B4, và gắn vào ô đó kí tự đại diện như sau:

=HLOOKUP (B4&“*”, B1:I2, 2, FALSE)

7. Ô THAM CHIẾU TUYỆT ĐỐI VÀ TƯƠNG ĐỐI TRONG HLOOKUP

Nếu bạn đang viết hàm Hlookup cho một ô, bạn có thể không cần quan tâm nên sử dụng ô tham chiếu tuyệt đối hay tương đối vì cả hai đều phù hợp. Tuy nhiên, khi sao chép một công thức cho nhiều ô tham chiếu, bạn cần phân biệt được hai loại này

• table_array luôn được đặt cố định bằng các ô tham chiếu tuyệt đối với kí hiệu “$”, ví dụ $B$1:$I$2
• tham chiếu lookup_value là tương đối hoặc hỗn hợp dựa trên logic kinh doanh của bạn

Để dễ hiểu hơn, chúng ta cùng xem kĩ hàm Hlookup khi lấy dữ liệu từ một trang tính khác

=HLOOKUP (B$1, Duongkinh!$B$1:$I$2, 2, FALSE)

Trong công thức trên, chúng ta sử dụng tham chiếu tuyệt đối ($B$1:$I$2) trong table_array vì nó cần được giữ không đổi khi công thức được sao chép từ các ô khác

Đối với lookup_value (B$1), chúng ta sử dụng tham chiếu hỗn hợp, cột tương đối và hàng tuyệt đối vì các giá trị tìm kiếm (tên hành tinh) nằm trên cùng một hàng (hàng 1) nhưng ở các cột khác nhau (từ B đến I) và cột tham chiếu nên thay đổi dựa trên vị trí tương đối của ô chứa công thức được sao chép.

8. INDEX/MATCH, MỘT CÔNG THỨC HIỆU QUẢ KHÁC BÊN CẠNH HLOOKUP

Như các bạn đã biết, hàm HLOOKUP có một vài hạn chế với nhược điểm lớn nhất là không thể thể tìm các giá trị nằm ngoài hàng đầu và phải xếp các giá trị theo thứ tự khi tìm kết quả khớp tương đối. May mắn thay, sự kết hợp giữa hàm INDEX và MATCH sẽ khắc phục được những điểm này:

INDEX (nơi chứa giá trị về, MATCH (giá trị tìm kiếm, nơi tìm giá trị, 0))

Giả sử giá trị tìm kiếm ở ô B7, bạn đang tìm một kết quả khớp ở hàng 2 (B2:I2), và muốn giá trị trả về từ hàng 1 (B1:I1), công thức như sau

=INDEX (B1:I1, MATCH (B7, B2:I2, 0))

Trong hình chụp dưới đây, bạn có thể thấy trong cả 2 trường hợp INDEX MATCH đều hoạt động tốt.

9. CÁCH SỬ DỤNG HLOOKUP PHÂN BIỆT DẠNG CHỮ TRONG EXCEL

Như đã đề cập lúc đầu, hàm HLOOKUP không thể phân biệt chữ thường và in hoa. Trong nhiều trường hợp khi dạng chữ của kí tự quan trọng, bạn có thể dùng hàm EXACT để so sánh các ô và đặt bên trong hàm INDEX MATCH như trong ví dụ trước:

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))

Giả sử giá trị tìm kiếm của bạn ở ô B4, mảng tìm kiếm là B1:I1, mảng giá trị trả về là B2:I2, công thức có dạng như sau:

=INDEX (B2:I2, MATCH (TRUE, EXACT (B1:I1, B4), 0))

Lưu ý: Đây là công thức mảng do đó bạn phải ấn tổ hợp Ctrl + Shift + Enter sau khi viết công thức

Trên đây là bài viết chi tiết về ứng dụng của hàm HLOOKUP và so sánh với hàm INDEX kết hợp với hàm MATCH trong Excel. Hi vọng qua bài viết này có thể giúp cho bạn hiểu thêm về các hàm dò tìm này trong Excel. Cảm ơn bạn đã quan tâm!