Như 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 dùng cảm thấy hoảng sợ khi áp 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 vào Excel và đưa một vài lấy ví dụ minh hoạ khiến cho bạn sử dụng Hlookup một cách kết quả nhất.

Bạn đang xem: Hàm hlookup nhiều điều kiện


HÀM HLOOKUP trong EXCEL LÀ GÌ?

Hàm HLOOKUP được thực hiện để search một giá bán trị nhất mực trong sản phẩm trên thuộc của bảng cùng trả lại một giá trị khác trong thuộc cột từ sản phẩm mà các bạn chỉ định.

Hàm HLOOKUP thực hiện được mang lại 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á chỉ trị yêu cầu tìm. Rất 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á chỉ trị tất cả hai hàng dữ 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ị tra cứu kiếm cần được đặt ở hàng đầu tiên của table_array

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

Range_lookup (tuỳ chọn): một giá chỉ trị súc tích (Boolean) cho thấy HLOOKUP rất cần được tìm kết quả khớp đúng mực hay tương đối.

Nếu TRUE hoặc quăng quật qua, hiệu quả khớp kha khá được trả về. Tức thị nếu tác dụng khớp đúng đắn không được search 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 nhỏ hơn look_up value.

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

Để dễ hiểu hơn, bạn 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 giá trị này, trong bảng này, trả lại cực hiếm từ mặt hàng này, )

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

Trong phương pháp 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 có nhu cầu tìm

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

Row_index_num là 2 bởi Đường kính nghỉ ngơi hàng thứ 2 trong bảng

Range_lookup  là FALSE vì chưng hàng đầu tiên của bảng ko được thu xếp theo lắp thêm tự từ A đến Z, nên họ chỉ có thể tìm giá bán trị chính xác ở trong lấy ví dụ này

Bây tiếng xếp những tham số vào và họ 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ứ bao giờ bạn tìm quý hiếm trong hàng, hãy nhớ mọi điều sau:

Hàm HLOOKUP chỉ có thể tìm ở sản phẩm trên cùng của table_array. Nếu bạn cần search ở gần như vị trí khác, hãy thực hiện công thức Index hoặc Match HLOOKUP trong Excel không phân minh được chữ hoa với thường nếu 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ị ở hàng đầu của table_array đề nghị được xếp theo sản phẩm tự tăng vọt (A-Z) trường đoản cú trái thanh lịch 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 tìm một giá chỉ trị. Tuy nhiên chúng khác nhau trong phương pháp hoạt động. Họ dễ dàng nhận biết tên của nhì hàm khác biệt ở vần âm đầu – “H” là ngang (horizontal) và “V” là dọc (vertical)

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

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

Khi nào sử dụng VLOOKUP, bao giờ 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 thắc mắc này. Chúng ta có 1 tuyệt kỹ để dễ dàng nhận diện nó, kia 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à 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 VlookupNế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 có thể tìm công dụng khớp đúng chuẩn và không đúng đắn dựa trên quý hiếm của tham số range_lookup

TRUE hoặc vứt trống: tác dụng khớp tương đốiFALSE: công dụng khớp chính xác

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

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

HLOOKUP với tác dụng khớp tương đối

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

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

Ví dụ, để biết hành tinh nào 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 để 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 các giá trị ở bậc nhất tiên từ nhỏ dại nhất đến lớn số 1 hoặc tự A mang đến Z, 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ề địa cầu Hải Vương, giữa những hành tinh lạnh tuyệt nhất trong hệ khía cạnh trời luôn giữ ánh sáng trung bình vào thời gian -346 độ F.

Hlookup với công dụng khớp bao gồm xác

Nếu bạn biết đúng mực giá trị phải tìm, chúng ta cũng có thể đặt tham số sau cùng 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 dễ dàng sử dụng hơn vày nó không yêu cầu sắp xếp dữ liệu ở bậc nhất tiên. Mặt khác, nếu hiệu quả khớp chính xác không được tìm thấy, lỗi #N/A sẽ tiến hành trả về.

Xem thêm: Tổng Hợp Font Chữ Thời Trang Ấn Tượng 2021, Tổng Hợp Font Chữ Thời Trang Hiện Đại Nhất 2021

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

=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 tự worksheet hoặc workbook khác

Nhìn chung, tìm quý hiếm theo hàng xuất phát từ một trang tính hoặc một bảng tính không giống nghĩa là chúng ta phải cung cấp các tham chiếu ngoại tuyến mang lại hàm HLOOKUP

Để lấy cực hiếm khớp xuất phát điểm từ 1 trang tính khác, cá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 tên trang tính chứa dấu bí quyết hoặc kí tự không phải chữ cái, đặt nó trong lốt ngoặc đơn 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 vào 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 phải 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 bởi đánh tên bảng tính và trang tính bằng tay, chúng ta có thể chọn những ô trong trang tính khác với Excel sẽ thêm tham chiếu ngoại tuyến đường vào hàm của công ty một cách tự động hóa

HLOOKUP với hiệu quả khớp một phần (sử dụng kí từ bỏ đại diện)

Như vào trường vừa lòng của Vlookup, hàm HLOOKUP được cho phép sử dụng các kí tự thay mặt sau đến 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 (*) để khớp với cùng 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 muốn lấy thông tin xuất phát điểm từ một cơ sở dữ liệu dựa vào một vài ba đoạn văn phiên bản nằm trong văn bản của ô search kiếm

Ví dụ, các bạn có một danh sách tên các quý khách hàng ở sản phẩm 1 với số order ở sản phẩm 2. Bạn có nhu cầu tìm số order của một quý khách nhất định nào đó nhưng chúng ta không thể lưu giữ tên đúng chuẩn vị khách mà chỉ lưu giữ nó bắt đầu với chữ “La”.

