Hàm Hlookup Là Gì

     

Như bạn đã biết Microsoft tất cả 3 hàm dùng làm tìm giá trị, LOOKUP, VÀ HLOOKUP, và người tiêu dùng cảm thấy bồn chồn khi thực hiện 3 hàm này nhất. Trong nội dung bài viết này, Học Excel Online sẽ triệu tập vào các điểm sáng của hàm HLOOKUP trong Excel và chuyển một vài lấy một ví dụ minh hoạ giúp đỡ bạn sử dụng Hlookup một cách công dụng nhất.

Bạn đang xem: Hàm hlookup là gì


HÀM HLOOKUP vào EXCEL LÀ GÌ?

Hàm HLOOKUP được sử dụng để tra cứu một giá bán trị cố định trong hàng trên cùng của bảng cùng trả lại một quý giá khác trong thuộc cột từ sản phẩm mà bạn chỉ định.

Hàm HLOOKUP sử dụng được mang đến Microsoft Excel 2016, Excel 2013, Excel 2010, Excel 2007 với phiên 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, )

Với

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


*

*

Table_array (bắt buộc): bảng tìm kiếm giá bán trị tất cả hai hàng tài liệu trở lên. Hoàn toàn có thể là mảng thường, được lấy tên hoặc bảng Excel. Những giá trị tìm kiếm kiếm bắt buộc được để ở bậc nhất tiên của table_array

Row_index_num (bắt buộc): số sản phẩm trong table_array khu vực mà cực hiếm được trả về. Ví dụ, nhằm trả lại cực hiếm trùng khớp tự hàng máy 2, để row_index_num bởi 2.

Range_lookup (tuỳ chọn): một giá chỉ trị lô ghích (Boolean) cho thấy thêm HLOOKUP cần phải tìm hiệu quả khớp đúng đắn hay tương đối.

Nếu TRUE hoặc bỏ qua, hiệu quả khớp tương đối được trả về. Tức là nếu hiệu quả khớp đúng đắn không được kiếm tìm thấy, hàm Hlookup của các bạn sẽ trả về giá bán trị lớn nhất kế tiếp bé dại hơn look_up value.

Nếu FALSE, chỉ tác dụng khớp chính xác được trả về. Còn nếu không giá trị như thế nào trong hàng hướng đẫn khớp đúng chuẩn với giá trị tìm kiếm, hàm Hlookup đang trả về lỗi #N/A.

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

HLOOKUP (tìm kiếm quý hiếm này, vào bảng này, trả lại quý giá từ hàng này, )

Để hiểu cách hoạt động, họ hãy cùng xem một ví dụ 1-1 giản. Trả sử bạn có một bảng với tin tức cơ phiên bản về những hành tinh của hệ mặt trời. Bạn có nhu cầu hàm trả về đường kính của hành tinh mang tên trong ô B5.

Trong phương pháp Hlookup, bọn họ sẽ sử dụng những tham số sau

Lookup_value là B5, ô đựng tên của hành tinh bạn có nhu cầu tìm

Table_arrray là B2:I3, bảng cất giá trị nên tìm

Row_index_num là 2 vì chưng Đường kính sinh sống hàng thứ 2 trong bảng

Range_lookup  là FALSE do hàng đầu tiên của bảng không được bố trí theo thứ tự từ bỏ A mang đến Z, nên họ chỉ có thể tìm giá chỉ trị đúng mực ở trong ví dụ này

Bây giờ đồng hồ xếp những tham số vào và chúng ta có cách làm 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ớ phần lớn điều sau:

Hàm HLOOKUP chỉ có thể tìm ở mặt hàng trên cùng của table_array. Nếu như khách hàng cần tìm kiếm ở số đông vị trí khác, hãy thực hiện công thức Index hoặc Match HLOOKUP vào Excel không rành mạch được chữ hoa và thường nếu như range_lookup được đặt là TRUE hoặc vứt trống (kết quả khớp tương đối), các giá trị ở số 1 của table_array bắt buộc được xếp theo lắp thêm tự tăng mạnh (A-Z) từ trái thanh lịch phải.

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

Như bạn đã biết, cả nhị hàm HLOOKUP VÀ VLOOKUP dùng để làm tìm tìm một giá bán trị. Nhưng mà chúng khác nhau trong phương pháp hoạt động. Bọn họ dễ dàng phân biệt tên của nhị hàm không giống nhau ở chữ cái đầu – “H” là ngang (horizontal) với “V” là dọc (vertical)

