Hàm Hlookup trong Excel; dùng Hlookup, Vlookup hay Xlookup

Như bạn đã biết Microsoft có 3 hàm dùng để tìm giá trị, LOOKUP,  VÀ HLOOKUP, và 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, Học Excel Online 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 Hlookup một cách hiệu quả nhất.

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 năm nay, 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

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

Với

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

Table_array (bắt buộc): 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 đặt tên hoặc bảng Excel. 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): số hàng trong table_array nơi mà giá trị được trả về. Ví dụ, để trả lại giá trị trùng khớp từ hàng thứ 2, đặt row_index_num bằng 2.

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

Nếu TRUE hoặc bỏ lỡ, tác dụng khớp tương đối được trả về. Nghĩa là nếu tác dụng khớp đúng mực không được tìm thấy, hàm Hlookup của bạn sẽ trả về giá trị lớn nhất sau đó nhỏ hơn look_up value .
Nếu FALSE, chỉ tác dụng khớp đúng chuẩn được trả về. Nếu không giá trị nào trong hàng chỉ định khớp đúng mự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 hoàn toà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 giải trí, tất cả chúng ta hãy cùng xem một ví dụ đơn thuần. Giả sử bạn có một bảng với thông tin cơ bản về những 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, tất cả chúng ta sẽ sử dụng những 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 những tham số vào và tất cả chúng ta 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ứ 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ỉ hoàn toàn 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 ( tác dụng khớptương đối), những giá trị ở số 1 của table_array phải được xếp theo thứ tự tăng dần ( A-Z ) từ trái sang phải .

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 giải trí. Chúng ta thuận tiện nhận ra tên của hai hàm khác nhau ở vần âm đầ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 tài 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 tiên phong 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 bộc lộ sự khác nhau giữa hai công thức Vlookup và Hlookup

           

Xem thêm : Các hàm excel thông dụng

Khi nào sử dụng 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

ỨNG DỤNG CỦA HLOOKUP TRONG EXCEL

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 trong Excel hoàn toàn có thể tìm hiệu quả khớp đúng chuẩn và không đúng chuẩn dựa trên giá trị của tham số range_lookup

• TRUE hoặc bỏ trống : tác dụng khớp tương đối
• FALSE : tác dụng khớp đúng chuẩn

Nên nhớ rằng mặc dầu tất cả chúng ta thường nói là tác dụng khớp tương đối, công thức Hlookup luôn tìm một tác dụng khớp đúng chuẩn tiên phong. Đặt tham số là FALSE được cho phép hàm trả về hiệu 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 tác dụng khớp đúng mự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 tác dụng khớp tương đối

Giả sử bạn có một list những 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 đúng mực nhiệt độ cần tìm, bạn hoàn toàn có thể kiến thiết xây dựng hàm Hlookup để đưa ra tác dụng khớp gần nhất nếu giá trị đúng chuẩn không được tìm thấy .
Ví dụ, để biết hành tinh nào có nhiệt độ trung bình vào lúc – 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 hiệu quả khớp tương đối nhu yếu sắp xếp những giá trị ở hàng tiên phong 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 hiệu 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 tầm – 346 độ F .

• Hlookup với hiệu quả khớp đúng chuẩn

Nếu bạn biết đúng mực giá trị cần tìm, bạn hoàn toàn có thể đặt tham số sau cuối của hàm Hlookup là FALSE :

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

Một mặt, hiệu quả khớp tường dối thuận tiện sử dụng hơn vì nó không nhu yếu sắp xếp tài liệu ở hàng tiên phong. Mặt khác, nếu tác dụng khớp đúng mự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ả”)

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 ứng những 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 vần âm, đặ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 được cho phép sử dụng những kí tự đại diện thay mặt 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 những kí tự

Các kí tự đại diện thay mặt rất có ích khi bạn muốn lấy thông tin từ một cơ sở tài 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 list tên những người mua ở hàng 1 và số order ở hàng 2. Bạn muốn tìm số order của một người mua nhất định nào đó nhưng bạn không hề nhớ tên đúng chuẩn vị khách mà chỉ nhớ nó mở màn với chữ “ La ” .
Cho rằng tài 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 như sau :

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

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

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

Lưu ý

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

Ô 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 hoàn toàn có thể không cần chăm sóc nên sử dụng ô tham chiếu tuyệt đối hay tương đối vì cả hai đều tương thích. 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 và thắt chặt bằng những ô tham chiếu tuyệt đối với kí hiệu “ USD ”, ví dụ USD B USD 1 : USD I USD 2
• tham chiếu lookup_value là tương đối hoặc hỗn hợp dựa trên logic kinh doanh thương mại của bạn

