Như các bạn sẽ biết Microsoft có 3 hàm dùng để tra cứu quý giá, LOOKUP, VÀ HLOOKUPhường., với người tiêu dùng Cảm Xúc hồi hộp Lúc sử dụng 3 hàm này tốt nhất. Trong nội dung bài viết này, Học Excel Online đang triệu tập vào các Điểm sáng của hàm HLOOKUP trong Excel cùng đưa một vài ví dụ minch hoạ giúp bạn áp dụng Hlookup một cách công dụng duy nhất.

Bạn đang xem: Cách dùng hàm vlookup và hlookup


HÀM HLOOKUP TRONG EXCEL LÀ GÌ?

Hàm HLOOKUP. được áp dụng để tìm một giá trị cố định trong sản phẩm bên trên thuộc của bảng với trả lại một giá trị không giống vào thuộc cột trường đoản cú sản phẩm nhưng các bạn hướng dẫn và chỉ định.

Hàm HLOOKUP áp dụng được cho Microsoft Excel năm nhâm thìn, Excel 2013, Excel 2010, Excel 2007 và phiên bạn dạng cũ hơn.

CÚ PHÁP. VÀ CÁCH SỬ DỤNG HLOOKUP

Cú pháp hàm Hlookup nlỗi sau

HLOOKUP. (lookup_value, table array, row_index_num, )

Với

Lookup_value (bắt buộc): quý giá cần kiếm tìm. cũng có thể là ô tham chiếu, một quý hiếm hoặc chuỗi vnạp năng lượng phiên bản.


*

*

Table_array (bắt buộc): bảng tìm kiếm quý hiếm gồm nhì sản phẩm dữ liệu trsinh hoạt lên. Có thể là mảng thường xuyên, được đặt tên hoặc bảng Excel. Các cực hiếm tìm kiếm tìm đề nghị được đặt tại hàng đầu tiên của table_array

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

Range_lookup (tuỳ chọn): một giá trị xúc tích và ngắn gọn (Boolean) cho biết thêm HLOOKUPhường rất cần được search kết quả khớp đúng đắn hay tương đối.

Nếu TRUE hoặc làm lơ, hiệu quả khớp tương đối được trả về. Nghĩa là giả dụ hiệu quả khớp đúng mực không được tra cứu thấy, hàm Hlookup của các bạn sẽ trả về quý giá lớn nhất tiếp đến nhỏ hơn look_up value.

Nếu FALSE, chỉ tác dụng khớp đúng chuẩn được trả về. Nếu ko quý giá như thế nào vào mặt hàng hướng dẫn và chỉ định khớp chính xác với giá trị kiếm tìm tìm, hàm Hlookup đang trả về lỗi #N/A.

Để dễ nắm bắt rộng, chúng ta cũng có thể dịch cú pháp HLOOKUP của Excel lịch sự giờ đồng hồ Việt nlỗi sau

HLOOKUP. (search kiếm cực hiếm này, vào bảng này, trả lại quý hiếm từ hàng này, )

Để đọc phương pháp vận động, bọn họ hãy thuộc xem một ví dụ đơn giản và dễ dàng. Giả sử các bạn gồm một bảng cùng với thông báo cơ bản về các toàn cầu của hệ Mặt ttách. Bạn mong muốn hàm trả về 2 lần bán kính của toàn cầu có tên vào ô B5.

Trong công thức Hlookup, chúng ta đang thực hiện các tđắm say số sau

Lookup_value là B5, ô đựng tên của toàn cầu bạn có nhu cầu tìm

Table_arrray là B2:I3, bảng đựng quý giá đề xuất tìm

Row_index_num là 2 do Đường kính nghỉ ngơi hàng thứ hai vào bảng

Range_lookup  là FALSE bởi vì hàng thứ nhất của bảng ko được thu xếp theo vật dụng trường đoản cú tự A cho Z, đề nghị họ chỉ hoàn toàn có thể kiếm tìm quý giá đúng đắn sống vào ví dụ này