Do đó, bạn thực hiện hàm VLOOKUP nhằm tìm quý giá theo cột sinh hoạ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 quý giá theo sản phẩm ngang. Nó tìm giá trị ở bậc nhất tiên của bảng cùng trả lại quý giá ở mặt hàng được hướng dẫn và chỉ định ở trong cùng một cột.

Hình sau đây thể hiện sự khác nhau giữa hai phương pháp Vlookup với Hlookup

Khi nào áp dụng VLOOKUP, bao giờ sử dụng HLOOKUP

Khi nào thực hiện VLOOKUP, khi nào sử dụng HLOOKUP? Hẳn chúng ta rất vướng 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 tìm và công dụng cần tìm)

Nếu lookup_value nằm trên cột đầu tiên của bảng thì cần sử dụng VlookupNếu lookup_value nằm trên dòng đầu tiên của bảng thì cần sử dụng Hlookup

ỨNG DỤNG CỦA HLOOKUP trong EXCEL

Tìm giá trị theo mặt hàng với hiệu quả khớp tương đối và chính xác

Hàm HLOOKUP trong Excel rất có thể tìm kết quả khớp đúng đắn và không đúng chuẩn dựa trên giá trị của thông số range_lookup

TRUE hoặc bỏ trống: công dụng khớp tương đốiFALSE: kết quả khớp bao gồm xác

Nên nhớ rằng mang dù bọn họ thường nói là kết quả khớp tương đối, bí quyết Hlookup luôn tìm một hiệu quả khớp đúng mực đầu tiên. Đặt tham số là FALSE 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ị đề nghị tìm kiếm) nếu hiệu quả khớp đúng mực không tra cứu thấy; TRUE hoặc bị bỏ trống sẽ trả về lỗi #N/A

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

HLOOKUP với hiệu 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à ánh sáng của chúng ở mặt hàng 1 (B1:I1). Bạn có nhu cầu tìm hành tinh có nhiệt độ bằng nhiệt độ ở ô B4.

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

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

=HLOOKUP (B4, B1:I2, 2)

Hãy ghi nhớ rằng hiệu quả khớp kha khá yêu ước sắp xếp những giá trị ở hàng đầu tiên từ bé dại nhất đến lớn số 1 hoặc từ A đến Z, nếu không hàm Hlookup sẽ gửi ra công dụng sai.

Trong hình chụp bên dưới đây, hàm trả về hành tinh Hải Vương, trong số những hành tinh lạnh tốt nhất trong hệ khía cạnh trời luôn luôn giữ ánh sáng trung bình vào thời gian -346 độ F.

Hlookup với tác dụng khớp chính xác

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

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

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

Xem thêm: Họ Cá Thác Lác Tiếng Anh Là Gì, Họ Cá Thát Lát, Cá Thát Lát Tiếng Anh Là Gì

Tip Để giúp người tiêu dùng không cảm thấy hồi hộp khi nhìn thấy lỗi N/A xuất hiện, bạn cũng có thể đưa hàm Hlookup vào hàm INFERROR cùng gửi thông điệp của bạn

=INFERROR (HLOOKUP (B4, B1:I2, 2, FALSE), “Xin lỗi, không tìm kiếm thấy kết quả”)

Cách cần sử dụng hàm Hlookup trường đoản cú worksheet hoặc workbook khác

Nhìn chung, tìm cực hiếm theo hàng xuất phát điểm từ 1 trang tính hoặc một bảng tính khác nghĩa là bạn phải cung ứng các tham chiếu nước ngoài tuyến cho hàm HLOOKUP

Để lấy giá trị khớp xuất phát điểm từ 1 trang tính khác, chúng ta phải chứng thực tên của trang tính đặt trước lốt “!”. Ví dụ:

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

*

Nếu thương hiệu trang tính cất dấu biện pháp hoặc kí tự chưa hẳn chữ cái, đặt nó trong dấu ngoặc solo như sau:

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

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

=HLOOKUP (B$1, Đườngkính!$B$1:$I$2, 2, FALSE)

Nếu bạn có nhu cầu lấy giá bán trị xuất phát điểm từ một bảng tính đóng, bạn cần chỉ đi ra ngoài đường dẫn mang đến bảng tính:

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

