Cách dùng hàm HLOOKUP VLOOKUP trong Excel

Trong bài viết hôm nay, chúng ta sẽ cùng nhau tìm hiểu về cách dùng và ứng dụng của hàm HLOOKUP VLOOKUP trong Excel, được xem là những hàm sử dụng phổ biến nhất nhé!

HÀM HLOOKUP TRONG EXCEL LÀ GÌ?

Hàm HLOOKUP được dùng để tìm giá trị nhất định tại hàng trên cùng thuộc bảng tính, đồng thời trả lại một giá trị khác trong cùng cột tính từ hàng mà bạn chỉ định. Hàm HLOOKUPdùng được cho Microsoft Excel 2016, Excel 2013, Excel 2010, Excel 2007, kể cả các phiên bản cũ hơn.

CÚ PHÁP VÀ CÁCH DÙNG HÀM HLOOKUP

Cú pháp như sau:

HLOOKUP (lookup_value, table array, row_index_num, [range_lookup])

Trong đó:

Lookup_value (bắt buộc): là giá trị cần tìm. Đó có thể là ô tham chiếu, một giá trị hoặc là chuỗi văn bản.

Row_index_num (bắt buộc): là số hàng trong table_array, nơi mà giá trị sẽ được trả về. Ví dụ như để trả lại giá trị trùng khớp từ hàng thứ 2, bạn sẽ đặt row_index_num bằng 2.

Range_lookup (tuỳ chọn): là một giá trị logic (Boolean) cho biết hàm HLOOKUP cần phải tìm kết quả khớp chính xác hoặc là tương đối.

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

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

Nhằm giúp bạn dễ hiểu hơn, cú pháp HLOOKUP của Excel sẽ được dịch 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])

Tiếp đến, để giúp bạn nắm rõ được cách hoạt động của hàm, hãy cùng chúng tôi xem quan một ví dụ đơn giản. Bạn đang có một bảng tính với thông tin cơ bản về những hành tinh của hệ Mặt Trời. Và bạn đang muốn sử dụng một hàm giúp trả về đường kính của hành tinh này có tên ở ô B5.

Ở công thức HLOOKUP, bạn dùng những tham số sau

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

Table_arrray là vùng 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 bởi vì hàng thứ nhất của bảng không được sắp xếp đúng thứ tự từ A đến Z, nên bạn chỉ có thể tìm giá trị chính xác ở trong ví dụ này

Bây giờ hãy thực hiện xếp những tham số vào và bạn sẽ có công thức như sau:

=HLOOKUP (40, A2:B15, 2)

3 ĐIỀU BẠN CẦN BIẾT VỀ HÀM HLOOKUP TRONG EXCEL 

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

1. Hàm HLOOKUP chỉ có thể được tìm ở hàng trên cùng của table_array. Nếu như bạn cần tìm ở các vị trí khác, hãy dùng công thức Index hoặc Match
2. HLOOKUP không phân biệt được chữ hoa và chữ thường
3. Nếu range_lookup được đặt là TRUE hoặc là bỏ trống (kết quả khớp tương đối), nhũng  giá trị ở hàng đầu của table_array phải được xếp đúng thứ tự tăng dần (A-Z) và từ trái sang phải.

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

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

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

Hình minh họa bên dưới thể hiện sự khác nhau giữa hai công thức Vlookup và Hlookup

ỨNG DỤNG CỦA HLOOKUP TRONG EXCEL

Thực hiện tìm giá trị theo hàng với kết quả khớp tương đối và chính xác

Hàm HLOOKUP có khả năng tìm kết quả khớp chính xác và không chính xác dựa vào giá trị của tham số range_lookup

• TRUE hoặc bỏ trống: là kết quả khớp tương đối
• FALSE: là kết quả khớp chính xác

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

Hãy cùng xem ví dụ minh hoạ bên dưới đây

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

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

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

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

=HLOOKUP (B4, B1:I2, 2)

Bạn cũng lưu ý rằng kết ủa khớp tương đối được yêu cầu sắp xếp những giá trị tại hàng đầu tiên theo thứ tự từ nhỏ nhất đến lớn nhất, hoặc là từ A đến Z, nếu không thì hàm Hlookup sẽ đưa ra kết quả sai.

Tại hình minh họa bên dưới đây, hàm trả về hành tinh Hải Vương, là một trong các 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 như bạn biết được chính xác giá trị cần tìm, bạn sẽ có thể đặt tham số cuối cùng của hàm Hlookup là FALSE:

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

 

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

Tip Để giúp người sử dụng không cảm thấy lo lắng khi nhìn thấy lỗi N/A xuất hiện, bạn hãy đưa hàm Hlookup vào hàm INFERROR rồi 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ả”)