Bây tiếng xếp những tsi mê số vào và họ tất cả phương pháp nlỗi sau:

=HLOOKUPhường (40, A2:B15, 2)

3 ĐIỀU BẠN CẦN BIẾT VỀ HÀM HLOOKUPhường TRONG EXCEL 

Bất cứ đọng lúc nào bạn kiếm tìm quý giá trong sản phẩm, hãy ghi nhớ đều điều sau:

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

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

Nhỏng chúng ta đã biết, cả hai hàm HLOOKUP VÀ VLOOKUP. dùng để làm tìm kiếm tìm một quý giá. Nhưng chúng khác nhau trong cách vận động. Chúng ta dễ dãi nhận thấy tên của nhị hàm khác biệt ở chữ cái đầu – “H” là ngang (horizontal) với “V” là dọc (vertical)

Do đó, chúng ta áp dụng hàm VLOOKUPhường nhằm search cực hiếm theo cột ở phía phía trái của tài liệu bạn muốn kiếm tìm. Hàm HLOOKUPhường dùng để làm tìm kiếm giá trị theo hàng ngang. Nó tìm quý hiếm sống số 1 tiên của bảng và trả lại giá trị sống hàng được chỉ định ngơi nghỉ trong và một cột.

Hình dưới đây biểu thị sự khác biệt thân hai công thức Vlookup cùng Hlookup

Lúc như thế nào áp dụng VLOOKUPhường, khi nào sử dụng HLOOKUP

lúc nào áp dụng VLOOKUP, bao giờ sử dụng HLOOKUP? Hẳn bạn vô cùng vướng mắc cùng với câu hỏi này. Chúng ta có 1 bí quyết để dễ dàng thừa nhận diện nó, chính 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 cất tài liệu tìm kiếm cùng 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 chiếc đầu tiên của bảng thì sử dụng Hlookup

ỨNG DỤNG CỦA HLOOKUP TRONG EXCEL

Tìm quý hiếm theo mặt hàng với kết quả khớp kha khá với thiết yếu xác

Hàm HLOOKUPhường vào Excel có thể tra cứu công dụng khớp đúng chuẩn với ko đúng đắn dựa trên quý giá của tđắm say số range_lookup

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

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

Chúng ta cùng coi ví dụ minch hoạ bên dưới đây

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

Giả sử các bạn bao gồm một list những thế giới sinh hoạt hàng 2 (B2:I2) cùng ánh nắng mặt trời của bọn chúng sống sản phẩm 1 (B1:I1). Bạn ý muốn kiếm tìm hành tinh gồm nhiệt độ bằng ánh sáng làm việc ô B4.

Trong trường hòa hợp chúng ta chần chờ đúng đắn ánh nắng mặt trời nên tìm, bạn cũng có thể tạo hàm Hlookup để lấy ra kết quả khớp gần nhất nếu như cực hiếm chính xác ko được search thấy.

lấy ví dụ như, để tìm hiểu toàn cầu nào có ánh sáng vừa đủ vào khoảng -340°F, các bạn sử dụng công thức Hlookup như sau (range_lookup được đặt là TRUE trong trường hợp này):

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

Hãy đừng quên tác dụng khớp kha khá yên cầu bố trí các cực hiếm sinh hoạt bậc nhất tiên trường đoản cú bé dại độc nhất đến lớn nhất hoặc từ bỏ A mang lại Z, còn nếu như không hàm Hlookup vẫn đưa ra hiệu quả không đúng.

Trong hình chụp tiếp sau đây, hàm trả về toàn cầu Hải Vương, một trong những trái đất giá độc nhất trong hệ Mặt ttránh luôn duy trì ánh nắng mặt trời vừa phải vào lúc -346 độ F.

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

Nếu các bạn biết đúng chuẩn quý giá cần tra cứu, bạn có thể đặt tsi số sau cuối của hàm Hlookup là FALSE:

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