Cho rằng dữ liệu của chúng ta ở ô B1:I2 (table_array) với thứ tự các số ở sản phẩm 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ũng có thể đánh cực hiếm tìm kiếm vào một trong những ô vậy thể, ví dụ như B4, và 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 cùng với kí tự đại diện thay mặt đưa tác dụng đúng, range_lookup yêu cầu đượ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á trị trước tiên tìm được sẽ được trả về

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

Nếu ai đang viết hàm Hlookup cho một ô, chúng ta có thể không cần thân thương nên sử dụng ô tham chiếu tuyệt vời và hoàn hảo nhất hay kha khá vì cả hai đầy đủ phù hợp. Tuy nhiên, khi sao chép một cách làm cho nhiều ô tham chiếu, bạn cần phân biệt được hai nhiều loại này

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

Để dễ hiểu hơn, họ cùng coi 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 phương pháp trên, chúng ta sử dụng tham chiếu hoàn hảo ($B$1:$I$2) vào table_array vày nó cần phải giữ không thay đổi khi phương pháp được sao chép từ những ô khác

Đối với lookup_value (B$1), bọn họ sử dụng tham chiếu lếu hợp, cột tương đối và hàng hoàn hảo và tuyệt vời nhất vì những giá trị search kiếm (tên hành tinh) ở trên cùng một hàng (hàng 1) cơ mà ở những cột khác biệt (từ B đến I) cùng cột tham chiếu nên chuyển đổi dựa trên vị trí kha khá của ô chứa bí quyết đượ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 gồm một vài tinh giảm với nhược điểm lớn số 1 là không thể thể tìm những giá trị nằm ngoài hàng đầu và đề xuất xếp các giá trị theo vật dụng tự khi tìm công dụng khớp tương đối. Suôn sẻ thay, sự kết hợp giữa hàm INDEX cùng MATCH vẫn khắc phục được phần đa điểm này:

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

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

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

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

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

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

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

Giả sử quý hiếm tìm kiếm của người sử dụng ở ô B4, mảng tra cứu 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ì vậy bạn đề xuất ấn tổ hợp Ctrl + Shift + Enter sau thời điểm viết phương pháp

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

Hàm Hlookup là một trong những hàm phức tạp bởi nó có không ít thành phần trong công thức khiến các lỗi #N/A, #VALUE hoặc #REF liên tục 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 số lỗi bên dưới đây

HLOOKUP quan trọng tìm kiếm các hàng phía trên

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

hiệu quả khớp kha khá và chính xác

Khi bạn đang tìm cực hiếm trong Excel, theo mặt hàng ngang xuất xắc dọc, đa số các trường hòa hợp là bạn đang tìm tìm một giá trị ví dụ nên cần kết quả khớp thiết yếu xác. Khi tìm kiếm với công dụng khớp tương đối (range_lookup được để TRUE hoặc bỏ trống), chúng ta nên lưu giữ 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 biến đổi khi sao chép công thức

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

sản xuất hoặc xoá giảm hàng mới

Để hiểu vì sao thêm một hàng mới rất có thể làm hỏng cách làm Hlookup, bạn phải hiểu phương pháp HLOOKUP lấy thông tin về cực 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 muốn lấy số liệu lợi nhuận dựa bên trên ID sản phẩm. đầy đủ 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 lúc 1 hàng bắt đầu được thêm vào, nó biến đổi hàng lắp thêm 5 cùng Hlookup ko thể chuyển động được. Vấn đề xảy ra tương tự như khi bạn xoá giảm mộ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 bắt đầu vào hoặc thực hiện công thức INDEX/MATCH rứa cho Hlookup. Trong Index/Match, chúng ta phải chỉ rõ những hàng để tìm quý giá và trả giá trị 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 nên phải lo ngại về vấn đề thêm tuyệt xoá bớt hàng như khi áp dụng công thức Hlookup.

đụng hàng trong bảng

Hàm HLOOKUP vào Excel chỉ có thể trả về một giá trị là giá trị thứ nhất trong bảng khớp với cái giá trị tra cứu kiếm.

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

Loại quăng quật trùng lặp bằng cách dùng phép tắc của Excel Nếu bạn có nhu cầu trùng lặp được giữ nguyên trong dữ liệu, chế tác PivotTable để nhóm và lọc những dữ liệu theo yêu thương cầu của mình Sử dụng bí quyết mảng để rút những giá trị trùng khỏi mảng tra cứu kiếm khoảng tầm trắng

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

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

Chuỗi văn bạn dạng giống như số là một khó khăn khác khi áp dụng công thức Excel. Mô tả cụ thể của sự việc này vẫn được lý giải trong lý do các cách làm Excel hoàn thành hoạt động

quý giá tìm kiếm vượt thừa 255 kí tự

Tất cả những hàm tìm kiếm trong Excel chỉ chuyể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 nữa sẽ trả lại hiệu quả là lỗi #VALUE!. Vì hàm INDEX/MATCH giới hạn max số lượng kí trường đoản cú nên bạn có thể khắc phục lỗi này khi thực hiện INDEX/MATCH

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

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

sai tham số

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

Nếu row_index_num nhỏ dạ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 search kiếm, 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 bé dại hơn giá trị nhỏ nhất trong hàng đầu tiên của table_array, lỗi #N/A sẽ được trả lại

Để rất có thể ứng dụng tốt Excel vào vào công việc, chúng ta không chỉ nắm rõ được các hàm cơ mà còn cần sử dụng tốt cả các công vắt của Excel. Rất nhiều 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 luật pháp thường thực hiện là Data validation, Conditional formatting, Pivot table…