HLOOKUP Function in Google Sheets: Guide bước đầu tiên

GiGiKing·Tháng tư 3, 2024·0 comments

Bạn muốn tìm kiếm một khóa qua hàng đầu tiên của một mảng 2D và trả về một giá trị từ một hàng cụ thể? Hãy sử dụng hàm HLOOKUP trong Google Sheets.

Ví dụ, hàng đầu tiên của dãy B1:L1000 chứa tên các tháng, chẳng hạn như “Tháng Một”, “Tháng Hai”, “Tháng Ba”, và cứ tiếp tục như vậy. Dưới hàng đó, chúng ta có thông tin về số lượng bán hàng trong các tháng tương ứng.

Ô A1 chứa nhãn trường “Tên”. Dưới ô đó, chúng ta có một vài tên người bán hàng. Một trong các tên đó là “Ben”, và ô đó nằm ở hàng 6.

Bạn có thể sử dụng khóa “Tháng Hai” trong hàm HLOOKUP để tìm kiếm qua hàng đầu tiên và trả về giá trị từ hàng 6 để biết số lượng bán hàng của Ben trong tháng Hai.

Ngược lại, để tìm kiếm theo cột đầu tiên cho tên “Ben” và trả về số lượng bán hàng của ông từ cột thứ hai, hãy sử dụng hàm VLOOKUP.

Đó không phải là hai hàm duy nhất trong bảng tính cho các thao tác tìm kiếm. Còn có các hàm khác như XLOOKUP, LOOKUP và kết hợp của các hàm INDEX và MATCH hoặc XMATCH.

MATCH và XMATCH là các hàm tìm kiếm. Chúng tìm kiếm chỉ mục hàng hoặc cột của một giá trị trong một phạm vi ô. INDEX là một hàm lệch. Nó trả về giá trị từ ô được lệch một số hàng và cột cố định từ ô được cho trước.

Trong bài hướng dẫn này, hãy tìm hiểu cách sử dụng hàm HLOOKUP trong Google Sheets.

HLOOKUP Function: Cú pháp và Đối số

Cú pháp của HLOOKUP trong Google Sheets:

HLOOKUP(search_key, range, index, [is_sorted])

Đối số:

  • Search_key: Giá trị mà bạn muốn tìm trong hàng đầu tiên của tập dữ liệu. Bạn có thể nhập trực tiếp vào công thức hoặc sử dụng tham chiếu ô để chỉ đến ô chứa giá trị.

  • Range: Phạm vi ô được xem xét để tìm kiếm. Sử dụng tham chiếu đến phạm vi hoặc tên phạm vi.

  • Index: Số hàng trong phạm vi từ đó giá trị phù hợp sẽ được trả về.

  • Is_sorted: Một giá trị logic xác định xem tìm kiếm là tìm kiếm chính xác hay tìm kiếm gần giống của search_key trong hàng đầu tiên. Đây là một đối số tùy chọn. Nếu bỏ qua, công thức sẽ sử dụng TRUE.

    • TRUE (trong phạm vi đã sắp xếp): Giá trị gần nhất (xấp xỉ).
    • FALSE (trong cả phạm vi đã sắp xếp hoặc không sắp xếp): Tìm kiếm chính xác.

Khi is_sorted là TRUE, nếu không tìm thấy kết quả chính xác trong hàng đầu tiên, giá trị gần nhất nhỏ hơn search_key sẽ được phù hợp.

Ví dụ về search_key trong HLOOKUP Function trong Google Sheets:

Trong bảng dưới đây, bạn có thể thấy cách sử dụng văn bản, ngày, thời gian và số lượng làm search_key trong hàm HLOOKUP.

HLOOKUP examples

Bạn cũng có thể sử dụng search_key là tham chiếu ô. Nhập key vào một ô và sau đó tham chiếu đến ô đó trong HLOOKUP function.

Tìm kiếm ngang trong một phạm vi chưa sắp xếp

  • Search_key: “Yumbilla Falls”
  • Range: A1:J4 (chưa sắp xếp)
  • Index: 4
  • Is_sorted: FALSE

Công thức Tìm kiếm ngang:

=HLOOKUP("Yumbilla Falls",A1:J4,4,false)