Xem thêm: Cách dùng hàm tính nhân trong Excel

Cách dùng hàm Hlookup từ worksheet hoặc workbook khác

Tóm tại, để tìm giá trị theo hàng từ một trang tính hoặc là một bảng tính khác, tức là bạn phải cung cấp những tham chiếu ngoại tuyến cho hàm HLOOKUP

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

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

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

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

Khi thực hiện tham chiếu từ bảng tính khác, tiến hành đặ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 như bạn muốn lấy giá trị từ một bảng tính đóng, thì bạn cần thực hiệ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 cho việc đánh tên bảng tính và trang tính thủ công bằng tay, bạn vẫn có thể tích chọn các ô trong trang tính khác và Excel sẽ tiến hành thêm tham chiếu ngoại tuyến vào hàm của bạn tự động

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

Cũng tương tự như trường hợp của hàm VLOOKUP, hàm HLOOKUP cho phép dùng những kí tự đại diện sau cho tham số lookup_value:

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

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

Chẳng hạn như, bạn đang có một danh sách tên những khách hàng ở hàng 1 và số order ở hàng 2. Bạn muốn thực hiện tìm số order của một khách hàng nhất định nào đó, tuy nhiên bạn không thể nhớ tên chính xác vị khách mà chỉ có thể nhớ nó bắt đầu với chữ “La”.

Nhận thấy rằng dữ liệu của bạn ở ô B1:I2 (table_array) và thứ tự những số ở hàng 2 (row_index_num), công thức thể hiện như sau:

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

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

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

 

Lưu ý

1. Để hàm HLOOKUP cùng kí tự đại diện cho ra kết quả đúng, range_lookup phải được đặt chế độ FALSE
2. Nếu table_array có chứa nhiều hơn một giá trị khớp với kí tự đại diện, thì giá trị đầu tiên tìm được sẽ được trả về

Ô tham chiếu tuyệt đối và tương đối trong HLookup

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

• table_array luôn được đặt nằm cố định bằng những ô 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 là hỗn hợp dựa trên logic kinh doanh của bạn

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

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

Ở công thức trên, bạn dùng tham chiếu tuyệt đối ($B$1:$I$2) trong table_array bởi vì nó cần được giữ không đổi khi công thức được sao chép từ những ô khác

Đối với lookup_value (B$1), bạn dùng hàm tham chiếu hỗn hợp, cột tương đối và hàng tuyệt đối bởi vì những 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 lại ở các cột khác nhau (từ B đến I), đồng thời cột tham chiếu nên thay đổi dựa vào vị trí tương đối của ô chứa công thức đã được sao chép.

 

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

Như vậy, hàm HLOOKUP có một số hạn chế với nhược điểm lớn nhất đó là không thể thể tìm những giá trị nằm ngoài hàng đầu và phải xếp những giá trị theo thứ tự khi tìm kết quả khớp tương đối. Nhưng may mắn là sự kết hợp giữa hàm INDEX và MATCH sẽ giúp khắc phục được các đ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))

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

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

Tại hình minh họa dưới đây, bạn có thể nhìn thấy rằng trong cả 2 trường hợp INDEX MATCH đều đang hoạt động tốt.

 

Cách sử dụng H-lookup phân biệt dạng chữ trong Excel

Như chúng ta đã có đề cập lúc đầu, hàm HLOOKUP không thể phân biệt được chữ thường và in hoa. Trong một vài trường hợp khi dạng chữ của kí tự quan trọng, thì bạn có thể sử dụng hàm EXACT để so sánh những ô và đặt bên trong hàm INDEX MATCH như ở 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))

Ví dụ giá trị tìm kiếm của bạn ở ô B4, và mảng tìm kiếm là B1:I1, mảng giá trị trả về là B2:I2, thì công thức lúc này 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 vì thế bạn phải tiến hành ấn tổ hợp Ctrl + Shift + Enter sau khi đã viết công thức

10 LÝ DO PHỔ BIẾN KHIẾN HLOOKUP KHÔNG CHẠY ĐƯỢC

1. HLOOKUP không thể thực hiện tìm kiếm các hàng phía trên

Ngay cả khi bạn quên toàn bộ các chi tiết về tìm kiếm theo hàng ngang trong Excel, thì hãy luôn nhớ rằng hàm Hlookup chỉ có thể tiến hành tìm ở hàng đầu tiên trong bảng. Nếu như những giá trị tìm kiếm nằm ở các hàng khác, thì lỗi N/A sẽ được trả lại. Để khắc phục được lỗi này, bạn nên dùng công thức INDEX/MATCH

2. Kết quả khớp tương đối và chính xác