Để dễ hiểu hơn, tất cả chúng ta cùng xem kĩ hàm Hlookup khi lấy tài 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, tất cả chúng ta sử dụng tham chiếu tuyệt đối ( USD B USD 1 : USD I USD 2 ) trong table_array 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 USD 1 ), tất cả 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ì 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 ở những cột khác nhau ( từ B đến I ) và cột tham chiếu nên đổi khác dựa trên 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ư những bạn đã biết, hàm HLOOKUP có một vài hạn chế với điểm yếu kém lớn nhất là không hề thể tìm những giá trị nằm ngoài số 1 và phải xếp những giá trị theo thứ tự khi tìm hiệu quả khớp tương đối. May mắn thay, sự tích 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 hiệu 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 hoàn toàn có thể thấy trong cả 2 trường hợp INDEX MATCH đều hoạt động giải trí tốt .

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

Như đã đề cập lúc đầu, hàm HLOOKUP không hề 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 hoàn toàn có thể dùng hàm EXACT để so sánh những ô 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

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

Hàm Hlookup là một hàm phức tạp bởi nó có rất nhiều thành phần trong công thức khiến những lỗi # N / A, # VALUE hoặc # REF tiếp tục xảy ra. Nếu hàm HLOOKUP của bạn không hoạt động giải trí được, nguyên do hoàn toàn có thể là một trong những lỗi dưới đây

1. HLOOKUP không hề tìm kiếm những hàng phía trên

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

2. Kết quả khớp tương đối và đúng mực

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

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

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

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

Để hiểu tại sao thêm một hàng mới hoàn toàn có thể làm hỏng công thức Hlookup, bạn cần hiểu cách HLOOKUP lấy thông tin về giá trị tìm kiếm đó là dựa trên chỉ số của hàng bạn chỉ định
Giả sử bạn muốn lấy số liệu doanh thu dựa trên ID loại sản phẩm. Những số liệu này ở hàng 4 nên bạn đánh 4 vào tham số row_index_num. Nhưng khi một hàng mới được thêm vào, nó trở thành hàng thứ 5 và Hlookup không hề hoạt động giải trí được. Vấn đề xảy ra tương tự như như khi bạn xoá bớt một hàng trong bảng .
Cách xử lý là khoá bảng để tránh việc người sử dụng thêm một hàng mới vào hoặc sử dụng công thức INDEX / MATCH thay cho Hlookup. Trong Index / Match, bạn phải chỉ rõ những hàng để tìm giá trị và trả giá trị về như mảng tham chiếu và Excel hoàn toàn có thể tự kiểm soát và điều chỉnh những tham chiếu này. Nhờ vậy, bạn không cần phải lo ngại về yếu tố thêm hay xoá bớt hàng như khi sử dụng công thức Hlookup .

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

Hàm HLOOKUP trong Excel chỉ hoàn toàn có thể trả về một giá trị là giá trị tiên phong trong bảng khớp với giá trị tìm kiếm .
Nếu có một vài giá trị giống nhau trong bảng, chọn một trong những giải pháp mà tương thích với bạn nhất :

• Loại bỏ trùng lặp bằng cách dùng công cụ của Excel
• Nếu bạn muốn trùng lặp được giữ nguyên trong tài liệu, tạo PivotTable để nhóm và lọc những tài liệu theo nhu yếu của mình
• Sử 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 Hlookup của bạn trọn vẹn đúng nhưng lỗi # N / A vẫn bị trả về, hãy kiểm tra lại bảng và giá trị tìm kiếm xem có kí tự trắng nào không. Bạn hoàn toàn có thể nhanh gọn vô hiệu những khoảng chừng trống bằng cách sử dụng hàm TRIM trong Excel .

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

Chuỗi văn bản giống như số là một khó khăn vất vả khác khi sử dụng công thức Excel. Mô tả cụ thể của yếu tố này sẽ được lý giải trong Tại sao những công thức Excel ngừng hoạt động giải trí

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 chỉ hoạt động giải trí khi giá trị tìm kiếm dưới 255 kí tự. Giá trị tìm kiếm dài hơn sẽ trả lại tác dụng là lỗi # VALUE !. Vì hàm INDEX / MATCH không số lượng giới hạn số lượng kí tự nên bạn hoàn toàn có thể khắc phục lỗi này khi sử dụng INDEX / MATCH

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

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

10. Sai tham số

Các bạn nên nhớ rằng hàm Hlookup là một hàm phức tạp nên thật cẩn trọng khi sử dụng. Dưới đây là một vài lỗi phổ cập khi nhập sai tham số

• Nếu row_index_num nhỏ hơn 1, lỗi # VALUE ! sẽ được trả lại

• Nếu row_index_num lớn hơn số hàng trong bảng tìm kiếm, lỗi #REF! sẽ được trả lại

  Xem thêm: Trị Viêm Lợi Tại Nhà Hiệu Quả

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

Để hoàn toàn có thể ứng dụng tốt Excel vào trong việc làm, tất cả chúng ta không chỉ nắm vững được những hàm mà còn phải sử dụng tốt cả những công cụ của Excel. Những hàm nâng cao giúp vận dụng tốt vào việc làm như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH … Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table …
Toàn bộ những kiến thức và kỹ năng này những bạn đều hoàn toàn có thể học được trong khóa học

Source: http://wp.ftn61.com
Category: Hỏi Đáp