Kết hợp hàm vlookup và hlookup

     

Như chúng ta đã biết Microsoft tất cả 3 hàm dùng để tìm giá bán trị, LOOKUP, VÀ HLOOKUP, và người tiêu dùng cảm thấy bối rối 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 đặc điểm của hàm HLOOKUP vào Excel và đưa một vài ví dụ như minh hoạ giúp đỡ bạn sử dụng Hlookup một cách công dụng nhất.

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


HÀM HLOOKUP trong EXCEL LÀ GÌ?

Hàm HLOOKUP được áp dụng để 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 quý giá khác trong cùng cột từ sản phẩm mà chúng ta chỉ định.

Hàm HLOOKUP áp 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ị phải tìm. Hoàn toàn có thể là ô tham chiếu, một quý hiếm hoặc chuỗi văn bản.


*

*

Table_array (bắt buộc): bảng search kiếm giá chỉ trị tất cả hai hàng dữ liệu trở lên. Hoàn toàn có thể là mảng thường, chọn cái tên hoặc bảng Excel. Những giá trị tìm kiếm bắt buộc đượ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 địa điểm mà cực hiếm được trả về. Ví dụ, để trả lại giá trị trùng khớp tự hàng thiết bị 2, để row_index_num bởi 2.

Range_lookup (tuỳ chọn): một giá chỉ trị súc tích (Boolean) cho biết thêm HLOOKUP cần được tìm hiệu quả khớp đúng chuẩn hay tương đối.

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

Nếu FALSE, chỉ kết quả khớp đúng đắn được trả về. Nếu như 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 vẫn trả về lỗi #N/A.

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

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

Để hiểu giải pháp hoạt động, họ hãy cùng xem một ví dụ đối kháng giản. Giả sử các bạn có một bảng với thông tin cơ bạn dạng về các hành tinh của hệ phương diện trời. Bạn muốn hàm trả về đường kính của hành tinh có tên trong ô B5.

Trong bí quyết Hlookup, họ sẽ sử dụng các 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 chứa giá trị đề nghị 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 trước tiên của bảng không được bố trí theo máy tự trường đoản cú A cho Z, nên họ chỉ hoàn toàn có thể tìm giá bán trị đúng đắn ở trong lấy ví dụ này

Bây tiếng xếp những 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 trong EXCEL 

Bất cứ bao giờ bạn tìm giá trị trong hàng, hãy nhớ đều đ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ư khách hàng cần search ở các vị trí khác, hãy áp dụng công thức Index hoặc Match HLOOKUP trong Excel không rõ ràng được chữ hoa cùng thường ví như range_lookup được để 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 đề xuất được xếp theo máy tự tăng ngày một nhiều (A-Z) tự trái sang phải.

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

Như các bạn đã biết, cả nhị hàm HLOOKUP VÀ VLOOKUP dùng làm tìm tìm một giá chỉ trị. Nhưng lại chúng khác nhau trong cách hoạt động. Chúng ta dễ dàng nhận ra tên của hai hàm khác biệt ở vần âm đầu – “H” là ngang (horizontal) và “V” là dọc (vertical)

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

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

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

Khi nào sử dụng VLOOKUP, bao giờ sử dụng HLOOKUP? Hẳn các bạn rất vướng mắc với thắc mắc này. Chúng ta có 1 tuyệt kỹ để 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 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 hàng với công dụng khớp tương đối và bao gồm xác

Hàm HLOOKUP trong Excel có thể tìm hiệu quả khớp chính xác và không chính xác 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: tác dụng 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, cách làm Hlookup luôn luôn tìm một công dụng khớp đúng đắn đầu tiên. Đặt thông số là FALSE cho phép hàm trả về tác dụng khớp kha khá (giá trị gần nhất nhở hơn giá trị cần tìm kiếm) nếu kết quả khớp đúng đắn không tra cứu thấy; TRUE hoặc bị bỏ trống đã trả về lỗi #N/A

