SỬ DỤNG HÀM VLOOKUP HLOOKUP TRONG EXCEL 2010

Nlỗi bạn vẫn biết Microsoft tất cả 3 hàm dùng làm tìm cực hiếm, LOOKUP., VÀ HLOOKUP.., và người tiêu dùng Cảm Xúc hoảng loạn khi sử dụng 3 hàm này độc nhất. Trong bài viết này, Học Excel Online sẽ triệu tập vào các điểm lưu ý của hàm HLOOKUP trong Excel cùng gửi một vài ba ví dụ minc hoạ giúp cho bạn áp dụng Hlookup một phương pháp công dụng nhất.

Bạn đang xem: Sử dụng hàm vlookup hlookup trong excel 2010


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

Hàm HLOOKUP được thực hiện để tìm kiếm một cực hiếm nhất mực trong mặt hàng trên thuộc của bảng với trả lại một quý giá khác trong cùng cột trường đoản cú hàng cơ mà chúng ta chỉ định.

Hàm HLOOKUPhường thực hiện được mang lại Microsoft Excel năm 2016, Excel 2013, Excel 2010, Excel 2007 và phiên phiên bản cũ rộng.

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

Cú pháp hàm Hlookup nhỏng sau

HLOOKUP (lookup_value, table array, row_index_num, )

Với

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


*

*

Table_array (bắt buộc): bảng search tìm giá trị bao gồm nhị sản phẩm tài liệu trsinh hoạt lên. Có thể là mảng thường xuyên, chọn cái tên hoặc bảng Excel. Các giá trị tìm tìm cần được đặt tại số 1 tiên của table_array

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

Range_lookup (tuỳ chọn): một cực hiếm ngắn gọn xúc tích (Boolean) cho biết thêm HLOOKUPhường rất cần phải tìm kiếm hiệu quả khớp đúng mực hay kha khá.

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

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

Để dễ dàng nắm bắt hơn, chúng ta có thể dịch cú pháp HLOOKUP.. của Excel sang giờ đồng hồ Việt như sau

HLOOKUPhường (tra cứu kiếm cực hiếm này, vào bảng này, trả lại quý giá từ mặt hàng này, )

Để hiểu cách vận động, họ hãy thuộc coi một ví dụ dễ dàng và đơn giản. Giả sử bạn bao gồm một bảng với báo cáo cơ bản về các toàn cầu của hệ Mặt trời. Quý Khách ý muốn hàm trả về đường kính của trái đất mang tên vào ô B5.

Trong phương pháp Hlookup, bọn họ đang thực hiện các tham mê số sau

Lookup_value là B5, ô cất thương hiệu của địa cầu bạn muốn tìm

Table_arrray là B2:I3, bảng đựng cực hiếm yêu cầu tìm

Row_index_num là 2 bởi Đường kính nghỉ ngơi mặt hàng thứ hai trong bảng

Range_lookup  là FALSE do sản phẩm trước tiên của bảng không được bố trí theo lắp thêm từ trường đoản cú A cho Z, phải họ chỉ rất có thể tìm quý giá đúng mực làm việc trong ví dụ này

Bây tiếng xếp những tmê mệt số vào cùng bọn họ bao gồm phương pháp 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ờ các bạn search quý giá trong hàng, hãy nhớ những điều sau:

Hàm HLOOKUP.. chỉ rất có thể tìm sống hàng bên trên thuộc của table_array. Nếu bạn phải tìm kiếm làm việc những vị trí không giống, hãy sử dụng bí quyết Index hoặc Match HLOOKUPhường vào Excel không sáng tỏ được chữ hoa và thường Nếu range_lookup được đặt là TRUE hoặc bỏ trống (kết quả khớp tương đối), những quý hiếm sinh hoạt hàng đầu của table_array buộc phải được xếp theo thiết bị từ bỏ tăng đột biến (A-Z) từ trái quý phái đề xuất.

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

Như các bạn sẽ biết, cả nhì hàm HLOOKUP VÀ VLOOKUP dùng để làm tra cứu kiếm một cực hiếm. Nhưng chúng không giống nhau trong biện pháp hoạt động. Chúng ta dễ dãi nhận ra 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à “V” là dọc (vertical)