Công thức HLOOKUP trên tìm kiếm văn bản “Yumbilla Falls” trong phạm vi bảng A1:J4 và trả lại tên quốc gia từ hàng thứ tư.

Làm thế nào để hàm HLOOKUP trong Google Sheets trả về nhiều giá trị?

Chúng ta có thể sử dụng một hoặc nhiều chỉ mục hàng trong tham số thứ ba của hàm HLOOKUP trong Google Sheets. Để làm điều này, chúng ta có thể sử dụng dấu ngoặc nhọn để bao quanh các chỉ mục hàng mà chúng ta muốn trả về.

Ví dụ, công thức sau sẽ trả lại địa điểm và quốc gia cho thác nước có tên là “Yumbilla Falls”:

=ARRAYFORMULA(HLOOKUP("Yumbilla Falls",A1:J4,{3,4},false))

Hàm HLOOKUP không thể trả về nhiều giá trị một mình, vì vậy bạn cần sử dụng hàm ARRAYFORMULA để hỗ trợ việc này.

Đây là hướng dẫn liên quan: Cách trả về một cột toàn bộ bằng cách sử dụng hàm HLOOKUP trong Google Sheets.

Đừng lẫn lộn nếu bạn thấy các hàm HSTACK hoặc VSTACK trong tham số thứ ba của hàm HLOOKUP trong Google Sheets. Chúng có thể được sử dụng để thay thế cho dấu ngoặc nhọn. Ví dụ, bạn có thể thay thế {3,4} bằng HSTACK(3,4) và {3;4} bằng VSTACK(3,4).

Tìm kiếm ngang trong một phạm vi đã được sắp xếp

Trong Google Sheets, chúng ta có thời gian hẹn phỏng vấn trong phạm vi B1:F1 và tên ứng viên trong phạm vi B2:F2. Làm thế nào chúng ta sử dụng hàm HLOOKUP để tìm kiếm một thời gian và tìm tên ứng viên được chỉ định?

Ví dụ:

=HLOOKUP(D6,B1:F2,2,TRUE)

Khóa tìm kiếm nằm trong ô D6, là 13:00. Vì nó không có trong hàng đầu tiên, công thức sẽ tìm giá trị thời gian tiếp theo nhỏ hơn thời gian tìm kiếm, trong trường hợp này là 11:30.

Chúng ta đã sử dụng TRUE làm tham số cuối cùng trong công thức vì dữ liệu được sắp xếp. Điều này cho biết công thức phải phù hợp với giá trị tiếp theo nhỏ hơn hoặc bằng với khóa tìm kiếm, nếu không tìm thấy giá trị chính xác.

Chúng ta có thể sử dụng FALSE trong cả dữ liệu đã được sắp xếp và chưa được sắp xếp. Tuy nhiên, nếu sử dụng FALSE và không có khớp chính xác với khóa tìm kiếm, công thức sẽ trả về lỗi #N/A.

Nếu khóa tìm kiếm trong ô D6 là 15:00, kết quả sẽ là “Emily”, bất kể bạn chỉ định FALSE hoặc TRUE như tham số cuối cùng trong hàm HLOOKUP. Điều này là do giá trị 15:00 khớp chính xác với giá trị trong ô B5.

Wildcard trong hàm HLOOKUP trong Google Sheets

Wildcard là các ký tự đặc biệt có thể được sử dụng trong hàm HLOOKUP trong Google Sheets để phù hợp với bất kỳ số lượng ký tự nào hoặc một ký tự duy nhất.

Một điểm quan trọng mà bạn có thể không biết về việc sử dụng wildcards trong HLOOKUP là bạn phải xác định tham số cuối cùng, tức là is_sorted thành FALSE.

Dễ hiểu hơn, hàm HLOOKUP hỗ trợ hai wildcard sau: dấu sao (*) và dấu chấm hỏi (?).

Sử dụng dấu sao (*) để phù hợp với bất kỳ số lượng ký tự nào và dấu chấm hỏi (?) để phù hợp với bất kỳ ký tự duy nhất nào.

Với hai ví dụ sau đây, bạn có thể hiểu cách sử dụng wildcards trong hàm HLOOKUP trong Google Sheets.