Chúng ta cùng xem lấy 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 những hành tinh ở mặt hàng 2 (B2:I2) và nhiệt độ của chúng ở sản phẩm 1 (B1:I1). Bạn có nhu cầu tìm địa cầu có ánh sáng bằng ánh sáng ở ô B4.

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

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

=HLOOKUP (B4, B1:I2, 2)

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

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

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

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

Xem thêm: Tổng Hợp Tìm M Để Hàm Số Liên Tục Trên R Ên R, Hàm Số Liên Tục Trên R Khi Nào

Tip Để giúp người dùng không cảm thấy hoảng loạn khi thấy được 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 công ty

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

Cách sử dụng hàm Hlookup từ bỏ worksheet hoặc workbook khác

Nhìn chung, tìm cực hiếm theo hàng xuất phát từ 1 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 ứng 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 từ một trang tính khác, các bạn phải chứng 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 đựng dấu phương pháp hoặc kí tự không phải chữ cái, đặt nó trong dấu 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ỏ bảng tính khác, viế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ừ 1 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ì chưng đánh thương hiệu bảng tính cùng trang tính bằng tay, bạn có thể chọn các ô trong trang tính khác với Excel vẫn thêm tham chiếu ngoại tuyến đường vào hàm của doanh nghiệp một cách auto

HLOOKUP với công dụng khớp một phần (sử dụng kí trường đoản cú đại diện)

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

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

Các kí tự thay mặt rất có ích 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 ba đoạn văn bạn dạng nằm trong nội dung của ô tìm kiếm kiếm

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

Cho rằng dữ liệu của khách hàng ở ô B1:I2 (table_array) và thứ tự những số ở mặt 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, chúng ta cũng có thể đánh giá trị tìm kiếm vào một trong những ô vắt thể, lấy ví dụ như B4, cùng 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 đưa công dụng đúng, range_lookup yêu cầu được đặt chế độ FALSENếu table_array chứa nhiều hơn một quý giá khớp với kí từ đại diện, giá chỉ trị đầu 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 bạn đang viết hàm Hlookup cho một ô, bạn có thể không cần đon đả nên áp dụng ô tham chiếu hoàn hảo hay tương đối vì cả hai hồ hết phù hợp. Mặc dù nhiên, khi sao chép một công thức cho nhiều ô tham chiếu, bạn phải 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 “$”, ví dụ $B$1:$I$2tham chiếu lookup_value là tương đối hoặc láo lếu hợp dựa trên logic marketing của bạn

Để dễ hiểu hơn, chúng ta cùng coi kĩ hàm Hlookup khi lấy dữ liệu xuất phát điểm từ một trang tính khác

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

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