Do kia, các bạn áp dụng hàm VLOOKUPhường để tìm kiếm giá trị theo cột sống phía bên trái của tài liệu bạn có nhu cầu search. Hàm HLOOKUP dùng để tìm kiếm quý hiếm theo mặt hàng ngang. Nó tìm kiếm quý hiếm sinh hoạt số 1 tiên của bảng với trả lại giá trị sống sản phẩm được hướng dẫn và chỉ định ở vào và một cột.

Hình sau đây diễn tả sự khác nhau thân nhị bí quyết Vlookup và Hlookup

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

lúc như thế nào thực hiện VLOOKUPhường., lúc nào áp dụng HLOOKUP? Hẳn bạn cực kỳ vướng mắc cùng với câu hỏi này. Chúng ta có một tuyệt kỹ để dễ ợt thừa nhận diện nó, chính là Căn cứ đọng theo đối tượng Lookup_value và cấu trúc của vùng bảng Table_Array (Bảng cất dữ liệu kiếm tìm kiếm cùng kết quả cần tìm)

Nếu lookup_value ở trên cột đầu tiên của bảng thì sử 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.. TRONG EXCEL

Tìm quý giá theo hàng cùng với kết quả khớp kha khá với bao gồm xác

Hàm HLOOKUP. vào Excel có thể tìm kiếm công dụng khớp chính xác với không chính xác dựa trên giá trị của tsay mê số range_lookup

TRUE hoặc quăng quật trống: hiệu quả khớp tương đốiFALSE: kết quả khớp bao gồm xác

Nên hãy nhờ rằng mặc dù chúng ta thường xuyên nói là tác dụng khớp tương đối, phương pháp Hlookup luôn search một công dụng khớp đúng đắn đầu tiên. Đặt tmê mẩn số là FALSE được cho phép hàm trả về kết quả khớp tương đối (giá trị gần nhất nhở rộng giá trị yêu cầu tra cứu kiếm) nếu như kết quả khớp đúng chuẩn không kiếm thấy; TRUE hoặc bị vứt trống đã trả về lỗi #N/A

Chúng ta thuộc coi ví dụ minh hoạ dưới đây

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

Giả sử các bạn bao gồm một danh sách các hành tinh sinh hoạt mặt hàng 2 (B2:I2) cùng nhiệt độ của chúng ở hàng 1 (B1:I1). Bạn mong mỏi kiếm tìm thế giới bao gồm ánh sáng bởi ánh nắng mặt trời ngơi nghỉ ô B4.

Trong trường phù hợp chúng ta ngần ngừ đúng đắn ánh sáng yêu cầu tìm, bạn có thể tạo ra hàm Hlookup để đưa ra hiệu quả khớp gần nhất giả dụ giá trị đúng đắn ko được search thấy.

lấy ví dụ, để tìm hiểu trái đất như thế nào tất cả ánh sáng mức độ vừa phải vào mức -340°F, bạn sử dụng cách làm Hlookup nlỗi sau (range_lookup được đặt là TRUE trong ngôi trường phù hợp này):

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

Hãy đừng quên kết quả khớp kha khá đề nghị bố trí những cực hiếm sống hàng đầu tiên trường đoản cú nhỏ độc nhất vô nhị mang lại lớn nhất hoặc tự A đến Z, còn nếu như không hàm Hlookup đang chỉ dẫn hiệu quả sai.

Trong hình chụp dưới đây, hàm trả về thế giới Hải Vương, một giữa những toàn cầu lạnh độc nhất vô nhị trong hệ Mặt ttránh luôn giữ lại á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 bao gồm xác

Nếu bạn biết đúng đắn quý giá buộc phải tìm, chúng ta có thể đặt tđê mê số sau cùng của hàm Hlookup là FALSE:

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

Một phương diện, công dụng khớp tường dối dễ dãi áp dụng hơn do nó không đòi hỏi bố trí tài liệu làm việc số 1 tiên. Mặt khác, nếu công dụng khớp đúng chuẩn ko được search thấy, lỗi #N/A sẽ được trả về.

Xem thêm:

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

