Cách Sử Dụng Hàm Hlookup Trong Excel

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 sử dụng 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 vào Excel và gửi một vài lấy một ví dụ minh hoạ giúp đỡ bạn sử dụng Hlookup một cách hiệu quả nhất.

Bạn đang xem: Cách sử dụng hàm hlookup trong excel


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

Hàm HLOOKUP được áp dụng để tra cứu một giá chỉ trị nhất định 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 cùng cột từ sản phẩm mà bạn chỉ định.

Hàm HLOOKUP áp dụng được cho Microsoft Excel 2016, 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, )

Với

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


*

*

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

Row_index_num (bắt buộc): số mặt hàng trong table_array vị trí mà cực hiếm được trả về. Ví dụ, để trả lại cực hiếm trùng khớp trường đoản cú hàng sản phẩm công nghệ 2, để row_index_num bằng 2.

Range_lookup (tuỳ chọn): một giá trị logic (Boolean) cho biết thêm HLOOKUP rất cần phải tìm công dụng khớp đúng chuẩn hay tương đối.

Nếu TRUE hoặc bỏ qua, công dụng khớp kha khá được trả về. Tức là nếu công dụng khớp chính xác không được kiếm tìm thấy, hàm Hlookup của 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ỉ hiệu quả khớp chính xác được trả về. Nếu như không giá trị nào trong hàng hướng dẫn và chỉ định khớp chính xác với giá trị tìm kiếm, hàm Hlookup sẽ trả về lỗi #N/A.

Để dễ dàng nắm bắt 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 giá trị này, vào bảng này, trả lại quý giá từ mặt hàng này, )

Để hiểu biện pháp hoạt động, chúng ta hãy thuộc xem một ví dụ solo giản. Trả sử bạn có một bảng với thông tin cơ bạn dạng về những hành tinh của hệ mặt trời. Bạn muốn hàm trả về 2 lần bán kính của hành tinh mang tên trong ô B5.

Trong bí quyết Hlookup, họ sẽ sử dụng các tham số sau

Lookup_value là B5, ô đựng tên của hành tinh bạn muốn tìm

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

Row_index_num là 2 do Đường kính làm việc hàng thứ hai trong bảng

Range_lookup  là FALSE bởi vì hàng thứ nhất của bảng không được bố trí theo đồ vật tự tự A mang đến Z, nên chúng ta chỉ rất có thể tìm giá bán trị đúng mực ở trong lấy ví dụ như này

Bây giờ xếp các tham số vào và họ có bí quyết 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ứ khi nào bạn tìm cực hiếm trong hàng, hãy nhớ phần nhiều điều sau:

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 kiếm tìm ở rất nhiều vị trí khác, hãy thực hiện công thức Index hoặc Match HLOOKUP trong Excel không biệt lập được chữ hoa với thường trường hợp range_lookup được đặt là TRUE hoặc vứt trống (kết quả khớp tương đối), những giá trị ở hàng đầu của table_array cần được xếp theo sản phẩm công nghệ tự tăng mạnh (A-Z) tự trái sang 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á bán trị. Tuy thế chúng không giống nhau trong bí quyết hoạt động. Bọn họ dễ dàng nhận thấy tên của hai 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 sử dụng hàm VLOOKUP để tìm quý hiếm theo cột sống phía phía bên trái của dữ liệu bạn có nhu cầu tìm. Hàm HLOOKUP dùng làm tìm quý giá theo sản phẩm ngang. Nó tìm quý giá ở bậc nhất tiên của bảng cùng trả lại quý hiếm ở hàng được chỉ định và hướng dẫn ở trong và một cột.

Hình tiếp sau đây thể hiện sự không giống nhau giữa hai bí quyết Vlookup với 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 câu hỏi này. Chúng ta có 1 tuyệt kỹ để dễ dàng dấ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 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ì dùng VlookupNếu lookup_value nằm trên dòng đầu tiên của bảng thì sử dụng Hlookup

