Ứng dụng của hàm Hlookup trong Excel (phần 1)

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

Ứng dụng của hàm Hlookup trong Excel (phần 1)

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Hàm Hlookup được sử dụng để tìm một giá trị nhất định trong hàng trên cùng của bảng và trả lại một giá trị khác trong cùng cột từ hàng mà bạn chỉ định.

1. 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 trong Excel có thể tìm kết quả khớp chính xác và không chính xác dựa trên giá trị của tham số range_lookup

  • TRUE hoặc bỏ trống: kết quả khớp tương đối
  • FALSE: kết quả khớp chính xác

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

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

  • HLOOKUP với kết quả khớp tương đối

Giả sử bạn có một danh sách các hành tinh ở hàng 2 (B2:I2) và nhiệt độ của chúng ở hàng 1 (B1:I1). Bạn muốn tìm hành tinh có nhiệt độ bằng nhiệt độ ở ô B4.

Trong trường hợp bạn không biết chính xác nhiệt độ cần tìm, bạn có thể xây dựng hàm Hlookup để đưa ra kết quả khớp gần nhất nếu giá trị chính xác không được tìm thấy.

Ví dụ, để biết hành tinh nào có nhiệt độ trung bình vào khoảng -340°F, 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 nhớ rằng kết quả khớp tương đối yêu cầu sắp xếp các giá trị ở hàng đầu tiên từ nhỏ nhất đến lớn nhất hoặc từ A đến Z, nếu không hàm Hlookup sẽ đưa ra kết quả sai.

Trong hình chụp dưới đây, hàm trả về hành tinh Hải Vương, một trong những 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.

  • Hlookup với kết quả khớp chính xác

Nếu bạn biết chính xác giá trị cần tìm, bạn có thể đặt tham số cuối cùng của hàm Hlookup là FALSE:

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

Một mặt, kết quả khớp tường dối dễ dàng sử dụng hơn vì nó không yêu cầu sắp xếp dữ liệu ở hàng đầu tiên. Mặt khác, nếu kết quả khớp chính xác không được tìm thấy, lỗi #N/A sẽ được trả về.

Tip Để giúp người dùng không cảm thấy hoảng sợ khi nhìn thấy lỗi N/A xuất hiện, bạn có thể đưa hàm Hlookup vào hàm Inferror và 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ả”)

2. Cách dùng hàm Hlookup từ worksheet hoặc workbook khác

Nhìn chung, tìm giá trị theo hàng từ một trang tính hoặc một bảng tính khác nghĩa là bạn phải cung cấp các tham chiếu ngoại tuyến cho hàm HLOOKUP.

Để lấy giá trị khớp từ một trang tính khác, bạn phải chỉ rõ tên của trang tính đặt trước dấu “!”. Ví dụ:

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

Cách dùng hàm Hlookup từ worksheet hoặc workbook khác

Cách dùng hàm Hlookup từ worksheet hoặc workbook khác

Nếu tên trang tính chứa dấu cách hoặc kí tự không phải chữ cái, đặt nó trong dấu ngoặc đơn như sau:

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

Khi tham chiếu từ bảng tính khác, đặ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 bạn muốn lấy giá trị từ một bảng tính đóng, bạn cầ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 vì đánh tên bảng tính và trang tính bằng tay, bạn có thể chọn các ô trong trang tính khác và Excel sẽ thêm tham chiếu ngoại tuyến vào hàm của bạn một cách tự động

Hàm Hlookup với kết quả khớp một phần (sử dụng kí tự đại diện)

Như trong trường hợp của Vlookup, hàm HLOOKUP cho phép sử dụng các kí tự đại diện sau cho tham số lookup_value:

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

Các kí tự đại diện rất hữu ích khi bạn muốn lấy thông tin từ một cơ sở dữ liệu dựa trên một vài đoạn văn bản nằm trong nội dung của ô tìm kiếm

Ví dụ, bạn có một danh sách tên các khách hàng ở hàng 1 và số order ở hàng 2. Bạn muốn tìm số order của một khách hàng nhất định nào đó nhưng bạn không thể nhớ tên chính xác vị khách mà chỉ nhớ nó bắt đầu với chữ “La”.

Cho rằng dữ liệu của bạn ở ô B1:I2 (table_array) và thứ tự các số ở hàng 2 (row_index_num), công thức như sau:

=HLOOKUP (“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 vào một ô cụ thể, ví dụ B4, và gắn vào ô đó kí tự đại diện như sau:

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

Lưu ý

  1. Để hàm HLOOKUP với kí tự đại diện đưa kết quả đúng, range_lookup phải được đặt chế độ FALSE
  2. Nếu table_array chứa nhiều hơn một giá trị khớp với kí tự đại diện, giá trị đầu tiên tìm được sẽ được trả về.

Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