Khi bạn đang tìm giá trị trong Excel, theo hàng ngang hay dọc, đa số các trường hợp là bạn đang tìm kiếm một giá trị cụ thể nên cần kết quả khớp chính xác. Khi tìm kiếm với kết quả khớp tương đối (range_lookup được đặt TRUE hoặc bỏ trống), các bạn nên nhớ xếp các giá trị trong hàng theo thứ tự tăng dần.

3. Bảng tham chiếu thay đổi khi sao chép công thức

Khi sử dụng nhiều hàm HLOOKUP để lấy thông tin về hàng chứa giá trị tìm kiếm, thì bạn phải khoá tham chiếu table_array

4. Thêm vào hoặc xoá bớt hàng mới

Để có thể hiểu tại sao việc thêm một hàng mới có thể làm hỏng đi công thức Hlookup, thì bạn cần hiểu cách hàm HLOOKUP lấy thông tin về giá trị tìm kiếm, đó là dựa trên chỉ số của hàng mà bạn chỉ định

Ví dụ bạn muốn lấy số liệu doanh số dựa trên ID sản phẩm. Các số liệu này nằm ở hàng 4 nên bạn đánh 4 vào tham số row_index_num. Tuy nhiên khi một hàng mới được thêm vào, thì nó sẽ trở thành hàng thứ 5 và hàm Hlookup không thể hoạt động được. Vấn đề này xảy ra tương tự như khi bạn thực hiện xoá bớt một hàng trong bảng.

Cách giải quyết cho vấn đề này đó là khoá bảng để tránh việc người dùng thêm một hàng mới vào hoặc là dùng công thức INDEX/MATCH thay cho hàm Hlookup. Trong Index/Match, thì bạn cần chỉ rõ những hàng để tìm giá trị và trả giá trị về như mảng tham chiếu và Excel thì có thể tự điều chỉnh những tham chiếu này. Nhờ vậy, bạn cũng không cần phải lo lắng về vấn đề thêm hay là xoá bớt hàng như khi dùng công thức Hlookup.

5. Trùng lặp trong bảng

Hàm HLOOKUP chỉ có thể trả về một giá trị là giá trị đầu tiên nằm trong bảng khớp với giá trị tìm kiếm.

Nếu như có một vài giá trị giống nhau trong bảng, bạn hãy tiến hành chọn một trong những giải pháp mà phù hợp với bạn nhất:

• Tiến hành loại bỏ trùng lặp bằng việc dùng công cụ của Excel
• Nếu như bạn muốn trùng lặp được giữ nguyên trong dữ liệu, hãy thực hiện tạo PivotTable để nhóm và lọc các dữ liệu theo yêu cầu của mình
• Dùng công thức mảng để rút những giá trị trùng khỏi mảng tìm kiếm

6. Khoảng trắng

Khi công thức hàm Hlookup hoàn toàn đúng nhưng lỗi #N/A vẫn trả về, bạn hãy kiểm tra lại bảng và giá trị tìm kiếm xem là có kí tự trắng nào ở đấy không. Bạn sử dụng hàm TRIM để có thể nhanh chóng loại bỏ các khoảng trống.

7. Số được cài đặt ở dạng văn bản

Chuỗi văn bản cũng giống như số là một khó khăn khác khi dùng công thức Excel.

8. Giá trị tìm kiếm vượt quá 255 kí tự

Tất cả những hàm tìm kiếm trong Excel sẽ chỉ hoạt động khi giá trị tìm kiếm nằm dưới 255 kí tự. Giá trị tìm kiếm dài hơn thì sẽ trả lại kết quả là lỗi #VALUE!. Bởi vì hàm INDEX/MATCH không giới hạn số lượng kí tự nên bạn sẽ khắc phục lỗi này khi dùng hàm INDEX/MATCH

9. Đường dẫn đầy đủ tới bảng tính không được nêu rõ

Nếu như bạn thực hiện hàm Hlookup từ một bảng tính khác, thì bạn nên nhớ rằng cần phải cung cấp đầy đủ đường dẫn tới nó.

10. Sai tham số

Hàm Hlookup là một hàm phức tạp nên bạn cần nhớ rằng phải thật cẩn thận khi dùng. Dưới đây là một số  lỗi phổ biến thường gặp khi nhập sai tham số

• Nếu row_index_num nhỏ hơn 1, thì lỗi #VALUE! sẽ được trả lại
• Nếu row_index_num lớn hơn số hàng nằm trong bảng tìm kiếm, thì lỗi #REF! sẽ được trả lại
• Nếu như bạn tìm kiếm với kết quả khớp tương đối và lookup_value nhỏ hơn giá trị nhỏ nhất nằm trong hàng đầu tiên của table_array, thì lỗi #N/A sẽ được trả lại

Xem thêm: Hướng dẫn cách sử dụng hàm MATCH trong Excel