Một khía cạnh, công dụng khớp tường dối dễ ợt áp dụng hơn bởi vì nó không thử khám phá bố trí dữ liệu ở số 1 tiên. Mặt khác, trường hợp tác dụng khớp đúng mực không được tìm kiếm thấy, lỗi #N/A sẽ được trả về.

Tip Để góp người dùng không Cảm Xúc bối rối lúc nhận thấy lỗi N/A xuất hiện thêm, bạn cũng có thể gửi 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 thấy kết quả”)

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

Nhìn chung, tra cứu giá trị theo hàng từ một trang tính hoặc một bảng tính không giống tức thị bạn đề nghị cung cấp những tham mê chiếu ngoại tuyến đường mang đến hàm HLOOKUPhường

Để đem giá trị khớp từ 1 trang tính khác, chúng ta yêu cầu chứng thực thương hiệu của trang tính đặt trước vệt “!”. Ví dụ:

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

*

Nếu tên trang tính đựng vết phương pháp hoặc kí từ chưa phải vần âm, đặt nó trong dấu ngoặc đơn nlỗi sau:

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

lúc tmê say chiếu từ bảng tính không giống, đặt tên bảng tính trong ngoặc vuông:

=HLOOKUPhường. (B$1, Đườngkính!$B$1:$I$2, 2, FALSE)

Nếu bạn có nhu cầu đem giá trị xuất phát điểm từ một bảng tính đóng, bạn cần chỉ đi ra ngoài đường dẫn đến bảng tính:

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

Tip: Ttốt bởi đánh tên bảng tính và trang tính bằng tay, bạn có thể lựa chọn các ô vào trang tính khác với Excel vẫn thêm ttê mê chiếu ngoại tuyến đường vào hàm của công ty một phương pháp auto

HLOOKUPhường. cùng với hiệu quả khớp một trong những phần (áp dụng kí từ đại diện)

Nhỏng vào ngôi trường phù hợp của Vlookup, hàm HLOOKUP được cho phép thực hiện những kí tự thay mặt đại diện sau cho tđắm đuối số lookup_value:

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

Các kí trường đoản cú đại diện siêu bổ ích Lúc bạn có nhu cầu rước báo cáo xuất phát điểm từ 1 cơ sở dữ liệu dựa trên một vài đoạn vnạp năng lượng phiên bản phía trong văn bản của ô tìm kiếm

Ví dụ, bạn bao gồm một danh sách thương hiệu những quý khách hàng sinh hoạt mặt hàng 1 cùng số order sinh hoạt hàng 2. Quý Khách mong mỏi search số order của một khách hàng cố định nào kia tuy thế chúng ta cần thiết lưu giữ tên đúng chuẩn vị khách cơ mà chỉ ghi nhớ nó ban đầu với chữ “La”.

Cho rằng tài liệu của công ty ở ô B1:I2 (table_array) và sản phẩm từ bỏ những số sinh hoạt hàng 2 (row_index_num), cách làm nlỗi sau:

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

Để cách làm linch hoạt hơn, chúng ta cũng có thể đánh giá trị kiếm tìm tìm vào một ô ví dụ, ví dụ B4, và gắn vào ô kia kí từ thay mặt đại diện nhỏng sau:

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

Lưu ý

Để hàm HLOOKUPhường với kí tự đại diện đưa tác dụng đúng, range_lookup cần được đặt cơ chế FALSENếu table_array chứa nhiều rộng một quý hiếm khớp với kí trường đoản cú thay mặt, cực hiếm thứ nhất kiếm được sẽ tiến hành trả về

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

Nếu ai đang viết hàm Hlookup cho một ô, chúng ta có thể ko nên quan tâm đề xuất sử dụng ô tđắm say chiếu tuyệt vời nhất giỏi tương đối bởi vì cả hai phần lớn tương xứng. Tuy nhiên, Lúc coppy một bí quyết mang đến những ô tđê mê chiếu, bạn phải biệt lập được nhì nhiều loại này

