HÀM KẾT HỢP VLOOKUP VÀ HLOOKUP

Như chúng ta đã biết Microsoft bao gồm 3 hàm dùng để tìm giá bán trị, LOOKUP, VÀ HLOOKUP, và người dùng cảm thấy bồn chồn khi thực hiện 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 điểm sáng của hàm HLOOKUP vào 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.

Bạn đang xem: Hàm kết hợp vlookup và hlookup


HÀM HLOOKUP trong EXCEL LÀ GÌ?

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

Hàm HLOOKUP sử dụng được mang lại Microsoft Excel 2016, Excel 2013, Excel 2010, Excel 2007 và 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á chỉ trị nên tìm. Hoàn toàn 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á chỉ trị có hai hàng tài liệu trở lên. Rất có thể là mảng thường, được đặt tên hoặc bảng Excel. Các giá trị tra cứu kiếm bắt buộc được đặt ở số 1 tiên của table_array

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

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

Nếu TRUE hoặc vứt qua, công dụng khớp kha khá được trả về. Nghĩa là nếu công dụng khớp đúng chuẩn không được tìm kiếm thấy, hàm Hlookup của các bạn sẽ trả về giá trị lớn số 1 kế tiếp nhỏ tuổi hơn look_up value.

Nếu FALSE, chỉ hiệu quả khớp đúng đắn được trả về. Còn nếu không giá trị làm sao trong hàng hướng dẫn và chỉ định khớp đúng đắn với quý hiếm tìm kiếm, hàm Hlookup đã trả về lỗi #N/A.

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

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

Để hiểu phương pháp hoạt động, chúng ta hãy cùng xem một ví dụ đối chọi giản. Giả sử các bạn có một bảng với thông tin cơ bản về các hành tinh của hệ khía cạnh 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 phương pháp Hlookup, bọn họ 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ị phải tìm

Row_index_num là 2 vày Đường kính sống hàng thứ hai 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 sản phẩm tự từ A cho Z, nên họ chỉ hoàn toàn có thể tìm giá trị đúng mực ở trong lấy một ví dụ này

Bây giờ đồng hồ xếp các tham số vào và bọn họ 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 vào EXCEL 

Bất cứ lúc nào bạn tìm quý giá trong hàng, hãy nhớ số đông điều sau:

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

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

Hình tiếp sau đây thể hiện sự khác biệt giữa hai cách làm Vlookup cùng Hlookup

Khi nào sử dụng VLOOKUP, lúc nào sử dụng HLOOKUP

Khi nào thực hiện VLOOKUP, lúc nào sử dụng HLOOKUP? Hẳn bạn rất vướng mắc với câu hỏi này. Họ có 1 bí quyết để dễ dàng dấ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 dữ liệu tìm kiếm và hiệu 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ì cần 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 hiệu quả khớp tương đối và chính xác

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

TRUE hoặc bỏ trống: tác dụng khớp tương đốiFALSE: kết quả khớp chủ yếu xác

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

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

HLOOKUP với hiệu quả khớp tương đối

Giả sử chúng ta 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 đị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 chúng ta không biết đúng đắn nhiệt độ yêu cầu tìm, bạn có thể xây dựng hàm Hlookup để mang ra công dụng khớp gần nhất nếu giá bán trị đúng mực không được kiếm tìm thấy.

Ví dụ, để tìm hiểu hành tinh làm sao có nhiệt độ trung bình vào thời gian -340°F, bạn sử dụng công thức Hlookup như sau (range_lookup được để là TRUE vào trường hợp này):

=HLOOKUP (B4, B1:I2, 2)

Hãy nhớ rằng công dụng khớp tương đối yêu mong sắp xếp những giá trị ở bậc nhất tiên từ nhỏ nhất đến lớn nhất hoặc từ A mang lại Z, còn nếu như không hàm Hlookup sẽ chuyển ra hiệu quả sai.

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

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

Nếu bạn biết đúng mực giá trị yêu cầu tìm, chúng ta 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àng sử dụng hơn vì chưng 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 tác dụng khớp đúng chuẩn không được tra cứu thấy, lỗi #N/A sẽ được trả về.

Xem thêm: Hướng Dẫn Cách Tạo Thư Mục Trong Outlook, Tự Động Di Chuyển Email Và Phân Loại

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