Tip: Thay vì chưng đánh thương hiệu bảng tính với trang tính bằng tay, bạn cũng có thể chọn các ô trong trang tính khác với Excel đang thêm tham chiếu ngoại tuyến vào hàm của doanh nghiệp một cách tự động

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

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

Dấu hỏi (?) nhằm khớp với bất kể một kí tự solo nàoDấu sao (*) nhằm khớp với 1 chuỗi những kí tự

Các kí tự thay mặt đại diện rất có lợi khi bạn có nhu cầu lấy thông tin xuất phát từ một cơ sở dữ liệu dựa trên một vài đoạn văn bạn dạng nằm trong câu chữ của ô tìm kiếm

Ví dụ, bạn có một list tên các người tiêu dùng ở sản phẩm 1 cùng số order ở hàng 2. Bạn muốn tìm số order của một người tiêu dùng nhất định nào đó nhưng bạn không thể ghi nhớ tên chính xác vị khách mà lại chỉ ghi nhớ nó bước đầu với chữ “La”.

Cho rằng dữ liệu của doanh nghiệp ở ô B1:I2 (table_array) cùng thứ tự những số ở hàng 2 (row_index_num), bí quyết như sau:

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

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

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

Lưu ý

Để hàm HLOOKUP với kí tự đại diện thay mặt đưa kết quả đúng, range_lookup buộc phải được đặt chế độ FALSENếu table_array đựng được nhiều hơn một quý hiếm khớp với kí trường đoản cú đại diện, giá trị đầu tiên tìm được sẽ được trả về

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

Nếu bạn đang viết hàm Hlookup cho 1 ô, chúng ta có thể không cần vồ cập nên thực hiện ô tham chiếu hoàn hảo hay tương đối vì cả hai phần lớn phù hợp. Mặc dù nhiên, khi xào nấu một công thức cho những ô tham chiếu, bạn phải phân biệt được hai loại này

table_array luôn được đặt thắt chặt và cố định bằng những ô tham chiếu tuyệt đối với kí hiệu “$”, lấy một ví dụ $B$1:$I$2tham chiếu lookup_value là kha khá hoặc lếu láo hợp dựa trên logic sale của bạn

Để dễ nắm bắt hơn, bọn họ cùng xem kĩ hàm Hlookup khi lấy dữ liệu xuất phát từ 1 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 hoàn hảo ($B$1:$I$2) vào table_array vày nó cần phải giữ không đổi khi công thức được xào nấu từ các ô khác

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

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 bao gồm một vài tinh giảm với nhược điểm lớn nhất là quan yếu thể tìm những giá trị ở ngoài bậc nhất và buộc phải xếp những giá trị theo sản phẩm tự khi tìm kết quả khớp tương đối. May mắn thay, sự phối hợp giữa hàm INDEX và MATCH đã khắc phục được gần như điểm này:

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

Giả sử giá trị tìm tìm ở ô B7, nhiều người đang tìm một công dụng khớp ở mặt hàng 2 (B2:I2), và ao ước giá trị trả về từ hàng 1 (B1:I1), cách làm như sau

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

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

Cách thực hiện H-lookup phân minh dạng chữ trong Excel

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

INDEX (hàng chứa giá trị trả về, MATCH (TRUE, EXACT (hàng tìm kiếm kiếm, cực hiếm tìm kiếm), 0))

Giả sử quý hiếm tìm kiếm của doanh nghiệp ở ô B4, mảng tra cứu kiếm là B1:I1, mảng cực hiếm trả về là B2:I2, công thức tất cả dạng như sau:

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

Lưu ý: Đây là bí quyết mảng vì vậy bạn phải ấn tổ hợp Ctrl + Shift + Enter sau khoản thời gian viết cách làm

10 LÝ vì 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 vì nó có không hề ít thành phần nằm trong công thức khiến cho các lỗi #N/A, #VALUE hoặc #REF thường xuyên xảy ra. Nếu như hàm HLOOKUP của doanh nghiệp không chuyển động được, nguyên nhân rất có thể là một trong số lỗi bên dưới đây

HLOOKUP thiết yếu tìm kiếm các hàng bên trên

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

kết quả khớp kha khá và đúng chuẩn

Khi nhiều người đang tìm cực hiếm trong Excel, theo mặt hàng ngang tốt dọc, đa số các trường phù hợp là ai đang tìm kiếm một giá bán trị rõ ràng nên cần tác dụng khớp bao gồm xác. Khi tìm kiếm với hiệu quả khớp tương đối (range_lookup được đặt TRUE hoặc bỏ trống), các bạn nên lưu giữ xếp những giá trị trong mặt hàng theo lắp thêm tự tăng dần.

