Như các bạn đã biết Microsoft bao gồm 3 hàm dùng để làm tìm giá chỉ trị, LOOKUP, VÀ HLOOKUP, và người tiêu dùng cảm thấy hoảng loạn khi áp dụng 3 hàm này nhất. Trong nội dung 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ạ khiến cho bạn sử dụng Hlookup một cách tác dụng nhất.

Bạn đang xem: Hướng dẫn sử dụng hàm hlookup trong excel


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

Hàm HLOOKUP được áp dụng để kiếm tìm một giá trị nhất quyết trong mặt hàng trên cùng của bảng và trả lại một quý giá khác trong thuộc cột từ mặt hàng mà các bạn chỉ định.

Hàm HLOOKUP áp dụng được cho Microsoft Excel 2016, Excel 2013, Excel 2010, Excel 2007 cùng 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ị bắt buộc tìm. Hoàn toàn 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 search kiếm giá trị gồm hai hàng tài liệu trở lê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 bắt buộc được đặt ở số 1 tiên của table_array

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

Range_lookup (tuỳ chọn): một giá chỉ trị xúc tích và ngắn gọn (Boolean) cho biết 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, tác dụng khớp kha khá được trả về. Tức là nếu hiệu quả khớp đúng mực không được tra cứu thấy, hàm Hlookup của các bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value.

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

Để dễ dàng nắm bắt hơn, chúng ta cũng có thể dịch cú pháp HLOOKUP của Excel sang trọng tiếng Việt như sau

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

Để hiểu bí quyết hoạt động, họ hãy thuộc xem một ví dụ solo giản. Trả sử bạn có một bảng với tin tức cơ bản về các hành tinh của hệ phương diện trời. Bạn có nhu cầu hàm trả về đường kính của hành tinh có 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, ô cất tên của hành tinh bạn có nhu cầu tìm

Table_arrray là B2:I3, bảng đựng giá trị bắt buộc tìm

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

Range_lookup  là FALSE vị hàng thứ nhất của bảng ko được sắp xếp theo thứ tự từ bỏ A mang lại Z, nên bọn họ chỉ hoàn toàn có thể tìm giá trị đúng chuẩn ở trong ví dụ này

Bây giờ đồng hồ xếp các tham số vào và họ có phương pháp như sau:

=HLOOKUP (40, A2:B15, 2)

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

Bất cứ lúc nào bạn tìm cực hiếm trong hàng, hãy nhớ đa số điều sau:

Hàm HLOOKUP chỉ rất có thể tìm ở sản phẩm trên cùng của table_array. Nếu như bạn cần tìm ở đều 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õ ràng được chữ hoa và thường ví như range_lookup được đặt là TRUE hoặc quăng quật trống (kết quả khớp tương đối), những giá trị ở số 1 của table_array nên được xếp theo vật dụng tự tăng mạnh (A-Z) từ bỏ trái lịch sự phải.

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

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

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

Hình tiếp sau đây thể hiện sự khác biệt giữa hai bí quyết Vlookup với Hlookup

Khi nào thực hiện VLOOKUP, lúc 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 vướng mắc với thắc mắc này. Họ có 1 tuyệt kỹ để dễ dàng nhận diện nó, đó là địa thế 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 tài 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 vào EXCEL

Tìm quý giá theo sản phẩm với công dụng khớp kha khá và chính xác

Hàm HLOOKUP vào Excel hoàn toàn có thể tìm công dụng khớp đúng chuẩn và không đúng mực dựa trên cực hiếm của thông số range_lookup

TRUE hoặc quăng quật trống: hiệu quả khớp tương đốiFALSE: kết quả khớp chủ yếu xác

Nên lưu giữ rằng mặc dù họ thường nói là hiệu quả 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 đầu tiên. Đặt thông số là FALSE được cho phép hàm trả về tác dụng khớp kha khá (giá trị sớm nhất nhở hơn giá trị cần tìm kiếm) nếu tác dụng khớp đúng đắn không kiếm tìm thấy; TRUE hoặc bị quăng quật trống vẫn trả về lỗi #N/A

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

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

Giả sử bạn có một danh sách những hành tinh ở sản phẩm 2 (B2:I2) và ánh sáng của bọn chúng ở sản phẩm 1 (B1:I1). Bạn muốn tìm địa cầu có ánh nắng mặt trời bằng ánh nắng mặt trời ở ô B4.

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

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

=HLOOKUP (B4, B1:I2, 2)