ỨNG DỤNG CỦA HLOOKUP vào EXCEL

Tìm quý hiếm theo sản phẩm với kết quả khớp kha khá và bao gồm xác

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

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

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

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

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

Giả sử bạn có một danh sách các hành tinh ở sản phẩm 2 (B2:I2) và ánh nắng mặt trời của bọn chúng ở hàng 1 (B1:I1). Bạn muốn tìm hành tinh có nhiệt độ 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 độ cần tìm, chúng ta cũng có thể xây dựng hàm Hlookup để lấy ra công dụng khớp sớm nhất nếu giá trị đúng mực không được tìm kiếm thấy.

Ví dụ, để biết hành tinh như thế nào có ánh sáng trung bình vào khoảng -340°F, bạn áp dụng công thức Hlookup như sau (range_lookup được đặt là TRUE vào trường 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 mong sắp xếp các giá trị ở bậc nhất 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 như không hàm Hlookup sẽ chuyển ra tác dụng sai.

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

Hlookup với kết quả khớp chính xác

Nếu các bạn biết đúng mực giá trị yêu cầu tìm, bạn 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 tiện lợi sử dụng hơn bởi nó ko yêu cầu bố trí dữ liệu ở bậc nhất tiên. Mặt khác, nếu công dụng 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: Cách Đăng Nhập Techcombank Trên Điện Thoại, Cách Đăng Ký Internet Banking Techcombank

Tip Để giúp người dùng không cảm thấy bồn chồn khi bắt gặp 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 người sử 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 sử dụng hàm Hlookup tự worksheet hoặc workbook khác

Nhìn chung, tìm quý giá 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à các bạn phải cung cấp các tham chiếu nước ngoài tuyến mang đến hàm HLOOKUP

Để lấy quý giá khớp 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 dấu “!”. Ví dụ:

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

*

Nếu thương hiệu trang tính chứa dấu biện pháp hoặc kí tự không phải chữ cái, để nó trong vết 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, đánh 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ị từ một bảng tính đóng, bạn phải chỉ đi ra đường dẫn đến bảng tính:

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

Tip: Thay bởi vì đánh tên bảng tính với trang tính bằng tay, bạn có thể chọn những ô vào 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 trong những phần (sử dụng kí trường đoản cú đại diện)

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

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

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

Ví dụ, bạn có một danh sách tên các khách hàng ở mặt hàng 1 cùng số order ở sản phẩm 2. Bạn muốn tìm số order của một người sử 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 cơ mà chỉ nhớ nó bắt đầu với chữ “La”.

Cho rằng dữ liệu của người sử dụng ở ô B1:I2 (table_array) và thứ tự các 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ó thể đánh giá trị tìm kiếm vào trong 1 ô cụ thể, lấy một ví dụ B4, với gắn vào ô kia kí tự đại diện như sau:

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

Lưu ý

Để hàm HLOOKUP với kí tự đại diện đưa công dụng đúng, range_lookup yêu cầu được đặt chế độ FALSENếu table_array chứa đựng nhiều hơn một quý hiếm khớp với kí trường đoản cú đại diện, giá bán 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 bạn đang viết hàm Hlookup cho một ô, chúng ta cũng có thể không cần thân mật nên thực hiện ô tham chiếu hoàn hảo hay kha khá vì cả hai đông đảo phù hợp. Mặc dù nhiên, khi xào luộc một phương pháp cho những ô 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 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à kha khá hoặc hỗn hợp dựa trên logic marketing 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ừ một trang tính khác

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

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

Đối cùng với lookup_value (B$1), chúng ta sử dụng tham chiếu láo lếu hợp, cột kha khá và hàng tuyệt vời vì các giá trị tra cứu kiếm (tên hành tinh) nằm trên cùng 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ới cột tham chiếu nên đổi khác dựa bên trên vị trí kha khá 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 gồm một vài tiêu giảm với nhược điểm lớn số 1 là cần yếu thể tìm các giá trị nằm ngoài số 1 và đề xuất xếp các giá trị theo thứ tự lúc 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 và MATCH đang khắc phục được đều đ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á chỉ trị, 0))

