Hướng dẫn sử dụng hàm vlookup hlookup trong excel 2010
Trong bài viết hôm nay, chúng ta sẽ cùng nhau tìm hiểu về cách dùng và ứng dụng của hàm HLOOKUP VLOOKUP trong Excel, được xem là những hàm sử dụng phổ biến nhất nhé! Show
Nội dung bài viết Hàm HLOOKUP được dùng để tìm giá trị nhất định tại hàng trên cùng thuộc bảng tính, đồng thời trả lại một giá trị khác trong cùng cột tính từ hàng mà bạn chỉ định. Hàm HLOOKUPdùng được cho Microsoft Excel 2016, Excel 2013, Excel 2010, Excel 2007, kể cả các phiên bản cũ hơn. CÚ PHÁP VÀ CÁCH DÙNG HÀM HLOOKUP Cú pháp như sau: HLOOKUP (lookup_value, table array, row_index_num, [range_lookup]) Trong đó: Lookup_value (bắt buộc): là giá trị cần tìm. Đó có thể là ô tham chiếu, một giá trị hoặc là chuỗi văn bản. Table_array (bắt buộc): là bảng tìm kiếm giá trị gồm có hai hàng dữ liệu trở lên. Đó có thể là mảng thường, được đặt tên hoặc là bảng Excel. Những giá trị tìm kiếm phải được đặt ở vị trí hàng đầu tiên của table_array Row_index_num (bắt buộc): là số hàng trong table_array, nơi mà giá trị sẽ được trả về. Ví dụ như để trả lại giá trị trùng khớp từ hàng thứ 2, bạn sẽ đặt row_index_num bằng 2. Range_lookup (tuỳ chọn): là một giá trị logic (Boolean) cho biết hàm HLOOKUP cần phải tìm kết quả khớp chính xác hoặc là tương đối. Nếu TRUE hoặc là bỏ qua, kết quả khớp tương đối sẽ được trả về. Có nghĩa là nếu kết quả chính xác hoàn toàn không được tìm thấy, thì hàm HLOOKUP sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value. Nếu FALSE, thì chỉ kết quả khớp chính xác được trả về. Nếu như không có giá trị nào trong hàng chỉ định thỏa điều kiện khớp chính xác với giá trị tìm kiếm, hàm HLOOKUP sẽ trả về lỗi N/A.Nhằm giúp bạn dễ hiểu hơn, cú pháp HLOOKUP của Excel sẽ được dịch sang tiếng Việt như sau HLOOKUP (tìm kiếm giá trị này, trong bảng này, trả lại giá trị từ hàng này, [trả lại giá trị chính xác hoặc tương đối]) Tiếp đến, để giúp bạn nắm rõ được cách hoạt động của hàm, hãy cùng chúng tôi xem quan một ví dụ đơn giản. Bạn đang có một bảng tính với thông tin cơ bản về những hành tinh của hệ Mặt Trời. Và bạn đang muốn sử dụng một hàm giúp trả về đường kính của hành tinh này có tên ở ô B5. Ở công thức HLOOKUP, bạn dùng những tham số sau Lookup_value là ô B5, có chứa tên của hành tinh bạn muốn tìm Table_arrray là vùng B2:I3, bảng chứa giá trị cần tìm Row_index_num là 2 vì Đường kính ở hàng thứ 2 trong bảng Range_lookup là FALSE bởi vì hàng thứ nhất của bảng không được sắp xếp đúng thứ tự từ A đến Z, nên bạn chỉ có thể tìm giá trị chính xác ở trong ví dụ này Bây giờ hãy thực hiện xếp những tham số vào và bạn sẽ 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 EXCELBất cứ lúc nào bạn muốn tìm giá trị trong hàng, hãy nhớ các điều sau:
SỰ KHÁC NHAU GIỮA VLOOKUP VÀ HLOOKUPNhư các bạn đã biết, hàm HLOOKUP VÀ VLOOKUP đều dùng để tìm kiếm một giá trị. Nhưng hai hàm này khác nhau trong cách thức hoạt động. Bạn có thể dễ dàng nhận ra hai hàm khác nhau ở chữ cái đầu trong tên – “H” là ngang (horizontal), còn “V” là dọc (vertical) Vì vậy, bạn sẽ dùng hàm VLOOKUP để tiến hành tìm giá trị theo cột ở phía bên trái của dữ liệu mà bạn muốn tìm. Hàm HLOOKUP sẽ dùng để tìm giá trị theo hàng ngang. Hàm sẽ tìm giá trị ở hàng đầu tiên của bảng rồi trả lại giá trị ở hàng được chỉ định ở trong cùng một cột. Hình minh họa bên dưới thể hiện sự khác nhau giữa hai công thức Vlookup và Hlookup ỨNG DỤNG CỦA HLOOKUP TRONG EXCELThực hiện tìm giá trị theo hàng với kết quả khớp tương đối và chính xác Hàm HLOOKUP có khả năng tìm kết quả khớp chính xác và không chính xác dựa vào giá trị của tham số range_lookup
Bạn cần lưu ý rằng mặc dù ta vẫn hay nói là kết quả khớp tương đối, công thức HLOOKUP sẽ luôn tìm một kết quả khớp chính xác đầu tiên. Đặt tham số là FALSE sẽ cho phép hàm trả về kết quả khớp tương đối (là giá trị gần nhất nhỏ hơn giá trị cần tìm kiếm) nếu như kết quả khớp chính xác không được tìm thấy; TRUE hoặc bị bỏ trống sẽ trả về lỗi N/AHãy cùng xem ví dụ minh hoạ bên dưới đây
Chẳng hạn bạn có một danh sách những hành tinh ở hàng 2 (B2:I2) và nhiệt độ của các hành tinh này ở hàng 1 (B1:I1). Bạn đang muốn tìm hành tinh có nhiệt độ bằng nhiệt độ ở ô B4. Nếu trường hợp mà bạn không biết chính xác nhiệt độ cần tìm, bạn hãy xây dựng hàm Hlookup để có thể đưa ra kết quả khớp gần nhất nếu như giá trị chính xác không được tìm thấy. Ví dụ như để biết hành tinh nào có nhiệt độ trung bình vào khoảng -340°F, bạn hãy dùng công thức Hlookup như bên dưới (range_lookup được đặt là TRUE ở trường hợp này): \=HLOOKUP (B4, B1:I2, 2) Bạn cũng lưu ý rằng kết ủa khớp tương đối được yêu cầu sắp xếp những giá trị tại hàng đầu tiên theo thứ tự từ nhỏ nhất đến lớn nhất, hoặc là từ A đến Z, nếu không thì hàm Hlookup sẽ đưa ra kết quả sai. Tại hình minh họa bên dưới đây, hàm trả về hành tinh Hải Vương, là một trong các hành tinh lạnh nhất trong hệ Mặt trời luôn giữ nhiệt độ trung bình vào khoảng -346 độ F.
Nếu như bạn biết được chính xác giá trị cần tìm, bạn sẽ có thể đặt tham số cuối cùng của hàm Hlookup là FALSE: \=HLOOKUP (B4, B1:I2, 2, FALSE) Ngoài ra, kết quả khớp tương đối dễ dàng dùng hơn vì nó không yêu cầu sắp xếp các dữ liệu ở hàng đầu tiên. Mặt khác, nếu như kết quả khớp chính xác không tìm thấy, thì lỗi N/A sẽ được trả về.Tip Để giúp người sử dụng không cảm thấy lo lắng khi nhìn thấy lỗi N/A xuất hiện, bạn hãy đưa hàm Hlookup vào hàm INFERROR rồi gửi thông điệp của bạn \=INFERROR (HLOOKUP (B4, B1:I2, 2, FALSE), “Xin lỗi, không tìm thấy kết quả”) Xem thêm: Cách dùng hàm tính nhân trong Excel Cách dùng hàm Hlookup từ worksheet hoặc workbook khácTóm tại, để tìm giá trị theo hàng từ một trang tính hoặc là một bảng tính khác, tức là bạn phải cung cấp những tham chiếu ngoại tuyến cho hàm HLOOKUP Để có thể lấy giá trị khớp từ một trang tính khác, thì bạn phải chỉ rõ được tên của trang tính đặt trước dấu “!”. Ví dụ như sau: \=HLOOKUP (B$1, Duongkinh! $B$1:$I2, 2, FALSE) Nếu như tên trang tính có chứa dấu cách hoặc là kí tự không phải chữ cái, hãy đặt nó trong dấu ngoặc đơn như bên dưới: \=HLOOKUP (B$1, ‘Đường kính’!$B$1:$I$2, 2, FALSE) Khi thực hiện tham chiếu từ bảng tính khác, tiến hành đặt tên bảng tính trong ngoặc vuông: \=HLOOKUP (B$1, [BOOK1.xlsx]Đườngkính!$B$1:$I$2, 2, FALSE) Nếu như bạn muốn lấy giá trị từ một bảng tính đóng, thì bạn cần thực hiện chỉ ra đường dẫn đến bảng tính: \=HLOOKUP (B$1, ‘D:\Reports\[Book1.xlsx]Đườngkính’!$B$1:$I$2, 2, FALSE) Tip: Thay cho việc đánh tên bảng tính và trang tính thủ công bằng tay, bạn vẫn có thể tích chọn các ô trong trang tính khác và Excel sẽ tiến hành thêm tham chiếu ngoại tuyến vào hàm của bạn tự động HLOOKUP với kết quả khớp một phần (sử dụng kí tự đại diện) Cũng tương tự như trường hợp của hàm VLOOKUP, hàm HLOOKUP cho phép dùng những kí tự đại diện sau cho tham số lookup_value:
Những kí tự đại diện rất hữu ích khi bạn muốn thực hiện lấy thông tin từ một cơ sở dữ liệu dựa trên một số đoạn văn bản nằm trong nội dung của ô tìm kiếm. Chẳng hạn như, bạn đang có một danh sách tên những khách hàng ở hàng 1 và số order ở hàng 2. Bạn muốn thực hiện tìm số order của một khách hàng nhất định nào đó, tuy nhiên bạn không thể nhớ tên chính xác vị khách mà chỉ có thể nhớ nó bắt đầu với chữ “La”. Nhận thấy rằng dữ liệu của bạn ở ô B1:I2 (table_array) và thứ tự những số ở hàng 2 (row_index_num), công thức thể hiện như sau: \=HLOOKUP (“La*”, B1:I2, 2, FALSE) Để góp phần cho công thức linh hoạt hơn, bạn có thể thực hiện đánh giá trị tìm kiếm vào một ô cụ thể, ví dụ B4, và tiến hành gắn vào ô đó kí tự đại diện như bên dưới: \=HLOOKUP (B4&“*”, B1:I2, 2, FALSE) Lưu ý
Ô tham chiếu tuyệt đối và tương đối trong HLookupNếu như bạn đang viết hàm Hlookup cho một ô, bạn cũng có thể không cần quan tâm nên dùng ô tham chiếu tuyệt đối hay là tương đối vì cả hai đều rất phù hợp. Tuy vậy, khi thực hiện sao chép một công thức cho nhiều ô tham chiếu, bạn cũng cần phân biệt được hai loại này
Để bạn dễ hiểu hơn, hãy cùng xem kĩ hàm Hlookup khi thực hiện lấy dữ liệu từ một trang tính khác \=HLOOKUP (B$1, Duongkinh!$B$1:$I$2, 2, FALSE) Ở công thức trên, bạn dùng tham chiếu tuyệt đối ($B$1:$I$2) trong table_array bởi vì nó cần được giữ không đổi khi công thức được sao chép từ những ô khác Đối với lookup_value (B$1), bạn dùng hàm tham chiếu hỗn hợp, cột tương đối và hàng tuyệt đối bởi vì những giá trị tìm kiếm (tên hành tinh) nằm trên cùng một hàng (hàng 1) nhưng lại ở các cột khác nhau (từ B đến I), đồng thời cột tham chiếu nên thay đổi dựa vào vị trí tương đối 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 HLOOKUPNhư vậy, hàm HLOOKUP có một số hạn chế với nhược điểm lớn nhất đó là không thể thể tìm những giá trị nằm ngoài hàng đầu và phải xếp những giá trị theo thứ tự khi tìm kết quả khớp tương đối. Nhưng may mắn là sự kết hợp giữa hàm INDEX và MATCH sẽ giúp khắc phục được các điểm này: INDEX (nơi chứa giá trị về, MATCH (giá trị tìm kiếm, nơi tìm giá trị, 0)) Ví dụ như giá trị tìm kiếm ở ô B7, bạn đang tiến hành tìm một kết quả khớp ở hàng 2 (B2:I2), và mong muốn giá trị trả về từ hàng 1 (B1:I1), công thức thực hiện như sau \=INDEX (B1:I1, MATCH (B7, B2:I2, 0)) Tại hình minh họa dưới đây, bạn có thể nhìn thấy rằng trong cả 2 trường hợp INDEX MATCH đều đang hoạt động tốt. Cách sử dụng H-lookup phân biệt dạng chữ trong ExcelNhư chúng ta đã có đề cập lúc đầu, hàm HLOOKUP không thể phân biệt được chữ thường và in hoa. Trong một vài trường hợp khi dạng chữ của kí tự quan trọng, thì bạn có thể sử dụng hàm EXACT để so sánh những ô và đặt bên trong hàm INDEX MATCH như ở ví dụ trước: INDEX (hàng chứa giá trị trả về, MATCH (TRUE, EXACT (hàng tìm kiếm, giá trị tìm kiếm), 0)) Ví dụ giá trị tìm kiếm của bạn ở ô B4, và mảng tìm kiếm là B1:I1, mảng giá trị trả về là B2:I2, thì công thức lúc này có dạng như sau: \=INDEX (B2:I2, MATCH (TRUE, EXACT (B1:I1, B4), 0)) Lưu ý: Đây là công thức mảng vì thế bạn phải tiến hành ấn tổ 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
Ngay cả khi bạn quên toàn bộ các chi tiết về tìm kiếm theo hàng ngang trong Excel, thì hãy luôn nhớ rằng hàm Hlookup chỉ có thể tiến hành tìm ở hàng đầu tiên trong bảng. Nếu như những giá trị tìm kiếm nằm ở các hàng khác, thì lỗi N/A sẽ được trả lại. Để khắc phục được lỗi này, bạn nên dùng công thức INDEX/MATCH
Khi bạn đang tìm giá trị trong Excel, theo hàng ngang hay dọc, đa số các trường hợp là bạn đang tìm kiếm một giá trị cụ thể nên cần kết quả khớp chính xác. Khi tìm kiếm với kết quả khớp tương đối (range_lookup được đặt TRUE hoặc bỏ trống), các bạn nên nhớ xếp các giá trị trong hàng theo thứ tự tăng dần.
Khi sử dụng nhiều hàm HLOOKUP để lấy thông tin về hàng chứa giá trị tìm kiếm, thì bạn phải khoá tham chiếu table_array
Để có thể hiểu tại sao việc thêm một hàng mới có thể làm hỏng đi công thức Hlookup, thì bạn cần hiểu cách hàm HLOOKUP lấy thông tin về giá trị tìm kiếm, đó là dựa trên chỉ số của hàng mà bạn chỉ định Ví dụ bạn muốn lấy số liệu doanh số dựa trên ID sản phẩm. Các số liệu này nằm ở hàng 4 nên bạn đánh 4 vào tham số row_index_num. Tuy nhiên khi một hàng mới được thêm vào, thì nó sẽ trở thành hàng thứ 5 và hàm Hlookup không thể hoạt động được. Vấn đề này xảy ra tương tự như khi bạn thực hiện xoá bớt một hàng trong bảng. Cách giải quyết cho vấn đề này đó là khoá bảng để tránh việc người dùng thêm một hàng mới vào hoặc là dùng công thức INDEX/MATCH thay cho hàm Hlookup. Trong Index/Match, thì bạn cần chỉ rõ những hàng để tìm giá trị và trả giá trị về như mảng tham chiếu và Excel thì có thể tự điều chỉnh những tham chiếu này. Nhờ vậy, bạn cũng không cần phải lo lắng về vấn đề thêm hay là xoá bớt hàng như khi dùng công thức Hlookup.
Hàm HLOOKUP chỉ có thể trả về một giá trị là giá trị đầu tiên nằm trong bảng khớp với giá trị tìm kiếm. Nếu như có một vài giá trị giống nhau trong bảng, bạn hãy tiến hành chọn một trong những giải pháp mà phù hợp với bạn nhất:
Khi công thức hàm Hlookup hoàn toàn đúng nhưng lỗi N/A vẫn trả về, bạn hãy kiểm tra lại bảng và giá trị tìm kiếm xem là có kí tự trắng nào ở đấy không. Bạn sử dụng hàm TRIM để có thể nhanh chóng loại bỏ các khoảng trống.
Chuỗi văn bản cũng giống như số là một khó khăn khác khi dùng công thức Excel.
Tất cả những hàm tìm kiếm trong Excel sẽ chỉ hoạt động khi giá trị tìm kiếm nằm dưới 255 kí tự. Giá trị tìm kiếm dài hơn thì sẽ trả lại kết quả là lỗi VALUE!. Bởi vì hàm INDEX/MATCH không giới hạn số lượng kí tự nên bạn sẽ khắc phục lỗi này khi dùng hàm INDEX/MATCH
Nếu như bạn thực hiện hàm Hlookup từ một bảng tính khác, thì bạn nên nhớ rằng cần phải cung cấp đầy đủ đường dẫn tới nó.
Hàm Hlookup là một hàm phức tạp nên bạn cần nhớ rằng phải thật cẩn thận khi dùng. Dưới đây là một số lỗi phổ biến thường gặp khi nhập sai tham số |