Công thức HLOOKUP sau đây trong Bảng số 1 sẽ trả lại 50 từ hàng thứ hai.

=HLOOKUP("Wed*",A2:G3,2,FALSE)

Bạn có biết tại sao tôi sử dụng wildcard dấu sao trong hàm HLOOKUP này?

Tôi sử dụng nó vì tôi không chắc liệu hàng đầu tiên có chứa các từ viết tắt ngày (“Wed”) hay tên đầy đủ của ngày (“Wednesday”).

Wildcard dấu sao (*) phù hợp với bất kỳ số lượng ký tự nào, vì vậy trong trường hợp này, nó sẽ phù hợp với bất kỳ ô nào trong hàng đầu tiên của phạm vi A2:G3 bắt đầu bằng các chữ cái “Wed”.

Công thức HLOOKUP thứ hai trong Bảng số 2 sẽ trả lại 400.

=HLOOKUP("B?126",A7:C8,2,FALSE)

Tôi sử dụng wildcard dấu chấm hỏi trong công thức HLOOKUP này vì tôi không chắc về dấu phân cách được sử dụng trong các giá trị ô.

Wildcard dấu chấm hỏi (?) phù hợp với bất kỳ ký tự duy nhất nào, vì vậy trong trường hợp này, nó sẽ phù hợp với bất kỳ ô nào trong phạm vi A7:C8 bắt đầu bằng chữ “B”, tiếp theo là bất kỳ ký tự duy nhất và sau đó là số “126”.

Lưu ý: Chúng ta có thể sử dụng nhiều ký tự joker trong search_key.

Làm thế nào để làm sạch các giá trị lỗi trong hàm HLOOKUP trong Google Sheets

Một số lỗi thường gặp mà HLOOKUP trả về như sau:

  • LỖI #REF!

  • Phạm vi HLOOKUP nằm ngoài giới hạn: Kiểm tra xem số chỉ mục có đúng không. Trong một phạm vi 2 hàng, nếu bạn chỉ định bất kỳ số nào lớn hơn 2, lỗi này sẽ xảy ra.

  • Tham chiếu không tồn tại: Kiểm tra xem bạn đã xóa phạm vi được sử dụng trong hàm HLOOKUP không.

  • LỖI #VALUE!

  • Không tìm thấy giá trị mảng: Điều này thường xảy ra khi bạn sử dụng nhiều search_keys và quên sử dụng ARRAYFORMULA.

  • Lỗi #VALUE cũng có thể xảy ra khi bạn chỉ định 0 trong chỉ mục.

  • LỖI #N/A

  • Không tìm thấy giá trị ‘B?126’ trong quá trình đánh giá HLOOKUP: Không tìm thấy khóa tìm kiếm.

Bạn có thể bọc hàm HLOOKUP trong Google Sheets với hàm IFERROR để loại bỏ những lỗi đó. Tuy nhiên, tôi không khuyến nghị làm điều này, vì nó sẽ loại bỏ khả năng xử lý sự cố của bạn.

Thay vào đó, bạn có thể sử dụng hàm IFNA. Hàm này chỉ loại bỏ lỗi #N/A, vì vậy bạn vẫn có thể sửa chữa các lỗi khác.

Kết luận

Hy vọng bạn đã tìm thấy hướng dẫn bước đầu tiên này về hàm HLOOKUP hữu ích. Nếu bạn có bất kỳ câu hỏi hoặc ý kiến nào, hãy để lại chúng dưới đây.

Dưới đây là một số tài nguyên liên quan để phát triển thêm về hàm HLOOKUP.

  1. Kết hợp VLOOKUP và HLOOKUP trong Google Sheets
  2. HLOOKUP để tìm kiếm trong bảng toàn bộ và tìm tiêu đề trong Google Sheets
  3. Làm thế nào để thực hiện HLOOKUP nghịch đảo trong Google Sheets
  4. Làm thế nào để sử dụng nhiều điều kiện trong HLOOKUP trong Google Sheets
  5. Di chuyển một cột duy nhất thành nhiều cột bằng cách sử dụng HLOOKUP trong Google Sheets

GiGiKing

Related posts