Cách sử dụng hàm hlookup trong excel

Nhỏng bạn vẫn biết Microsoft có 3 hàm dùng để làm tìm kiếm giá trị, LOOKUPhường, VÀ HLOOKUP, cùng người tiêu dùng cảm thấy bồn chồn Lúc thực hiện 3 hàm này tuyệt nhất. Trong bài viết này, Học Excel Online đã tập trung vào các điểm sáng của hàm HLOOKUPhường trong Excel với chuyển một vài ví dụ minch hoạ giúp đỡ bạn sử dụng Hlookup một giải pháp kết quả tốt nhất.

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


HÀM HLOOKUPhường TRONG EXCEL LÀ GÌ?

Hàm HLOOKUPhường được sử dụng nhằm search một cực hiếm khăng khăng trong mặt hàng trên cùng của bảng cùng trả lại một giá trị khác trong cùng cột tự hàng mà bạn hướng dẫn và chỉ định.

Hàm HLOOKUPhường áp dụng được cho Microsoft Excel năm 2016, Excel 2013, Excel 2010, Excel 2007 và phiên phiên bản cũ hơn.

CÚ PHÁPhường 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): cực hiếm cần tìm. Có thể là ô tmê mệt 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 tìm cực hiếm tất cả nhị sản phẩm dữ liệu trsinh hoạt lên. cũng có thể là mảng thường, được đặt tên hoặc bảng Excel. Các cực hiếm tìm kiếm kiếm cần được đặt tại bậc nhất tiên của table_array

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

Range_lookup (tuỳ chọn): một giá trị logic (Boolean) cho thấy thêm HLOOKUP.. cần được kiếm tìm tác dụng khớp chính xác hay kha khá.

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

Nếu FALSE, chỉ tác dụng khớp đúng chuẩn được trả về. Nếu không giá trị làm sao trong sản phẩm chỉ định và hướng dẫn khớp đúng đắn với cái giá trị kiếm tìm tìm, hàm Hlookup sẽ trả về lỗi #N/A.

Để dễ hiểu hơn, bạn có thể dịch cú pháp HLOOKUPhường của Excel sang trọng giờ đồng hồ Việt như sau

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

Để đọc phương pháp chuyển động, họ hãy cùng coi một ví dụ đơn giản. Giả sử các bạn bao gồm một bảng cùng với báo cáo cơ bản về những hành tinh của hệ Mặt trời. quý khách hàng mong 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, họ đang sử dụng những tyêu thích số sau

Lookup_value là B5, ô chứa tên của thế giới bạn muốn tìm

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

Row_index_num là 2 vì chưng Đường kính ngơi nghỉ hàng thứ 2 trong bảng

Range_lookup  là FALSE bởi mặt hàng đầu tiên của bảng ko được bố trí theo trang bị từ bỏ từ A đến Z, phải bọn họ chỉ hoàn toàn có thể tra cứu quý hiếm đúng mực sống trong ví dụ này

Bây giờ đồng hồ xếp những tmê man số vào và họ gồm bí quyết như sau:

=HLOOKUP (40, A2:B15, 2)

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

Bất cứ khi nào các bạn kiếm tìm quý hiếm vào hàng, hãy lưu giữ phần đa điều sau:

Hàm HLOOKUPhường chỉ hoàn toàn có thể tra cứu ngơi nghỉ hàng bên trên thuộc của table_array. Nếu bạn phải search ngơi nghỉ phần nhiều vị trí khác, hãy thực hiện công thức Index hoặc Match HLOOKUP. trong Excel ko riêng biệt được chữ hoa với thường Nếu range_lookup được đặt là TRUE hoặc quăng quật trống (hiệu quả khớp tương đối), những quý giá sinh sống bậc nhất của table_array nên được xếp theo lắp thêm tự tăng nhiều (A-Z) từ trái sang trọng đề xuất.

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

Nhỏng chúng ta sẽ biết, cả nhị hàm HLOOKUP VÀ VLOOKUPhường. dùng để tìm kiếm tìm một cực hiếm. Nhưng bọn chúng không giống nhau vào phương pháp vận động. Chúng ta dễ ợt nhận thấy thương hiệu của nhị hàm khác nhau sống chữ cái đầu – “H” là ngang (horizontal) với “V” là dọc (vertical)

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

Hình tiếp sau đây miêu tả sự khác nhau thân nhị bí quyết Vlookup và Hlookup

khi nào sử dụng VLOOKUPhường, bao giờ sử dụng HLOOKUP

