CÁCH SỬ DỤNG HLOOKUP TRONG EXCEL

Nlỗi chúng ta đang biết Microsoft tất cả 3 hàm dùng làm search quý giá, LOOKUPhường, VLOOKUP.. VÀ HLOOKUPhường, và người dùng Cảm Xúc hoảng loạn khi sử dụng 3 hàm này độc nhất vô nhị. Trong bài viết này, Học Excel Online sẽ tập trung vào những Đặc điểm của hàm HLOOKUPhường. trong Excel với gửi một vài ba ví dụ minc hoạ giúp bạn thực hiện Hlookup một cách tác dụng tốt nhất.

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

HÀM HLOOKUP. TRONG EXCEL LÀ GÌ?

Hàm HLOOKUP được áp dụng để search một giá trị khăng khăng vào mặt hàng trên thuộc của bảng với trả lại một quý giá khác vào thuộc cột từ bỏ hàng cơ mà các bạn hướng dẫn và chỉ định.

Hàm HLOOKUP.. áp dụng được đến 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ỏng sau

HLOOKUPhường (lookup_value, table array, row_index_num, )

Với

Lookup_value (bắt buộc): quý giá buộc phải tra cứu. cũng có thể là ô tham mê chiếu, một quý hiếm hoặc chuỗi văn uống bản.

Table_array (bắt buộc): bảng search kiếm cực hiếm gồm nhì sản phẩm tài liệu trlàm việc lên. cũng có thể là mảng hay, được đặt tên hoặc bảng Excel. Các cực hiếm tìm kiếm bắt buộc được đặt ở bậc nhất tiên của table_array

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

Range_lookup (tuỳ chọn): một cực hiếm xúc tích và ngắn gọn (Boolean) cho biết HLOOKUP.. rất cần được tra cứu kết quả khớp chính xác giỏi kha khá.

Nếu TRUE hoặc bỏ qua mất, công dụng khớp tương đối được trả về. Nghĩa là ví như hiệu quả khớp chính xác ko được tìm kiếm thấy, hàm Hlookup của bạn sẽ trả về quý giá lớn số 1 tiếp nối nhỏ dại rộng look_up value.

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

Để dễ hiểu hơn, chúng ta có thể dịch cú pháp HLOOKUP của Excel sang tiếng Việt nhỏng sau

HLOOKUP. (tra cứu tìm quý hiếm này, vào bảng này, trả lại quý giá từ bỏ mặt hàng này, )

Để hiểu biện pháp vận động, chúng ta hãy thuộc xem một ví dụ dễ dàng. Giả sử chúng ta bao gồm một bảng cùng với đọc tin cơ phiên bản về các trái đất của hệ Mặt ttránh. Bạn ước ao hàm trả về đường kính của hành tinh mang tên vào ô B5.

Trong bí quyết Hlookup, bọn họ đã áp dụng các tmê mẩn số sau

Lookup_value là B5, ô chứa thương hiệu của trái đất bạn muốn tìm

Table_arrray là B2:I3, bảng đựng cực hiếm đề nghị tìm

Row_index_num là 2 vày Đường kính làm việc mặt hàng thứ 2 trong bảng

Range_lookup  là FALSE bởi sản phẩm trước tiên của bảng không được sắp xếp theo lắp thêm từ bỏ tự A mang lại Z, đề nghị họ chỉ rất có thể tra cứu quý giá đúng mực sinh sống trong ví dụ này

Bây giờ xếp những tmê mẩn số vào cùng bọn họ bao gồm bí quyết nlỗi sau:

=HLOOKUP. (40, A2:B15, 2)

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

Bất cứ đọng bao giờ bạn tìm kiếm cực hiếm trong sản phẩm, hãy nhớ phần đông điều sau:

Hàm HLOOKUP.. chỉ hoàn toàn có thể search sống sản phẩm trên cùng của table_array. Nếu bạn phải tìm kiếm nghỉ ngơi hồ hết vị trí không giống, hãy sử dụng công thức Index hoặc Match HLOOKUPhường trong Excel không phân minh được chữ hoa cùng thường Nếu range_lookup được đặt là TRUE hoặc bỏ trống (công dụng khớp tương đối), những giá trị nghỉ ngơi bậc nhất của table_array đề xuất được xếp theo vật dụng tự tăng mạnh (A-Z) từ trái lịch sự nên.

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