Hãy lưu giữ rằng hiệu quả khớp kha khá yêu ước sắp xếp những giá trị ở số 1 tiên từ nhỏ tuổi nhất đến lớn nhất hoặc trường đoản cú A mang đến Z, còn nếu không hàm Hlookup sẽ đưa ra công dụng sai.

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

Hlookup với hiệu quả khớp chủ yếu xác

Nếu các bạn biết đúng chuẩn giá trị buộc phải tìm, bạn cũng 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, tác dụng khớp tường dối dễ dãi sử dụng hơn vì nó ko yêu cầu bố trí dữ liệu ở hàng đầu tiên. Khía cạnh khác, nếu kết quả khớp đúng đắn không được tìm thấy, lỗi #N/A sẽ tiến hành trả về.

Xem thêm: 5+ Top Ổ Cứng Hãng Nào Tốt Nhất Hiện Nay Tương Thích Cao Giá Từ 1Tr3

Tip Để giúp người tiêu dùng không cảm thấy bối rối 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 với gửi thông điệp của công ty

=INFERROR (HLOOKUP (B4, B1:I2, 2, FALSE), “Xin lỗi, không 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ừ một trang tính hoặc một bảng tính không giống nghĩa là bạn phải cung cấp các tham chiếu ngoại tuyến mang đến hàm HLOOKUP

Để lấy quý hiếm khớp xuất phát điểm từ một trang tính khác, bạn phải chứng thật tên của trang tính để trước vệ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 cách hoặc kí tự không hẳn chữ cái, đặt nó trong lốt ngoặc solo như sau:

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

Khi tham chiếu trường đoản cú bảng tính khác, viế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 muốn lấy giá bán trị xuất phát từ một bảng tính đóng, bạn phải chỉ đi ra ngoài đường dẫn cho bảng tính:

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

Tip: Thay vày đánh tên bảng tính và trang tính bằng tay, bạn cũng có thể chọn những ô vào trang tính khác và Excel sẽ thêm tham chiếu ngoại con đường vào hàm của người sử dụng một cách auto

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

Như vào trường phù hợp của Vlookup, hàm HLOOKUP chất nhận được sử dụng các kí tự đại diện thay mặt sau mang lại tham số lookup_value:

Dấu hỏi (?) nhằm khớp với bất kỳ một kí tự 1-1 nàoDấu sao (*) nhằm khớp với một chuỗi các kí tự

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

Ví dụ, các bạn có một list tên các quý khách hàng ở mặt hàng 1 và 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 các bạn không thể lưu giữ tên chính xác vị khách nhưng chỉ 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ố ở mặt hàng 2 (row_index_num), bí quyết như sau:

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

Để công thức linh hoạt hơn, chúng ta cũng có thể đánh quý hiếm tìm kiếm vào một ô vắt thể, ví dụ B4, cùng gắn vào ô đó kí tự thay mặt như sau:

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

Lưu ý

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

Ô tham chiếu tuyệt đối hoàn hảo và kha khá trong HLookup

Nếu ai đang viết hàm Hlookup cho 1 ô, bạn có thể không cần thân thiện nên thực hiện ô tham chiếu hoàn hảo hay tương đối vì cả hai số đông phù hợp. Tuy nhiên, khi coppy một cách làm cho nhiều ô tham chiếu, bạn cần phân biệt được hai loại này

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

Để dễ nắm bắt hơn, họ cùng xem kĩ hàm Hlookup khi đem dữ liệu từ 1 trang tính không giống

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

Trong bí quyết trên, chúng ta sử dụng tham chiếu tuyệt vời ($B$1:$I$2) trong table_array vì chưng nó cần phải giữ không thay đổi khi phương pháp được xào nấu từ những ô khác

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

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

Như chúng ta đã biết, hàm HLOOKUP bao gồm một vài tinh giảm với nhược điểm lớn số 1 là cấp thiết thể tìm các giá trị ở ngoài bậc nhất và buộc phải xếp các giá trị theo máy tự khi tìm công dụng khớp tương đối. Như ý thay, sự kết hợp giữa hàm INDEX với MATCH sẽ khắc phục được phần đông điểm này:

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

Giả sử quý hiếm tìm kiếm ở ô B7, nhiều người đang tìm một công dụng khớp ở 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 dưới đây, chúng ta cũng có thể thấy trong cả 2 trường vừa lòng INDEX MATCH đều hoạt động tốt.

Cách thực hiện H-lookup sáng tỏ dạng chữ vào Excel

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

INDEX (hàng đựng giá trị trả về, MATCH (TRUE, EXACT (hàng kiếm tìm kiếm, quý giá tìm kiếm), 0))

Giả sử quý hiếm tìm kiếm của người sử dụng ở ô B4, mảng kiếm tìm kiếm là B1:I1, mảng quý giá trả về là B2:I2, công thức gồm dạng như sau:

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

Lưu ý: Đây là phương pháp mảng vì thế bạn nên ấn tổng hợp Ctrl + Shift + Enter sau thời điểm viết công thức

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

Hàm Hlookup là 1 hàm phức tạp bởi nó có rất nhiều thành bên 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 hàm HLOOKUP của người tiêu dùng không vận động được, nguyên nhân có thể là một trong những lỗi dưới đây

HLOOKUP không thể tìm kiếm các hàng phía bên trên

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

kết quả khớp tương đối và đúng chuẩn

Khi ai đang tìm giá trị trong Excel, theo sản phẩm ngang giỏi dọc, đa số các trường hợp là ai đang tìm kiếm một giá chỉ trị rõ ràng nên cần công dụng khớp chính xác. Lúc tìm kiếm với hiệu quả khớp tương đối (range_lookup được đặt TRUE hoặc quăng quật trống), chúng ta nên nhớ xếp những giá trị trong sản phẩm theo thiết bị tự tăng dần.

Bảng tham chiếu chuyển đổi khi xào luộc công thức

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

tiếp tế hoặc xoá bớt hàng mới

Để hiểu vì sao thêm một hàng mới có thể làm hỏng bí quyết Hlookup, bạn phải hiểu bí quyết HLOOKUP lấy tin tức về quý hiếm tìm kiếm đó 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 lợi nhuận dựa trên ID sản phẩm. đông đảo số liệu này ở hàng 4 nên các bạn đánh 4 vào tham số row_index_num. Nhưng lúc 1 hàng mới được thêm vào, nó biến hàng sản phẩm công nghệ 5 và Hlookup ko thể hoạt động được. Vụ việc xảy ra tương tự như như khi chúng ta xoá sút một sản phẩm trong bảng.

Cách giải quyết và 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 sử dụng công thức INDEX/MATCH cầm cố cho Hlookup. Vào Index/Match, các bạn phải chỉ rõ những hàng nhằm tìm cực hiếm và trả cực hiếm về như mảng tham chiếu cùng Excel có thể tự điều chỉnh các tham chiếu này. Dựa vào vậy, bạn không bắt buộc phải băn khoăn lo lắng về vụ việc thêm giỏi xoá sút hàng như khi áp 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 cực hiếm là giá chỉ trị trước tiên trong bảng khớp với cái giá trị tra cứu kiếm.

Nếu bao gồm một vài cực hiếm giống nhau vào bảng, chọn 1 trong các phương án 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 nguyên tắc của Excel Nếu bạn có nhu cầu trùng lặp được không thay đổi trong dữ liệu, tạo PivotTable nhằm nhóm và lọc các dữ liệu theo yêu thương cầu của bản thân mình Sử dụng công thức mảng nhằm rút những giá trị trùng khỏi mảng search kiếm khoảng trắng

Khi phương pháp Hlookup của doanh nghiệp hoàn toàn đúng nhưng lại lỗi #N/A vẫn bị trả về, hãy kiểm tra lại bảng và quý hiếm tìm kiếm xem tất cả kí từ bỏ trắng như thế nào không. Bạn cũng có thể nhanh chóng thải trừ các khoảng tầm trống bằng cách sử dụng hàm TRIM trong Excel.

Số được thiết lập dạng văn bản

Chuỗi văn phiên bản giống như số là một trong những khó khăn không giống khi sử dụng công thức Excel. Tế bào tả chi tiết của sự việc này đã được giải thích trong lý do các cách làm Excel xong hoạt đụng

cực hiếm tìm tìm vượt thừa 255 kí từ

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

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

Nếu bạn tiến hành hàm Hlookup xuất phát điểm từ một bảng tính khác, cần nhớ rằng bạn phải cung cấp tương đối đầy đủ đường dẫn đến nó.

không nên tham số

Các chúng ta nên nhớ rằng hàm Hlookup là 1 trong hàm phức tạp nên thật cẩn thận khi sử dụng. Dưới đây là một vài lỗi thịnh hành khi nhập không đúng tham số

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

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