table_array luôn được đặt thắt chặt và cố định bởi những ô tyêu thích chiếu hay so với kí hiệu “$”, ví dụ $B$1:$I$2tsi chiếu lookup_value là tương đối hoặc tất cả hổn hợp dựa vào lô ghích marketing của bạn

Để dễ hiểu hơn, bọn họ thuộc coi kĩ hàm Hlookup khi lấy dữ liệu từ 1 trang tính không giống

=HLOOKUPhường. (B$1, Duongkinh!$B$1:$I$2, 2, FALSE)

Trong bí quyết bên trên, chúng ta sử dụng tham chiếu tuyệt đối hoàn hảo ($B$1:$I$2) trong table_array do nó cần phải duy trì ko đổi Khi công thức được xào luộc tự các ô khác

Đối cùng với lookup_value (B$1), chúng ta thực hiện tđam mê chiếu các thành phần hỗn hợp, cột tương đối và hàng tuyệt đối vì chưng những quý hiếm search tìm (thương hiệu hành tinh) vị trí cùng một sản phẩm (mặt hàng 1) mà lại sống các cột khác biệt (tự B cho I) với cột tsay đắm chiếu bắt buộc biến hóa dựa vào địa chỉ tương đối 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

Nlỗi chúng ta đã biết, hàm HLOOKUP. có một vài giảm bớt với yếu điểm lớn số 1 là cần yếu thể tìm các cực hiếm nằm bên cạnh số 1 với đề nghị xếp những quý giá theo sản phẩm công nghệ từ bỏ Khi tìm hiệu quả khớp tương đối. May mắn cụ, sự phối kết hợp giữa hàm INDEX và MATCH đã khắc chế được mọi điểm này:

INDEX (nơi chứa giá trị về, MATCH (giá trị search kiếm, nơi tra cứu cực hiếm, 0))

Giả sử quý hiếm search kiếm ở ô B7, ai đang kiếm tìm một kết quả khớp sinh sống hàng 2 (B2:I2), với hy vọng cực hiếm trả về từ bỏ hàng 1 (B1:I1), công thức nhỏng sau

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

Trong hình chụp dưới đây, chúng ta có thể thấy vào cả hai ngôi trường hợp INDEX MATCH phần đa chuyể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 thiết rành mạch chữ thường xuyên và in hoa. Trong các trường hòa hợp lúc dạng văn bản của kí từ đặc biệt quan trọng, chúng ta cũng có thể dùng hàm EXACT để so sánh các ô và đặt bên phía trong hàm INDEX MATCH như vào ví dụ trước:

INDEX (sản phẩm đựng cực hiếm trả về, MATCH (TRUE, EXACT (mặt hàng kiếm tìm kiếm, cực hiếm tìm kiếm), 0))

Giả sử giá trị tìm kiếm kiếm của người sử dụng ngơi nghỉ ô B4, mảng tìm kiếm kiếm là B1:I1, mảng giá trị trả về là B2:I2, bí quyết gồm dạng như sau:

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

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

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

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

HLOOKUPhường. không thể tìm tìm những hàng bên trên

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

Kết trái khớp kha khá với đúng mực

Khi ai đang kiếm tìm quý giá vào Excel, theo sản phẩm ngang hay dọc, đa số các ngôi trường đúng theo là bạn đang search tìm một giá trị ví dụ phải cần hiệu quả khớp đúng chuẩn. khi kiếm tìm kiếm cùng với công dụng khớp tương đối (range_lookup được đặt TRUE hoặc vứt trống), chúng ta bắt buộc nhớ xếp các quý giá vào hàng theo thiết bị từ tăng nhiều.

Bảng ttê mê chiếu biến hóa lúc coppy công thức

Khi sử dụng các HLOOKUP để đưa đọc tin về hàng chứa quý giá kiếm tìm kiếm, bạn đề xuất khoá tsi mê chiếu table_array

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

Để đọc vì sao thêm 1 hàng bắt đầu rất có thể có tác dụng hỏng phương pháp Hlookup, bạn cần gọi cách HLOOKUP. đem lên tiếng về quý hiếm tìm tìm đó là dựa vào chỉ số của mặt hàng chúng ta chỉ định