Như chúng ta sẽ biết, cả nhì hàm HLOOKUPhường VÀ VLOOKUP dùng để search tìm một quý giá. Nhưng bọn chúng khác nhau trong bí quyết vận động. Chúng ta dễ dãi phân biệt thương hiệu của nhị hàm không giống nhau sống vần âm đầu – “H” là ngang (horizontal) với “V” là dọc (vertical)

Do kia, chúng ta sử dụng hàm VLOOKUPhường nhằm kiếm tìm giá trị theo cột làm việc phía bên trái của tài liệu bạn muốn tra cứu. Hàm HLOOKUPhường dùng để làm tra cứu giá trị theo hàng ngang. Nó search giá trị sinh sống bậc nhất tiên của bảng với trả lại quý giá nghỉ ngơi sản phẩm được chỉ định và hướng dẫn ngơi nghỉ vào cùng một cột.

Hình tiếp sau đây miêu tả sự khác nhau giữa nhì bí quyết Vlookup cùng Hlookup

ỨNG DỤNG CỦA HLOOKUP TRONG EXCEL

Tìm cực hiếm theo hàng cùng với kết quả khớp kha khá với chính xác

Hàm HLOOKUPhường trong Excel có thể tra cứu tác dụng khớp đúng chuẩn cùng ko đúng đắn dựa vào cực hiếm của tmê mẩn số range_lookup

TRUE hoặc vứt trống: hiệu quả khớp tương đốiFALSE: kết quả khớp thiết yếu xác

Nên hãy nhớ là tuy nhiên chúng ta hay nói là kết quả khớp kha khá, phương pháp Hlookup luôn search một công dụng khớp đúng mực thứ nhất. Đặt tyêu thích số là FALSE có thể chấp nhận được hàm trả về hiệu quả khớp tương đối (quý hiếm gần nhất nhngơi nghỉ rộng giá trị cần search kiếm) nếu như kết quả khớp chính xác không tìm thấy; TRUE hoặc bị bỏ trống đang trả về lỗi #N/A

Chúng ta cùng xem ví dụ minc hoạ dưới đây

HLOOKUPhường. với công dụng khớp tương đối

Giả sử các bạn có một danh sách các trái đất ở hàng 2 (B2:I2) và ánh sáng của chúng ngơi nghỉ sản phẩm 1 (B1:I1). Quý Khách mong muốn kiếm tìm hành tinh có ánh nắng mặt trời bởi ánh sáng nghỉ ngơi ô B4.

Trong trường hợp bạn lừng khừng đúng chuẩn nhiệt độ yêu cầu tìm kiếm, chúng ta có thể thi công hàm Hlookup để lấy ra hiệu quả khớp gần nhất ví như quý hiếm đúng mực ko được kiếm tìm thấy.

lấy ví dụ như, để hiểu toàn cầu như thế nào tất cả ánh nắng mặt trời vừa phải vào khoảng -340°F, bạn thực hiện cách làm Hlookup như sau (range_lookup được đặt là TRUE vào ngôi trường thích hợp này):

=HLOOKUP. (B4, B1:I2, 2)

Hãy đừng quên hiệu quả khớp tương đối kinh nghiệm bố trí những giá trị sinh sống hàng đầu tiên từ nhỏ dại duy nhất mang lại lớn nhất hoặc tự A cho Z, còn nếu không hàm Hlookup đang chỉ dẫn hiệu quả không nên.

Trong hình chụp dưới đây, hàm trả về trái đất Hải Vương, một Một trong những địa cầu rét mướt độc nhất vô nhị vào hệ Mặt trời luôn luôn duy trì ánh sáng trung bình vào lúc -346 độ F.

Hlookup với kết quả khớp bao gồm xác

Nếu bạn biết chính xác quý hiếm cần tìm kiếm, bạn có thể đặt tmê mẩn số sau cùng của hàm Hlookup là FALSE:

=HLOOKUPhường (B4, B1:I2, 2, FALSE)

Một mặt, công dụng khớp tường dối dễ dãi áp dụng hơn vị nó ko yêu cầu sắp xếp tài liệu ngơi nghỉ số 1 tiên. Mặt khác, trường hợp tác dụng khớp đúng mực không được tra cứu thấy, lỗi #N/A sẽ được trả về.