Lúc nào áp dụng VLOOKUPhường, bao giờ áp dụng HLOOKUP? Hẳn chúng ta khôn cùng thắc mắc với thắc mắc này. Chúng ta có 1 bí quyết để tiện lợi thừa nhận diện nó, đó là Cnạp năng lượng cứ đọng theo đối tượng Lookup_value và kết cấu của vùng bảng Table_Array (Bảng chứa dữ liệu tra cứu kiếm với tác dụng phải 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 loại đầu tiên của bảng thì cần sử dụng Hlookup

ỨNG DỤNG CỦA HLOOKUP. TRONG EXCEL

Tìm giá trị theo hàng với kết quả khớp kha khá với chủ yếu xác

Hàm HLOOKUPhường vào Excel có thể search hiệu quả khớp chính xác và ko đúng mực dựa trên quý giá của tham số range_lookup

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

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

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

HLOOKUP cùng với công dụng khớp tương đối

Giả sử bạn gồm một danh sách những toàn cầu sống mặt hàng 2 (B2:I2) với ánh nắng mặt trời của chúng ngơi nghỉ sản phẩm 1 (B1:I1). Quý Khách ước ao tra cứu địa cầu bao gồm ánh nắng mặt trời bằng nhiệt độ sinh sống ô B4.

Trong ngôi trường thích hợp chúng ta chần chừ đúng chuẩn ánh nắng mặt trời đề xuất tìm kiếm, bạn cũng có thể tạo hàm Hlookup để đưa ra công dụng khớp sớm nhất giả dụ quý hiếm chính xác ko được tra cứu thấy.

lấy ví dụ như, để biết thế giới làm sao tất cả ánh nắng mặt trời vừa đủ vào lúc -340°F, bạn sử dụng bí quyết Hlookup như sau (range_lookup được đặt là TRUE trong ngôi trường thích hợp này):

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

Hãy nhớ rằng công dụng khớp tương đối tận hưởng thu xếp các quý giá ngơi nghỉ số 1 tiên trường đoản cú nhỏ tuổi độc nhất đến lớn số 1 hoặc trường đoản cú A mang đến Z, nếu như không hàm Hlookup sẽ giới thiệu tác dụng 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 trong những toàn cầu rét độc nhất trong hệ Mặt ttránh luôn giữ nhiệt độ vừa đủ vào tầm khoảng -346 độ F.

Hlookup cùng với hiệu quả khớp bao gồm xác

Nếu các bạn biết đúng đắn cực hiếm đề nghị tìm, chúng ta cũng có thể đặt tham mê số cuối cùng của hàm Hlookup là FALSE:

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

Một phương diện, hiệu quả khớp tường dối tiện lợi áp dụng rộng vày nó ko yêu cầu sắp xếp tài liệu sống số 1 tiên. Mặt không giống, giả dụ kết quả khớp đúng chuẩn ko được tìm thấy, lỗi #N/A sẽ tiến hành trả về.

Xem thêm: Hướng Dẫn Cách Xóa Nền Trong Word Đơn Giản Nhất!, Cách Xóa Màu Nền Trong Word

Tip Để giúp người tiêu dùng ko cảm giác hoảng sợ Khi nhìn thấy lỗi N/A lộ diện, bạn có thể đưa hàm Hlookup vào hàm INFERROR với gửi thông điệp của chúng ta

=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 tầm thường, kiếm tìm giá trị theo mặt hàng xuất phát điểm từ một trang tính hoặc một bảng tính không giống tức là các bạn buộc phải hỗ trợ các tđắm đuối chiếu ngoại đường cho hàm HLOOKUPhường.

Để đem quý hiếm khớp từ 1 trang tính không giống, các bạn buộc phải 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 chứa vệt biện pháp hoặc kí từ bỏ chưa phải chữ cái, đặt nó trong vệt ngoặc đơn như sau:

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

Lúc tđam mê chiếu từ bảng tính khác, đánh tên bảng tính trong 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á trị xuất phát điểm từ một bảng tính đóng góp, bạn cần chỉ ra đường dẫn mang lại bảng tính:

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

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

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

Nhỏng trong ngôi trường thích hợp của Vlookup, hàm HLOOKUPhường được cho phép sử dụng những kí từ bỏ đại diện thay mặt sau mang đến tmê say số lookup_value:

Dấu hỏi (?) để khớp với bất kỳ một kí tự solo nàoDấu sao (*) để khớp với một chuỗi các kí tự

Các kí tự đại diện khôn xiết hữu ích Lúc bạn có nhu cầu đem thông tin xuất phát từ 1 các đại lý tài liệu dựa trên một vài đoạn văn uống phiên bản phía bên trong ngôn từ của ô kiếm tìm kiếm

ví dụ như, các bạn tất cả một list tên những người tiêu dùng ở sản phẩm 1 và số order ngơi nghỉ hàng 2. Quý Khách ao ước search số order của một quý khách hàng nhất mực làm sao kia tuy vậy chúng ta cấp thiết lưu giữ tên chính xác vị khách hàng cơ mà chỉ nhớ nó bắt đầu với chữ “La”.

Cho rằng dữ liệu của người sử dụng ở ô B1:I2 (table_array) với sản phẩm công nghệ trường đoản cú những số làm việc mặt hàng 2 (row_index_num), công thức như sau:

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

Để công thức linh hoạt hơn, bạn có thể Đánh Giá trị tìm kiếm kiếm vào một trong những ô rõ ràng, ví dụ B4, và đã nhập vào ô đó kí từ bỏ thay mặt đại diện nhỏng sau:

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

Lưu ý

Để hàm HLOOKUP với kí tự đại diện thay mặt gửi công dụng đúng, range_lookup đề nghị được đặt chế độ FALSENếu table_array chứa đựng nhiều hơn một quý hiếm khớp với kí tự thay mặt, cực hiếm đầu tiên tìm kiếm được sẽ được trả về

Ô tham mê chiếu hoàn hảo nhất và kha khá vào HLookup

Nếu bạn đang viết hàm Hlookup cho 1 ô, bạn cũng có thể ko bắt buộc quyên tâm buộc phải thực hiện ô tham chiếu hoàn hảo tuyệt kha khá vì cả nhị phần lớn phù hợp. Tuy nhiên, Khi xào nấu một bí quyết đến nhiều ô tsi mê chiếu, bạn cần sáng tỏ được nhì loại này

table_array luôn luôn được đặt cố định và thắt chặt bởi những ô tđam mê chiếu xuất xắc so với kí hiệu “$”, ví dụ $B$1:$I$2tsay đắm chiếu lookup_value là tương đối hoặc hỗn hợp dựa trên logic kinh doanh của bạn

Để dễ nắm bắt hơn, họ cùng coi kĩ hàm Hlookup Lúc lấy dữ liệu xuất phát từ một trang tính không giống

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

Trong cách làm bên trên, bọn họ thực hiện tham chiếu tuyệt đối hoàn hảo ($B$1:$I$2) vào table_array do nó cần phải giữ ko thay đổi lúc phương pháp được sao chép tự những ô khác

Đối cùng với lookup_value (B$1), chúng ta thực hiện tmê man chiếu các thành phần hỗn hợp, cột kha khá và sản phẩm tuyệt đối hoàn hảo bởi vì những quý hiếm tra cứu kiếm (thương hiệu hành tinh) nằm tại và một mặt hàng (mặt hàng 1) nhưng mà ở những cột không giống nhau (từ bỏ B đến I) cùng cột tđắm đuối chiếu đề nghị thay đổi dựa trên địa điểm kha khá của ô cất 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 HLOOKUPhường

Nlỗi các bạn đang biết, hàm HLOOKUPhường bao gồm một vài ba tiêu giảm với điểm yếu lớn số 1 là cần yếu thể tra cứu những quý hiếm nằm không tính số 1 và phải xếp những quý hiếm theo đồ vật từ khi kiếm tìm kết quả khớp kha khá. May mắn thay, sự phối hợp thân hàm INDEX cùng MATCH sẽ khắc chế được phần nhiều điểm này:

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

Giả sử quý giá search tìm ngơi nghỉ ô B7, bạn đang kiếm tìm một công dụng khớp sống hàng 2 (B2:I2), với ước ao cực hiếm trả về trường đoản cú sản phẩm 1 (B1:I1), phương pháp nlỗi 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 vừa lòng INDEX MATCH những vận động giỏi.

Cách áp dụng H-lookup biệt lập dạng văn bản vào Excel

Nlỗi sẽ nhắc ban đầu, hàm HLOOKUPhường. quan yếu minh bạch chữ hay và in hoa. Trong các ngôi trường đúng theo Lúc dạng văn bản của kí tự đặc trưng, chúng ta có thể sử dụng hàm EXACT để so sánh các ô và đặt bên phía trong hàm INDEX MATCH như trong ví dụ trước:

INDEX (sản phẩm chứa cực hiếm trả về, MATCH (TRUE, EXACT (mặt hàng search kiếm, quý giá search kiếm), 0))

Giả sử cực hiếm kiếm tìm kiếm của khách hàng sinh sống ô B4, mảng kiếm tìm kiếm là B1:I1, mảng giá trị trả về là B2:I2, bí quyết tất cả dạng nlỗi sau:

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

Lưu ý: Đây là bí quyết mảng vì vậy chúng ta buộc phải ấn tổng hợp Ctrl + Shift + Enter sau khi viết bí quyết

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

Hàm Hlookup là 1 trong những hàm phức hợp bởi nó có tương đối nhiều thành phần trong phương pháp khiến cho những lỗi #N/A, #VALUE hoặc #REF liên tiếp xẩy ra. Nếu hàm HLOOKUPhường của chúng ta không chuyển động được, nguyên ổn nhân có thể là 1 trong trong những lỗi dưới đây

HLOOKUPhường không thể search tìm những mặt hàng phía bên trên

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

Kết trái khớp tương đối với đúng đắn

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

Bảng tyêu thích chiếu biến hóa Khi xào luộc công thức

Khi sử dụng nhiều HLOOKUPhường để mang đọc tin về mặt hàng cất giá trị tìm kiếm, các bạn đề xuất khoá tđắm đuối chiếu table_array

Thêm vào hoặc xoá sút hàng mới

Để gọi tại vì sao thêm một mặt hàng mới hoàn toàn có thể làm hư công thức Hlookup, bạn phải đọc biện pháp HLOOKUPhường lấy đọc tin về quý giá tìm kiếm tìm đó là dựa trên chỉ số của hàng chúng ta chỉ định

Giả sử bạn có nhu cầu lấy số liệu lợi nhuận dựa vào ID sản phẩm. Những số liệu này sống hàng 4 cần chúng ta tấn công 4 vào tsay đắm số row_index_num. Nhưng lúc 1 hàng new được thêm vào, nó biến đổi mặt hàng đồ vật 5 và Hlookup chẳng thể vận động được. Vấn đề xảy ra tựa như nhỏng khi chúng ta xoá bớt một hàng vào bảng.

Cách xử lý là khoá bảng để tách vấn đề người sử dụng thêm 1 mặt hàng bắt đầu vào hoặc thực hiện cách làm INDEX/MATCH cụ cho Hlookup. Trong Index/Match, các bạn yêu cầu chứng thật những mặt hàng nhằm search quý hiếm cùng trả giá trị về như mảng tđắm say chiếu cùng Excel có thể từ bỏ điều chỉnh các tsi chiếu này. Nhờ vậy, chúng ta không cần thiết phải băn khoăn lo lắng về vụ việc thêm xuất xắc xoá bớt hàng như Lúc sử dụng bí quyết Hlookup.

Trùng lặp trong bảng

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

Nếu tất cả một vài ba quý hiếm kiểu như nhau trong bảng, chọn 1 trong các chiến thuật nhưng cân xứng với bạn nhất:

Loại vứt giống nhau bằng cách cần sử dụng chế độ của Excel Nếu bạn muốn trùng lặp được không thay đổi vào dữ liệu, tạo nên PivotTable để team và thanh lọc những tài liệu theo thử khám phá của bản thân Sử dụng cách làm mảng nhằm rút các cực hiếm trùng khỏi mảng tra cứu kiếm Khoảng trắng

lúc công thức Hlookup của người tiêu dùng 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 và quý giá tìm kiếm xem gồm kí từ bỏ trắng nào không. Quý Khách có thể gấp rút loại trừ những khoảng không bằng phương pháp sử dụng hàm TRIM trong Excel.

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

Chuỗi văn bản y hệt như số là một trong những khó khăn khác khi áp dụng bí quyết Excel. Mô tả chi tiết của sự việc này sẽ được lý giải vào Tại sao những cách làm Excel xong xuôi chuyển động

Giá trị tìm kiếm kiếm vượt quá 255 kí trường đoản cú

Tất cả các hàm tra cứu kiếm vào Excel chỉ hoạt động khi cực hiếm kiếm tìm tìm bên dưới 255 kí từ. Giá trị tra cứu tìm dài ra hơn nữa vẫn trả lại kết quả là lỗi #VALUE!. Vì hàm INDEX/MATCH giới hạn max con số kí từ bắt buộc bạn cũng có thể hạn chế lỗi này lúc áp dụng INDEX/MATCH

Đường dẫn không thiếu 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, nên nhớ rằng bạn phải cung ứng rất đầy đủ băng thông tới nó.

Sai tđắm say số

Các bạn nên đừng quên hàm Hlookup là 1 hàm phức hợp phải thật cảnh giác Khi áp dụng. Dưới đấy là một vài lỗi phổ cập khi nhập sai tham số

Nếu row_index_num nhỏ rộng 1, lỗi #VALUE! sẽ được trả lại Nếu row_index_num to 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 chúng ta kiếm tìm kiếm cùng với tác dụng khớp tương đối với lookup_value nhỏ dại rộng quý hiếm nhỏ tuổi 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ể vận dụng tốt Excel vào vào công việc, chúng ta không những nắm vững được những hàm Nhiều hơn phải áp dụng giỏi cả những hiện tượng của Excel. Những hàm cải thiện góp vận dụng giỏi vào các bước nlỗi SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những chế độ hay thực hiện là Data validation, Conditional formatting, Pivot table…