=INFERROR (HLOOKUPhường (B4, B1:I2, 2, FALSE), “Xin lỗi, không kiế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 tầm thường, tra cứu giá trị theo mặt hàng xuất phát từ 1 trang tính hoặc một bảng tính không giống tức là bạn cần cung ứng những tsay mê chiếu ngoại con đường đến hàm HLOOKUPhường

Để mang cực hiếm khớp xuất phát từ 1 trang tính khác, bạn nên chỉ rõ thương hiệu của trang tính đặt trước lốt “!”. Ví dụ:

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

*

Nếu tên trang tính đựng vết biện pháp hoặc kí tự không phải vần âm, đặt nó trong lốt ngoặc đối chọi như sau:

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

khi tmê man chiếu tự bảng tính không giống, khắc 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 muốn đem giá trị xuất phát điểm từ một bảng tính đóng góp, bạn cần chỉ 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: Txuất xắc vì chưng khắc tên bảng tính với trang tính bằng tay, bạn cũng có thể chọn những ô trong trang tính khác với Excel đã thêm tsi chiếu ngoại con đường vào hàm của bạn một giải pháp tự động hóa

HLOOKUPhường với tác dụng khớp một phần (thực hiện kí từ bỏ đại diện)

Nhỏng vào trường phù hợp của Vlookup, hàm HLOOKUPhường. chất nhận được thực hiện các kí tự đại diện sau mang đến tđê mê số lookup_value:

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

Các kí trường đoản cú đại diện thay mặt vô cùng có lợi khi bạn muốn rước thông tin xuất phát điểm từ một các đại lý dữ liệu dựa trên một vài ba đoạn văn uống bạn dạng nằm trong câu chữ của ô tra cứu kiếm

lấy một ví dụ, chúng ta có một list tên những khách hàng ngơi nghỉ hàng 1 cùng số order nghỉ ngơi sản phẩm 2. quý khách hy vọng kiếm tìm số order của một người sử dụng nhất quyết như thế nào đó cơ mà chúng ta tất yêu lưu giữ tên đúng đắn vị khách hàng cơ mà chỉ nhớ nó bước đầu với chữ “La”.

Cho rằng tài liệu của công ty sống ô B1:I2 (table_array) với sản phẩm công nghệ tự những số nghỉ ngơi mặt hàng 2 (row_index_num), bí quyết nhỏng sau:

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

Để công thức linh hoạt rộng, chúng ta cũng có thể đánh giá trị kiếm tìm kiếm vào một trong những ô ví dụ, ví dụ B4, với gắn vào ô kia kí từ bỏ thay mặt nhỏng sau:

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

Lưu ý

Để hàm HLOOKUP.. với kí từ bỏ thay mặt gửi hiệu quả đúng, range_lookup đề nghị được đặt chế độ FALSENếu table_array đựng được nhiều rộng một giá trị khớp cùng với kí tự đại diện, quý giá trước tiên tìm kiếm được sẽ tiến hành trả về

Ô tmê mệt chiếu tuyệt vời nhất với tương đối vào HLookup

Nếu ai đang viết hàm Hlookup cho 1 ô, bạn cũng có thể không nên quyên tâm yêu cầu thực hiện ô tham mê chiếu tuyệt vời nhất hay kha khá bởi vì cả hai hầu hết cân xứng. Tuy nhiên, khi sao chép một phương pháp đến các ô tmê say chiếu, bạn cần khác nhau được nhị loại này

table_array luôn được đặt thắt chặt và cố định bằng những ô tsi chiếu tốt so với kí hiệu “$”, ví dụ $B$1:$I$2tsi chiếu lookup_value là kha khá hoặc các thành phần hỗn hợp dựa vào súc tích sale của bạn

Để dễ hiểu hơn, bọn họ cùng xem kĩ hàm Hlookup khi lấy dữ liệu xuất phát điểm từ 1 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 áp dụng ttê mê chiếu tuyệt vời nhất ($B$1:$I$2) vào table_array bởi vì nó cần được giữ lại không đổi Lúc bí quyết được sao chép trường đoản cú các ô khác

Đối cùng với lookup_value (B$1), chúng ta áp dụng tmê man chiếu tất cả hổn hợp, cột tương đối với mặt hàng hoàn hảo bởi những cực hiếm tra cứu tìm (thương hiệu hành tinh) nằm trên và một sản phẩm (sản phẩm 1) tuy thế sống những cột không giống nhau (từ bỏ B cho I) và cột tmê say chiếu đề nghị biến đổi dựa trên địa chỉ kha khá của ô đựng phương pháp được xào luộc.

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

Nlỗi chúng ta vẫn biết, hàm HLOOKUP.. gồm một vài ba tiêu giảm với nhược điểm lớn số 1 là chẳng thể thể kiếm tìm những cực hiếm ở kế bên số 1 và đề nghị xếp những quý giá theo vật dụng từ Lúc tìm hiệu quả khớp tương đối. May mắn vậy, sự phối kết hợp thân hàm INDEX cùng MATCH đã hạn chế và khắc phục được đầy đủ điểm này:

INDEX (vị trí chứa quý giá về, MATCH (quý giá kiếm tìm kiếm, nơi tìm kiếm quý giá, 0))

Giả sử cực hiếm tìm kiếm tìm sinh sống ô B7, ai đang tra cứu một công dụng khớp ngơi nghỉ sản phẩm 2 (B2:I2), cùng hy vọng giá trị trả về từ bỏ sản phẩm 1 (B1:I1), bí quyết 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ả 2 ngôi trường phù hợp INDEX MATCH gần như vận động giỏi.

Cách thực hiện H-lookup phân minh dạng chữ vào Excel

Như vẫn đề cập ban sơ, hàm HLOOKUP. bắt buộc phân minh chữ thường với in hoa. Trong những ngôi trường vừa lòng Lúc dạng văn bản của kí trường đoản cú đặc trưng, chúng ta có thể cần sử dụng hàm EXACT nhằm so sánh những ô cùng đặt bên trong hàm INDEX MATCH nhỏng trong ví dụ trước:

INDEX (mặt hàng chứa quý hiếm trả về, MATCH (TRUE, EXACT (hàng kiếm tìm tìm, quý hiếm search kiếm), 0))

Giả sử giá trị kiếm tìm kiếm của bạn ở ô B4, mảng tìm kiếm kiếm là B1:I1, mảng giá trị trả về là B2:I2, cách làm có dạng nlỗi sau:

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

Lưu ý: Đây là công thức mảng vì vậy chúng ta cần ấn tổ hợp Ctrl + Shift + Enter sau khoản thời gian viết cách làm

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

Hàm Hlookup là 1 hàm phức hợp bởi nó có khá nhiều thành bên trong phương pháp khiến cho những lỗi #N/A, #VALUE hoặc #REF thường xuyên xảy ra. Nếu hàm HLOOKUP. của người tiêu dùng ko vận động được, nguyên ổn nhân hoàn toàn có thể là 1 trong những trong những lỗi bên dưới đây

HLOOKUP cần yếu tra cứu kiếm các mặt hàng bên trên

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

Kết quả khớp tương đối và chính xác

Lúc ai đang tìm quý hiếm vào Excel, theo mặt hàng ngang tuyệt dọc, phần lớn các trường thích hợp là nhiều người đang tìm kiếm một quý giá cụ thể bắt buộc cần hiệu quả khớp chính xác. lúc tìm kiếm cùng với kết quả khớp tương đối (range_lookup được đặt TRUE hoặc vứt trống), các bạn yêu cầu nhớ xếp các cực hiếm trong sản phẩm theo thứ từ tăng nhiều.

Bảng tđắm đuối chiếu biến hóa Khi coppy công thức

Khi áp dụng các HLOOKUPhường để đưa lên tiếng về mặt hàng cất cực hiếm tra cứu kiếm, bạn nên khoá tmê mệt chiếu table_array

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

Để phát âm tại vì sao thêm một sản phẩm bắt đầu hoàn toàn có thể làm cho hỏng phương pháp Hlookup, bạn cần đọc giải pháp HLOOKUPhường đem đọc tin về quý hiếm tìm kiếm tìm chính là dựa vào chỉ số của mặt hàng các bạn chỉ định

Giả sử bạn muốn rước số liệu doanh thu dựa vào ID thành phầm. Những số liệu này ở sản phẩm 4 cần chúng ta tiến công 4 vào tđắm đuối số row_index_num. Nhưng lúc một mặt hàng new được cung cấp, nó đổi thay sản phẩm thứ 5 cùng Hlookup bắt buộc hoạt động được. Vấn đề xảy ra tương tự như nlỗi khi chúng ta xoá giảm một sản phẩm vào bảng.

Cách xử lý là khoá bảng để tách câu hỏi người tiêu dùng thêm một sản phẩm new vào hoặc áp dụng công thức INDEX/MATCH núm đến Hlookup. Trong Index/Match, các bạn đề xuất chỉ rõ các hàng nhằm tra cứu quý giá với trả quý hiếm về nhỏng mảng tđê mê chiếu cùng Excel có thể từ điều chỉnh các tham mê chiếu này. Nhờ vậy, các bạn không nhất thiết phải lo lắng về vụ việc thêm tốt xoá bớt hàng như khi áp 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 cực hiếm là cực hiếm trước tiên trong bảng khớp với giá trị tra cứu kiếm.

Nếu bao gồm một vài quý hiếm tương tự nhau trong bảng, chọn một trong những giải pháp nhưng cân xứng với bạn nhất:

Loại vứt giống nhau bằng phương pháp sử dụng nguyên lý 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 ra PivotTable nhằm team với thanh lọc những tài liệu theo yêu cầu của bản thân Sử dụng phương pháp mảng nhằm rút ít các quý giá trùng khỏi mảng kiếm tìm kiếm Khoảng trắng

lúc công thức Hlookup của doanh nghiệp trọn vẹn đúng cơ mà lỗi #N/A vẫn bị trả về, hãy soát sổ lại bảng cùng quý giá tra cứu kiếm coi có kí từ White làm sao ko. quý khách hoàn toàn có thể gấp rút sa thải các không gian bằng cách áp dụng hàm TRIM vào Excel.

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

Chuỗi vnạp năng lượng bản hệt như số là một trở ngại khác Khi sử dụng cách làm Excel. Mô tả chi tiết của sự việc này sẽ tiến hành lý giải trong Tại sao các phương pháp Excel dứt hoạt động

Giá trị search kiếm quá quá 255 kí tự

Tất cả các hàm search tìm vào Excel chỉ hoạt động khi quý hiếm tìm kiếm dưới 255 kí trường đoản cú. Giá trị tìm tìm dài hơn sẽ trả lại hiệu quả là lỗi #VALUE!. Vì hàm INDEX/MATCH giới hạn max số lượng kí từ yêu cầu bạn có thể hạn chế lỗi này Lúc áp dụng INDEX/MATCH

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

Nếu bạn tiến hành hàm Hlookup xuất phát từ một bảng tính khác, buộc phải nhớ rằng bạn phải hỗ trợ vừa đủ đường dẫn tới nó.

Sai tđắm đuối số

Các bạn nên đừng quên hàm Hlookup là một trong hàm phức tạp đề nghị thiệt cảnh giác Khi thực hiện. Dưới đấy là một vài ba lỗi phổ biến Lúc nhập sai tmê man số

Nếu row_index_num nhỏ hơn 1, lỗi #VALUE! sẽ được trả lại Nếu row_index_num to hơn số mặt hàng trong bảng tìm kiếm kiếm, lỗi #REF! sẽ được trả lại Nếu chúng ta tìm tìm cùng với công dụng khớp tương đối cùng lookup_value nhỏ hơn cực hiếm nhỏ dại tốt nhất vào số 1 tiên của table_array, lỗi #N/A sẽ được trả lại

Để rất có thể áp dụng xuất sắc Excel vào trong các bước, chúng ta không chỉ nắm vững được các hàm Ngoài ra bắt buộc áp dụng tốt cả những quy định của Excel. Những hàm nâng cao giúp áp dụng giỏi vào các bước nlỗi SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những phương pháp thường áp dụng là Data validation, Conditional formatting, Pivot table…