Đối cùng với lookup_value (B$1), bọn họ sử dụng tham chiếu láo hợp, cột kha khá và hàng hoàn hảo 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) nhưng lại ở những cột khác biệt (từ B đến 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ông thức đượ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 tất cả một vài hạn chế với nhược điểm lớn số 1 là quan yếu thể tìm các giá trị nằm ngoài hàng đầu và cần xếp các giá trị theo trang bị tự lúc tìm tác dụng khớp tương đối. May mắn thay, sự kết hợp giữa hàm INDEX và MATCH vẫn khắc phục được phần nhiều điểm này:

INDEX (nơi cất giá trị về, MATCH (giá trị tìm kiếm, chỗ tìm giá trị, 0))

Giả sử cực hiếm tìm tìm ở ô B7, nhiều người đang tìm một hiệu quả khớp ở mặt hàng 2 (B2:I2), và mong mỏi 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ó thể thấy trong cả hai trường phù hợp INDEX MATCH đều chuyển động tốt.

Cách sử dụng H-lookup tách biệt dạng chữ vào Excel

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

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

Giả sử cực hiếm tìm kiếm của chúng ta ở ô B4, mảng search kiếm là B1:I1, mảng quý hiếm trả về là B2:I2, công thức 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 đề nghị ấn tổ hợp Ctrl + Shift + Enter sau khoản thời gian viết công thức

10 LÝ vị 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ó không ít thành phía bên trong công thức khiến các lỗi #N/A, #VALUE hoặc #REF tiếp tục xảy ra. Giả dụ hàm HLOOKUP của công ty không hoạt động được, nguyên nhân có thể là một trong các lỗi bên dưới đây

HLOOKUP thiết yếu 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 cụ thể về tìm kiếm theo hàng ngang vào Excel, luôn 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ị tìm 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, chúng ta nên áp dụng công thức INDEX/MATCH

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

Khi bạn đang tìm quý giá trong Excel, theo sản phẩm ngang tốt dọc, đa phần các trường hợp là nhiều người đang tìm tìm một giá trị ví dụ nên cần công dụng khớp chủ yếu xác. Khi tìm kiếm với hiệu quả khớp tương đối (range_lookup được để TRUE hoặc quăng quật trống), chúng ta nên lưu giữ xếp các giá trị trong sản phẩm theo thứ tự tăng dần.

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

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

thêm vào hoặc xoá giảm hàng mới

Để hiểu lý do thêm một hàng mới hoàn toàn có thể làm hỏng công thức Hlookup, bạn phải hiểu giải pháp HLOOKUP lấy thông tin về quý giá 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 lợi nhuận dựa bên trên ID sản phẩm. Phần lớn số liệu này ở hàng 4 nên các bạn đánh 4 vào thông số row_index_num. Nhưng lúc một hàng bắt đầu được thêm vào, nó trở nên hàng trang bị 5 với Hlookup ko thể vận động được. Vụ việc xảy ra tựa như như khi bạn xoá sút một sản phẩm trong bảng.

Cách giải quyết là khoá bảng nhằm 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 cố kỉnh cho Hlookup. Vào Index/Match, chúng ta phải chỉ rõ các hàng để tìm quý giá và trả quý 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. Nhờ vào vậy, các bạn không nên phải lo lắng về sự việc thêm giỏi xoá giảm hàng như khi áp dụng công thức Hlookup.

giống nhau trong bảng

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

Nếu có một vài quý giá giống nhau trong bảng, chọn 1 trong các phương án mà cân xứng với bạn nhất:

Loại quăng quật trùng lặp bằng phương pháp dùng cơ chế của Excel Nếu bạn muốn trùng lặp được giữ nguyên trong dữ liệu, tạo 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ách làm mảng để rút các giá trị trùng ngoài mảng tra cứu kiếm khoảng chừng trắng

Khi phương pháp Hlookup của chúng ta hoàn toàn đúng nhưng lại lỗi #N/A vẫn bị trả về, hãy kiểm soát lại bảng và quý giá 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 đào thải các khoảng tầm trống bằng cách sử dụng hàm TRIM vào Excel.

Xem thêm: Trò Chơi Náo Loạn Thành Phố Về Máy, Trò Chơi Náo Loạn Thành Phố

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à một khó khăn không giống khi áp dụng công thức Excel. Mô tả cụ thể của vấn đề này vẫn được phân tích và lý giải trong vì sao các bí quyết Excel ngừng hoạt hễ

quý hiếm tìm tìm vượt vượt 255 kí tự

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

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

Nếu bạn thực hiện hàm Hlookup xuất phát điểm từ một bảng tính khác, phải nhớ rằng bạn cần phải cung cấp rất đầy đủ đường mang tới nó.

sai 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 thông dụng khi nhập không đúng tham số

Nếu row_index_num nhỏ tuổi hơn 1, lỗi #VALUE! sẽ được 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 khách hàng tìm kiếm với tác dụng khớp kha khá và lookup_value nhỏ tuổi hơn giá trị nhỏ dại nhất trong bậc nhất 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 trong công việc, chúng ta không chỉ nắm rõ được các hàm nhưng còn bắt buộc sử dụng xuất sắc cả các công rứa của Excel. Phần đa hàm cải thiện giúp áp dụng tốt vào quá trình như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những pháp luật thường áp dụng là Data validation, Conditional formatting, Pivot table…