Giả sử bạn muốn lấy số liệu doanh số dựa trên ID thành phầm. Những số liệu này ngơi nghỉ mặt hàng 4 nên chúng ta tấn công 4 vào tmê man số row_index_num. Nhưng khi một mặt hàng bắt đầu được cung cấp, nó phát triển thành hàng đồ vật 5 và Hlookup thiết yếu hoạt động được. Vấn đề xảy ra giống như như khi chúng ta xoá bớt một hàng vào bảng.

Cách giải quyết và xử lý là khoá bảng để tách bài toán người tiêu dùng thêm 1 sản phẩm new vào hoặc sử dụng công thức INDEX/MATCH nạm đến Hlookup. Trong Index/Match, chúng ta phải chứng minh các sản phẩm nhằm tra cứu giá trị với trả giá trị về như mảng tsay đắm chiếu cùng Excel hoàn toàn có thể trường đoản cú kiểm soát và điều chỉnh những ttê mê chiếu này. Nhờ vậy, chúng ta không cần thiết phải lo lắng về vấn đề thêm tốt xoá giảm mặt hàng nhỏng lúc sử dụng bí quyết Hlookup.

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

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

Nếu gồm một vài quý hiếm tương tự nhau vào bảng, lựa chọn 1 trong những chiến thuật mà lại tương xứng với chúng ta nhất:

Loại quăng quật giống nhau bằng cách dùng chế độ của Excel Nếu bạn muốn đụng hàng được không thay đổi vào dữ liệu, tạo nên PivotTable nhằm nhóm và thanh lọc những dữ liệu theo thử dùng của mình Sử dụng phương pháp mảng nhằm rút ít các giá trị trùng khỏi mảng tra cứu kiếm Khoảng trắng

Khi công thức Hlookup của doanh nghiệp trọn vẹn đúng tuy nhiên lỗi #N/A vẫn bị trả về, hãy kiểm tra lại bảng và quý giá tra cứu kiếm xem có kí từ White nào ko. quý khách hàng rất có thể mau lẹ loại trừ những khoảng không bằng cách sử dụng hàm TRIM trong Excel.

Xem thêm: Dùng Wc Làm Camera Quan Sát Từ Xa Bằng Alfred, Dùng Wc Làm Camera Quan Sát Từ Xa Như Thế Nào

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

Chuỗi vnạp năng lượng phiên bản hệt như số là 1 trong trở ngại không giống lúc thực hiện công thức Excel. Mô tả chi tiết của vấn đề này sẽ được lý giải vào Tại sao các công thức Excel kết thúc hoạt động

Giá trị search kiếm thừa quá 255 kí trường đoản cú

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

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

Nếu các bạn thực hiện hàm Hlookup xuất phát từ một bảng tính khác, buộc phải nhớ là bạn phải cung ứng đầy đủ đường truyền tới nó.

Sai tyêu thích số

Các chúng ta nên hãy nhớ là hàm Hlookup là một trong hàm tinh vi đề xuất thật cảnh giác Khi sử dụng. Dưới đây là một vài ba lỗi thịnh hành lúc nhập không đúng tmê mệt số

Nếu row_index_num nhỏ tuổi rộng 1, lỗi #VALUE! sẽ được trả lại Nếu row_index_num lớn hơn số sản phẩm vào bảng search kiếm, lỗi #REF! sẽ được trả lại Nếu các bạn kiếm tìm tìm cùng với tác dụng khớp tương đối với lookup_value bé dại rộng cực hiếm nhỏ tốt nhất trong bậc nhất tiên của table_array, lỗi #N/A sẽ được trả lại

Để có thể vận dụng tốt Excel vào trong quá trình, chúng ta không chỉ nắm vững được những hàm ngoại giả cần sử dụng giỏi cả những lao lý của Excel. Những 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 nguyên lý hay sử dụng là Data validation, Conditional formatting, Pivot table…