Giả sử cực hiếm tìm tìm ở ô B7, bạn đang tìm một kết quả khớp ở hàng 2 (B2:I2), và muốn 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, bạn cũng có thể thấy trong cả 2 trường vừa lòng INDEX MATCH đều chuyển động tốt.

Cách áp dụng H-lookup riêng biệt dạng chữ trong Excel

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

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

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

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

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

10 LÝ bởi vì PHỔ BIẾN KHIẾN HLOOKUP KHÔNG CHẠY ĐƯỢC

Hàm Hlookup là một trong hàm phức tạp bởi vì nó có tương đối nhiều thành phần nằm trong công thức khiến các 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 được, nguyên nhân hoàn toàn có thể là một trong các lỗi bên dưới đây

HLOOKUP chẳng thể tìm kiếm những hàng phía trên

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

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

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

Bảng tham chiếu biến hóa khi xào luộc công thức

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

cấp dưỡng hoặc xoá sút hàng mới

Để hiểu nguyên nhân thêm một sản phẩm mới có thể làm hỏng phương pháp Hlookup, bạn phải hiểu giải 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 các bạn chỉ định

Giả sử bạn có nhu cầu lấy số liệu lợi nhuận dựa bên trên ID sản phẩm. đều số liệu này ở mặt hàng 4 nên bạn đánh 4 vào tham số row_index_num. Nhưng khi 1 hàng mới được thêm vào, nó trở nên hàng thứ 5 với Hlookup ko thể hoạt động được. Vấn đề xảy ra giống như như khi bạn xoá bớt một sản phẩm trong bảng.

Cách giải quyết là khoá bảng để tránh việc người tiêu dùng thêm một hàng new vào hoặc thực hiện công thức INDEX/MATCH nắm cho Hlookup. Vào Index/Match, chúng ta phải chỉ rõ các hàng nhằm tìm giá trị 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, chúng ta không buộc phải phải lo ngại về vấn đề thêm giỏi xoá bớt hàng như khi thực hiện công thức Hlookup.

giống nhau trong bảng

Hàm HLOOKUP vào Excel chỉ có thể trả về một cực hiếm là giá bán trị trước tiên trong bảng khớp với mức giá trị search kiếm.

Nếu bao gồm một vài giá trị giống nhau vào bảng, chọn một trong các phương án mà tương xứng với chúng ta nhất:

Loại bỏ trùng lặp bằng cách dùng pháp luật 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 ra PivotTable để nhóm cùng lọc các dữ liệu theo yêu cầu của chính mình Sử dụng công thức mảng nhằm rút các giá trị trùng khỏi mảng kiếm tìm kiếm khoảng tầm trắng

Khi phương pháp Hlookup của công ty hoàn toàn đúng nhưng mà lỗi #N/A vẫn bị trả về, hãy bình chọn lại bảng và giá trị tìm tìm xem tất cả kí từ trắng như thế nào không. Chúng ta có thể nhanh chóng loại trừ các khoảng chừng trống bằng phương pháp sử dụng hàm TRIM vào Excel.

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

Chuỗi văn bạn dạng giống như số là 1 trong những khó khăn không giống khi sử dụng công thức Excel. Mô tả chi tiết của sự việc này sẽ được phân tích và lý giải trong tại sao các cách làm Excel dứt hoạt hễ

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

Tất cả những hàm tra cứu kiếm vào Excel chỉ vận động khi quý hiếm tìm kiếm dưới 255 kí tự. Quý giá tìm kiếm dài hơn 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ó thể khắc phục lỗi này khi thực hiện INDEX/MATCH

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

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

không đúng tham số

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

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

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