=INFERROR (HLOOKUP (B4, B1:I2, 2, FALSE), “Xin lỗi, không kiế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 cực hiếm theo hàng từ một trang tính hoặc một bảng tính khác nghĩa là chúng ta phải cung ứng các tham chiếu nước ngoài tuyến cho hàm HLOOKUP

Để lấy cực hiếm khớp xuất phát từ 1 trang tính khác, chúng ta phải chứng thực 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 bí quyết hoặc kí tự không phải chữ cái, đặt nó trong lốt ngoặc đối chọi 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 muốn lấy giá chỉ trị từ 1 bảng tính đóng, bạn phải chỉ đi ra đường dẫn cho bảng tính:

=HLOOKUP (B$1, ‘D:ReportsĐườ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, chúng ta 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 đường vào hàm của người sử dụng một cách tự động hóa

HLOOKUP với công dụng khớp 1 phần (sử dụng kí tự đại diện)

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

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

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

Ví dụ, bạn có một list tên các người sử dụng ở sản phẩm 1 và số order ở mặt 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 kia nhưng các bạn không thể lưu giữ tên đúng đắn vị khách mà chỉ ghi nhớ nó bước đầu với chữ “La”.

Cho rằng dữ liệu của người sử dụng ở ô B1:I2 (table_array) với thứ tự những số ở hàng 2 (row_index_num), cách làm như sau:

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

Để cách làm 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 ô rứa thể, lấy ví dụ như B4, và gắn vào ô đó 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 đưa tác dụng đúng, range_lookup đề nghị được đặt chế độ FALSENếu table_array đựng được nhiều hơn một quý giá khớp với kí từ bỏ đại diện, giá trị trước tiên tìm được sẽ được trả về

Ô tham chiếu tuyệt vời và kha khá trong HLookup

Nếu bạn đang viết hàm Hlookup cho một ô, chúng ta có thể không cần thân thiện nên thực hiện ô tham chiếu hoàn hảo nhất hay tương đối vì cả hai phần đa phù hợp. Mặc dù nhiên, khi xào luộc một bí quyết cho các ô tham chiếu, bạn cần phân biệt được hai các 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 “$”, lấy ví dụ như $B$1:$I$2tham chiếu lookup_value là kha khá hoặc láo lếu hợp dựa vào logic sale của bạn

Để dễ dàng nắm bắt hơn, chúng ta 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 phương pháp trên, chúng ta sử dụng tham chiếu hoàn hảo và tuyệt vời nhất ($B$1:$I$2) vào table_array vị nó cần phải giữ không đổi khi công thức được coppy từ các ô khác

Đối cùng với lookup_value (B$1), chúng ta 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ì những giá trị tìm kiếm (tên hành tinh) nằm trên và một hàng (hàng 1) cơ mà ở những cột khác nhau (từ B đến I) và cột tham chiếu nên biến hóa dựa bên 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ư các bạn đã biết, hàm HLOOKUP gồm một vài tinh giảm với nhược điểm lớn nhất là không thể thể tìm các giá trị ở ngoài hàng đầu và buộc phải xếp các giá trị theo sản phẩm công nghệ tự khi tìm kết quả khớp tương đối. May mắn thay, sự kết hợp giữa hàm INDEX cùng MATCH sẽ khắc phục được gần như điểm này:

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

Giả sử giá trị tìm tìm ở ô B7, ai đang tìm một tác dụng khớp ở hàng 2 (B2:I2), và hy vọng 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ả hai trường phù hợp INDEX MATCH đều hoạt động tốt.

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

Như đang đề cập thời gian đầu, hàm HLOOKUP không thể riêng biệt chữ thường và in hoa. Trong tương đối nhiều trường vừa lòng khi dạng chữ của kí tự quan trọng, bạn cũng có thể dùng hàm EXACT nhằm so sánh các ô với đặ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 kiếm tìm kiếm, giá trị tìm kiếm), 0))

Giả sử quý giá tìm kiếm của khách hàng ở ô 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à phương pháp mảng cho nên vì thế bạn cần ấn tổng hợp Ctrl + Shift + Enter sau khoản thời gian 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à một trong những hàm phức tạp bởi vì nó có rất nhiều thành phần nằm trong công thức khiến cho các lỗi #N/A, #VALUE hoặc #REF liên tiếp xảy ra. Trường hợp hàm HLOOKUP của khách hàng không hoạt động được, nguyên nhân hoàn toàn có thể là một trong số lỗi bên dưới đây

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