Xem thêm: Hướng Dẫn Xem Camera Quan Sát Trên Máy Tính Camera Giám Sát, Hướng Dẫn Xem Lại Camera Trên Máy Tính Bằng Ivms

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

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

Cách dùng hàm Hlookup trường đoản cú worksheet hoặc workbook khác

Nhìn thông thường, tìm kiếm quý giá theo hàng từ một trang tính hoặc một bảng tính khác tức thị chúng ta đề xuất cung cấp các tsay đắm chiếu ngoại tuyến mang đến hàm HLOOKUP.

Để đem quý hiếm khớp từ 1 trang tính khác, chúng ta đề nghị chứng tỏ tên của trang tính đặt trước lốt “!”. Ví dụ:

=HLOOKUPhường (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í trường đoản cú không hẳn vần âm, đặt nó vào dấu ngoặc đối kháng nlỗi sau:

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

lúc tham mê 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 mang cực hiếm xuất phát điểm từ 1 bảng tính đóng góp, bạn phải chỉ đi xuống đường dẫn mang đến bảng tính:

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

Tip: Txuất xắc vày đánh tên bảng tính và trang tính bằng tay, chúng ta cũng có thể chọn các ô vào trang tính không giống và Excel đã thêm tyêu thích chiếu ngoại con đường vào hàm của người tiêu dùng một bí quyết auto

HLOOKUP với công dụng khớp một phần (thực hiện kí từ đại diện)

Nlỗi trong trường hòa hợp của Vlookup, hàm HLOOKUP.. được cho phép sử dụng những kí trường đoản cú thay mặt đại diện sau cho ttê mê số lookup_value:

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

Các kí tự thay mặt siêu hữu ích Khi bạn có nhu cầu lấy ban bố xuất phát từ một đại lý tài liệu dựa trên một vài ba đoạn vnạp năng lượng bản phía bên trong văn bản của ô tìm kiếm kiếm

lấy ví dụ như, bạn có một danh sách tên những quý khách hàng sống sản phẩm 1 cùng số order sống hàng 2. quý khách hàng muốn tra cứu số order của một người tiêu dùng nhất mực nào kia nhưng chúng ta chẳng thể lưu giữ tên chính xác vị khách nhưng chỉ ghi nhớ nó ban đầu cùng với chữ “La”.

Cho rằng tài liệu của công ty sinh hoạt ô B1:I2 (table_array) cùng sản phẩm tự những số ở hàng 2 (row_index_num), bí quyết nlỗi sau:

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

Để cách làm linc hoạt rộng, chúng ta có thể review trị search tìm vào trong 1 ô cụ thể, ví dụ B4, và gắn vào ô đó kí tự đại diện nlỗi sau:

=HLOOKUPhường (B4&“*”, B1:I2, 2, FALSE)

Lưu ý

Để hàm HLOOKUPhường với kí từ thay mặt chuyển hiệu quả đúng, range_lookup yêu cầu được đặt cơ chế FALSENếu table_array đựng nhiều rộng một cực hiếm khớp với kí tự thay mặt, giá trị đầu tiên kiếm được sẽ được trả về

Ô tsay đắm chiếu hoàn hảo và tương đối vào HLookup

Nếu ai đang viết hàm Hlookup cho một ô, bạn cũng có thể không phải quan tâm bắt buộc sử dụng ô tmê mệt chiếu tuyệt đối hoàn hảo tốt tương đối vì chưng cả nhì số đông tương xứng. Tuy nhiên, lúc coppy một công thức đến những ô tmê mệt chiếu, bạn phải rành mạch được nhì loại này

table_array luôn luôn được đặt cố định bởi các ô tmê mệt chiếu hay đối với kí hiệu “$”, ví dụ $B$1:$I$2tđắm say chiếu lookup_value là tương đối hoặc các thành phần hỗn hợp dựa trên lô ghích kinh doanh của bạn

Để dễ nắm bắt rộng, chúng ta thuộc coi kĩ hàm Hlookup lúc đem tài liệu xuất phát từ một trang tính khác

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

Trong phương pháp bên trên, chúng ta áp dụng tđắm đuối chiếu tuyệt đối hoàn hảo ($B$1:$I$2) vào table_array bởi vì nó rất cần phải giữ lại không thay đổi khi công thức được sao chép từ bỏ các ô khác

Đối với lookup_value (B$1), họ áp dụng tham chiếu tất cả hổn hợp, cột tương đối cùng sản phẩm tuyệt vời do những giá trị tìm kiếm tìm (tên hành tinh) nằm trên cùng một mặt hàng (sản phẩm 1) nhưng sống các cột khác biệt (trường đoản cú B mang đến I) cùng cột tsi mê chiếu nên chuyển đổi dựa trên địa chỉ kha khá của ô đựng cách làm được xào luộc.

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

Nhỏng chúng ta sẽ biết, hàm HLOOKUPhường bao gồm một vài ba hạn chế cùng với yếu điểm lớn số 1 là không thể thể search các giá trị nằm quanh đó bậc nhất với cần xếp các quý giá theo máy tự lúc kiếm tìm kết quả khớp kha khá. May mắn ráng, sự phối kết hợp thân hàm INDEX với MATCH sẽ hạn chế và khắc phục được các điểm này:

INDEX (nơi cất giá trị về, MATCH (quý giá kiếm tìm tìm, vị trí kiếm tìm cực hiếm, 0))

Giả sử quý hiếm search kiếm nghỉ ngơi ô B7, ai đang kiếm tìm một công dụng khớp nghỉ ngơi mặt hàng 2 (B2:I2), và ao ước cực hiếm trả về trường đoản cú hàng 1 (B1:I1), phương pháp như sau

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

Trong hình chụp sau đây, bạn có thể thấy vào cả hai ngôi trường thích hợp INDEX MATCH đều vận động xuất sắc.

Cách sử dụng H-lookup rõ ràng dạng văn bản trong Excel

Nlỗi sẽ nói thuở đầu, hàm HLOOKUP. cần yếu rõ ràng chữ hay với in hoa. Trong nhiều ngôi trường đúng theo Lúc dạng chữ của kí tự quan trọng đặc biệt, bạn cũng có thể cần sử dụng hàm EXACT để đối chiếu những ô và đặt phía bên trong hàm INDEX MATCH nlỗi vào ví dụ trước:

INDEX (sản phẩm đựng cực hiếm trả về, MATCH (TRUE, EXACT (sản phẩm search tìm, quý giá tra cứu kiếm), 0))

Giả sử cực hiếm tìm tìm của doanh nghiệp sống ô B4, mảng search kiếm là B1:I1, mảng quý giá trả về là B2:I2, công thức tất cả dạng nlỗi 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ế các bạn nên ấn tổng hợp Ctrl + Shift + Enter sau khi viết công thức

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

Hàm Hlookup là 1 trong những hàm tinh vi bởi vì nó có rất nhiều thành phần nằm trong cách làm khiến cho các lỗi #N/A, #VALUE hoặc #REF tiếp tục xẩy ra. Nếu hàm HLOOKUPhường của người sử dụng ko chuyển động được, nguim nhân có thể là một trong trong số lỗi bên dưới đây

HLOOKUPhường cần thiết kiếm tìm tìm các hàng phía trên

Kể cả khi chúng ta quên tất cả những chi tiết về tra cứu kiếm theo mặt hàng ngang trong Excel, luôn luôn hãy nhờ rằng Hlookup chỉ có thể tìm nghỉ ngơi hàng đầu tiên vào bảng. Nếu các quý hiếm search kiếm nằm ở những sản phẩm không giống, 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

Kết quả khớp kha khá cùng đúng mực

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

Bảng tđê mê chiếu đổi khác Khi xào nấu công thức

Khi sử dụng những HLOOKUPhường. để lấy công bố về hàng đựng giá trị tìm kiếm, các bạn đề xuất khoá tmê man chiếu table_array

Thêm vào hoặc xoá giảm sản phẩm mới

Để hiểu tại sao thêm một mặt hàng mới hoàn toàn có thể có tác dụng lỗi cách làm Hlookup, bạn cần hiểu bí quyết HLOOKUPhường. lấy báo cáo về cực hiếm kiếm tìm tìm sẽ là dựa vào chỉ số của mặt hàng bạn chỉ định

Giả sử bạn muốn mang số liệu doanh thu dựa vào ID sản phẩm. Những số liệu này sinh hoạt hàng 4 buộc phải bạn tiến công 4 vào tsay mê số row_index_num. Nhưng lúc 1 mặt hàng new được sản xuất, nó trở thành sản phẩm sản phẩm công nghệ 5 với Hlookup quan trọng vận động được. Vấn đề xảy ra tương tự như nlỗi khi bạn xoá giảm một sản phẩm vào bảng.

Cách xử lý là khoá bảng nhằm tránh bài toán người sử dụng thêm 1 mặt hàng mới vào hoặc thực hiện phương pháp INDEX/MATCH núm mang lại Hlookup. Trong Index/Match, chúng ta cần chỉ rõ những sản phẩm để tìm quý giá cùng trả cực hiếm về nhỏng mảng tham chiếu cùng Excel có thể tự điều chỉnh các tđắm đuối chiếu này. Nhờ vậy, bạn không nhất thiết phải băn khoăn lo lắng về sự việc thêm tốt xoá sút mặt hàng nhỏng lúc áp dụng công thức Hlookup.

Trùng lặp vào bảng

Hàm HLOOKUPhường vào Excel chỉ có thể trả về một cực hiếm là quý hiếm thứ nhất trong bảng khớp với giá trị kiếm tìm kiếm.

Nếu có một vài cực hiếm tương tự nhau trong bảng, chọn một trong những chiến thuật nhưng phù hợp với bạn nhất:

Loại vứt trùng lặp bằng cách sử dụng công cụ của Excel Nếu bạn muốn trùng lặp được giữ nguyên vào dữ liệu, sản xuất PivotTable để team cùng lọc những tài liệu theo hưởng thụ của chính mình Sử dụng phương pháp mảng nhằm rút những giá trị trùng khỏi mảng tìm tìm Khoảng trắng

lúc công thức Hlookup của người sử dụng hoàn toàn đúng cơ mà lỗi #N/A vẫn bị trả về, hãy kiểm soát lại bảng với cực hiếm search kiếm coi gồm kí trường đoản cú Trắng nào không. quý khách có thể lập cập vứt bỏ những khoảng trống bằng phương pháp sử dụng hàm TRIM vào Excel.

Số được setup dạng văn bản

Chuỗi văn uống phiên bản giống hệt như số là một trong những trở ngại khác khi sử dụng phương pháp Excel. Mô tả chi tiết của sự việc này sẽ được lý giải trong Tại sao những phương pháp Excel xong vận động

Giá trị tìm tìm vượt quá 255 kí từ bỏ

Tất cả những hàm tìm kiếm kiếm trong Excel chỉ chuyển động Lúc cực hiếm tra cứu kiếm bên dưới 255 kí từ bỏ. Giá trị kiếm tìm kiếm dài ra hơn nữa đã trả lại hiệu quả là lỗi #VALUE!. Vì hàm INDEX/MATCH không giới hạn số lượng kí từ buộc phải chúng ta có thể khắc chế lỗi này lúc sử dụng INDEX/MATCH

Đường dẫn vừa đủ cho tới bảng tính ko được nêu rõ

Nếu chúng ta triển khai hàm Hlookup xuất phát điểm từ một bảng tính khác, buộc phải đừng quên bạn phải hỗ trợ tương đối đầy đủ băng thông cho tới nó.

Sai tmê mệt số

Các chúng ta nên hãy nhớ là hàm Hlookup là một hàm phức tạp yêu cầu thật cảnh giác Lúc áp dụng. Dưới đây là một vài ba lỗi phổ cập Khi nhập không đúng tđê mê số

Nếu row_index_num nhỏ rộng 1, lỗi #VALUE! sẽ tiến hành trả lại Nếu row_index_num lớn hơn số mặt hàng trong bảng tìm tìm, lỗi #REF! sẽ tiến hành trả lại Nếu các bạn tra cứu kiếm với hiệu quả khớp kha khá cùng lookup_value bé dại hơn quý hiếm bé dại độc nhất trong bậc nhất 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 quá trình, chúng ta không chỉ có nắm rõ được các hàm mà hơn nữa nên sử dụng giỏi cả những giải pháp của Excel. Những hàm nâng cấp góp áp dụng giỏi vào quá trình nhỏng SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những dụng cụ thường thực hiện là Data validation, Conditional formatting, Pivot table…