Bảng tham chiếu thay đổi khi coppy công thức

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

thêm vào hoặc xoá sút hàng mới

Để hiểu lý do thêm một sản phẩm mới hoàn toàn có thể làm hỏng công thức Hlookup, bạn cần hiểu bí quyết HLOOKUP lấy thông tin về cực hiếm tìm kiếm kia là dựa trên chỉ số của hàng bạn chỉ định

Giả sử bạn có nhu cầu lấy số liệu doanh thu dựa bên trên ID sản phẩm. đều số liệu này ở mặt hàng 4 nên chúng ta đánh 4 vào thông số row_index_num. Nhưng khi một hàng bắt đầu được thêm vào, nó vươn lên là hàng sản phẩm 5 với Hlookup không thể chuyển động được. Vấn đề xảy ra tương tự như khi chúng ta xoá giảm một sản phẩm 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 bắt đầu vào hoặc áp dụng công thức INDEX/MATCH vắt cho Hlookup. Vào Index/Match, chúng ta phải chỉ rõ những hàng để tìm quý hiếm và trả cực hiếm về như mảng tham chiếu cùng Excel rất có thể tự điều chỉnh các tham chiếu này. Nhờ vào vậy, các bạn không bắt buộc phải lo lắng về vấn đề thêm giỏi xoá sút hàng như khi sử dụng công thức Hlookup.

đụng hàng trong bảng

Hàm HLOOKUP trong Excel chỉ rất có thể trả về một quý giá là giá bán trị trước tiên trong bảng khớp với giá trị tìm kiếm.

Nếu bao gồm một vài giá trị giống nhau trong bảng, lựa chọn 1 trong các giải pháp mà phù hợp với chúng ta nhất:

Loại quăng quật trùng lặp bằng phương pháp dùng phương tiện của Excel Nếu bạn muốn trùng lặp được không thay đổi trong dữ liệu, tạo PivotTable để nhóm và lọc những dữ liệu theo yêu cầu của bản thân Sử dụng cách làm mảng nhằm rút những giá trị trùng ngoài mảng kiếm tìm kiếm khoảng trắng

Khi bí quyết Hlookup của người tiêu dùng hoàn toàn đúng mà lại lỗi #N/A vẫn bị trả về, hãy khám nghiệm lại bảng và giá trị tìm kiếm xem bao gồm kí từ trắng nào không. Bạn có thể nhanh chóng sa thải các khoảng chừng trống bằng cách sử dụng hàm TRIM vào Excel.

Xem thêm: Ý Nghĩa Của 3 Tháng 10 Ngày, Cúng Mụ 3 Tháng 10 Ngày Cho Bé Có Ý Nghĩa Gì

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

Chuỗi văn phiên bản giống như số là 1 trong những khó khăn không giống khi áp dụng công thức Excel. Tế bào tả cụ thể của vấn đề này đã được lý giải trong lý do các phương pháp Excel kết thúc hoạt hễ

quý giá tìm tìm vượt thừa 255 kí từ bỏ

Tất cả các hàm kiếm tìm kiếm trong Excel chỉ vận động khi giá trị tìm kiếm dưới 255 kí tự. Cực hiếm tìm kiếm dài hơn sẽ trả lại công dụng là lỗi #VALUE!. Vày hàm INDEX/MATCH không giới hạn số lượng kí trường đoản cú nên bạn có thể khắc phục lỗi này khi sử dụng INDEX/MATCH

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

Nếu bạn triển khai hàm Hlookup từ một bảng tính khác, đề xuất nhớ rằng bạn phải cung cấp không hề thiếu đường dẫn đến nó.

không đúng tham số

Các chúng ta nên nhớ rằng hàm Hlookup là một trong hàm phức hợp nên thật cảnh giác khi sử dụng. Dưới đó là một vài lỗi thịnh hành khi nhập sai tham số

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

Để rất có thể ứng dụng xuất sắc Excel vào trong công việc, họ không chỉ nắm rõ được những hàm mà còn nên sử dụng tốt cả các công cố gắng của Excel. Hầu hết hàm nâng cao giúp áp dụng xuất sắc vào quá trình như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những phương tiện thường áp dụng là Data validation, Conditional formatting, Pivot table…