Kể cả khi bạn quên tất cả các cụ thể về tìm kiếm theo hàng ngang vào Excel, luôn 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 nằm ở các hàng khác, lỗi N/A sẽ được trả lại. Để hạn chế và khắc phục lỗi này, bạn nên thực hiện công thức INDEX/MATCH

công dụng khớp kha khá và đúng chuẩn

Khi bạn đang tìm giá trị trong Excel, theo mặt hàng ngang giỏi dọc, phần nhiều các trường phù hợp là bạn đang tìm kiếm một giá chỉ trị ví dụ nên cần kết quả khớp thiết yếu xác. Lúc tìm kiếm với hiệu quả khớp kha khá (range_lookup được để TRUE hoặc quăng quật trống), các bạn nên ghi nhớ xếp các giá trị trong hàng theo vật dụng tự tăng dần.

Bảng tham chiếu thay đổi khi xào nấu công thức

Khi sử dụng nhiều HLOOKUP để đưa thông tin về hàng cất giá trị tìm kiếm, chúng ta phải khoá tham chiếu table_array

cung cấp hoặc xoá bớt hàng mới

Để hiểu vì sao thêm một mặt hàng mới hoàn toàn có thể làm hỏng cách làm Hlookup, bạn cần hiểu bí quyết HLOOKUP lấy tin tức về giá trị tìm kiếm kia là dựa vào chỉ số của hàng chúng ta chỉ định

Giả sử bạn có nhu cầu lấy số liệu doanh số dựa trên ID sản phẩm. Số đông số liệu này ở sản phẩm 4 nên chúng ta đánh 4 vào tham số row_index_num. Nhưng lúc một hàng new được thêm vào, nó biến chuyển hàng thiết bị 5 với Hlookup không thể chuyển động được. Vụ việc xảy ra tương tự như như khi bạn xoá sút một mặt hàng trong bảng.

Cách xử lý là khoá bảng nhằm tránh việc người sử dụng thêm một hàng mới vào hoặc áp dụng công thức INDEX/MATCH vậy cho Hlookup. Trong Index/Match, chúng ta phải chỉ rõ những hàng để tìm quý giá và trả cực hiếm về như mảng tham chiếu và Excel hoàn toàn 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 nên phải băn khoăn lo lắng về sự việc thêm tốt xoá giảm hàng như khi sử dụng công thức Hlookup.

trùng lặp trong bảng

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

Nếu 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à tương xứng với các bạn nhất:

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

Khi công thức Hlookup của chúng ta hoàn toàn đúng nhưng mà lỗi #N/A vẫn bị trả về, hãy kiểm soát lại bảng và quý hiếm tìm kiếm xem tất cả kí từ bỏ trắng làm sao không. Bạn cũng có thể nhanh chóng đào thải 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 đặt dạng văn bản

Chuỗi văn phiên bản giống như số là một trong khó khăn khác khi thực hiện công thức Excel. Mô tả chi tiết của vấn đề này đang được lý giải trong lý do các công thức Excel xong hoạt đụng

giá trị tìm kiếm vượt vượt 255 kí tự

Tất cả các hàm tra cứu kiếm trong Excel chỉ hoạt động khi quý giá tìm kiếm bên dưới 255 kí tự. Giá trị tìm kiếm dài ra hơn sẽ trả lại công dụng là lỗi #VALUE!. Bởi hàm INDEX/MATCH giới hạn max số lượng kí từ bỏ 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 hề thiếu tới bảng tính không được nêu rõ

Nếu bạn thực hiện hàm Hlookup xuất phát 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ó.

sai tham số

Các chúng ta nên nhớ rằng hàm Hlookup là 1 trong hàm tinh vi nên thật cẩn trọng khi sử dụng. Dưới đó là một vài ba lỗi thông dụng khi nhập không nên tham số

Nếu row_index_num nhỏ hơn 1, lỗi #VALUE! sẽ được trả lại ví như row_index_num lớn hơn số sản phẩm trong bảng tìm kiếm, lỗi #REF! sẽ tiến hành trả lại nếu khách hàng tìm tìm với tác dụng khớp tương đối và lookup_value nhỏ tuổi 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ẽ tiến hành trả lại

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