Khi bạn mới bắt đầu làm quen với Excel, bạn sẽ sớm gặp một vài hàm then chốt để tham chiếu và tìm kiếm dữ liệu. Trong hầu hết trường hợp, đó sẽ là các hàm tra cứu trong Excel.

Trong bài viết này, chúng tôi sẽ giới thiệu các hàm liên quan đến việc tra cứu thông tin trong Excel và giải thích cách sử dụng chúng qua các ví dụ. Nếu bạn muốn nâng cao kỹ năng Excel ở mọi mảng, đừng quên xem lộ trình kỹ năng Excel Fundamentals của chúng tôi.

Hàm tra cứu trong Excel là gì?

Các hàm tra cứu trong Excel cho phép người dùng tìm kiếm dữ liệu cụ thể trong một tập dữ liệu và trả về thông tin tương ứng từ một cột hoặc hàng khác. Chúng rất quan trọng trong phân tích dữ liệu để tham chiếu, đối chiếu chéo và trích xuất insight có ý nghĩa từ các tập dữ liệu lớn hoặc phức tạp.

Vì sao các hàm tra cứu quan trọng? 

Các hàm tra cứu rất hữu ích để truy xuất dữ liệu hiệu quả. Việc truy xuất dữ liệu hiệu quả là then chốt cho năng suất, đặc biệt khi làm việc với bảng tính lớn hoặc tự động hóa quy trình báo cáo. Các hàm tra cứu giúp giảm tìm kiếm thủ công, đảm bảo tính nhất quán và cho phép giải pháp dữ liệu có thể mở rộng.

Ví dụ, hãy tưởng tượng bạn có bảng dữ liệu bán hàng với hàng nghìn hàng và cột, và bạn cần tìm tổng doanh số cho một sản phẩm hoặc khu vực cụ thể. 

Nếu không có các hàm tra cứu, bạn sẽ phải tự tay dò từng hàng để tìm thông tin liên quan, vừa tốn thời gian vừa dễ sai sót.

Tìm hiểu sâu hơn về các hàm này trong khóa học Data Preparation in Excel của chúng tôi.

Các loại hàm tra cứu trong Excel

Excel cung cấp nhiều hàm tra cứu, mỗi hàm có thế mạnh riêng phù hợp với các trường hợp sử dụng khác nhau. Hiểu sự khác biệt giữa các hàm này có thể cải thiện đáng kể khả năng quản lý và phân tích dữ liệu hiệu quả.

Hãy cùng xem một số loại hàm phổ biến dưới đây.

1. LOOKUP

Nguồn: Microsoft

Hàm tra cứu nguyên bản này là cơ bản nhất và hoạt động với các mảng đã sắp xếp. Nó tìm một giá trị trong một hàng hoặc cột đơn và trả về giá trị ở cùng vị trí trong một hàng hoặc cột khác. Chỉ hỗ trợ khớp gần đúng.

2. VLOOKUP (Tra cứu dọc)

Nguồn: Microsoft

VLOOKUP là một trong những hàm được sử dụng rộng rãi nhất trong các sổ làm việc Excel cũ. Nó tìm một giá trị ở cột đầu tiên của một bảng và trả về giá trị ở cùng hàng từ cột được chỉ định. 

Hàm hỗ trợ cả khớp chính xác và gần đúng nhưng có hạn chế như không thể tra cứu sang bên trái và vấn đề hiệu năng với tập dữ liệu lớn.

3. HLOOKUP (Tra cứu ngang)

Nguồn: HLOOKUP Tutorial

HLOOKUP hoạt động tương tự VLOOKUP nhưng tìm một giá trị ở hàng đầu tiên của bảng và trả về giá trị từ hàng được chỉ định. Hữu ích khi dữ liệu của bạn được tổ chức theo chiều ngang thay vì dọc.

Bạn nên cân nhắc dùng HLOOKUP thay vì VLOOKUP khi dữ liệu được tổ chức theo hàng thay vì cột.

4. INDEX và MATCH

Phương pháp INDEX và MATCH sử dụng kết hợp mạnh mẽ của hai hàm:

• INDEX trả về giá trị của một ô dựa trên số hàng và cột.
• MATCH trả về vị trí của một giá trị trong một hàng hoặc cột. Kết hợp với nhau, chúng linh hoạt hơn VLOOKUP và được ưa chuộng trong các tình huống phức tạp vì hỗ trợ tra cứu trái–phải và phải–trái, đồng thời không yêu cầu dữ liệu đã sắp xếp.

5. XLOOKUP

Giới thiệu trong Excel 2019 và Microsoft 365, XLOOKUP là sự thay thế hiện đại cho VLOOKUP và HLOOKUP. Nó cho phép khớp chính xác hoặc gần đúng, hoạt động theo cả chiều ngang và dọc, và hỗ trợ xử lý lỗi, khiến nó trở thành hàm tra cứu đa năng nhất hiện nay.

Hàm LOOKUP

Bắt đầu bằng việc khám phá hàm LOOKUP trong Excel, bao gồm cú pháp và tham số, dạng vector và một ví dụ minh họa. 

Cú pháp và tham số

=LOOKUP(lookup_value, lookup_vector, [result_vector])

Giải thích một số thành phần:

• lookup_value: Giá trị cần tìm.
• lookup_vector: Hàng hoặc cột đơn để tìm kiếm.
• result_vector (tùy chọn): Vùng chứa giá trị cần trả về.

Sử dụng dạng vector

Dạng vector thường được dùng và yêu cầu cả lookup_vector và result_vector có cùng kích thước.

Giải thích với ví dụ

=LOOKUP(90, A2:A10, B2:B10)

Hàm này tìm giá trị lớn nhất nhỏ hơn hoặc bằng 90 trong A2:A10, rồi trả về giá trị tương ứng từ B2:B10.

Trong trường hợp này, chỉ hàng 5 khớp với các tham số đã đặt. Do đó, đầu ra của hàm sẽ là kết quả “Medium-High”.

Hạn chế của hàm LOOKUP

• Yêu cầu dữ liệu đã sắp xếp: lookup_vector phải được sắp xếp tăng dần.
• Hành vi khớp gần đúng: Không hỗ trợ khớp chính xác—trả về giá trị gần nhất nhỏ hơn hoặc bằng lookup_value.

Hàm VLOOKUP

Tiếp theo là hàm VLOOKUP, một trong những hàm được dùng phổ biến nhất trong các phiên bản Excel cũ. 

Cú pháp và tham số

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: Giá trị cần tìm ở cột đầu tiên của table_array.
• table_array: Vùng bảng để tìm kiếm.
• col_index_num: Số thứ tự cột để trả về giá trị.
• range_lookup: Tùy chọn; TRUE cho khớp gần đúng, FALSE cho khớp chính xác.

Cách dùng VLOOKUP: Hướng dẫn từng bước với ví dụ

Bây giờ, hãy nhanh chóng xem hàm này hoạt động thế nào qua một ví dụ.

Chúng ta sẽ dùng hàm sau trong một tập dữ liệu đơn giản.

=VLOOKUP("John", A2:C10, 3, FALSE)

Hàm này sẽ tìm các mục thuộc về John và trả về mức lương của anh ấy.

Tìm "John" trong cột A và trả về giá trị từ cột thứ ba (cột C) ở cùng hàng.

Đúng như kỳ vọng, kết quả VLOOKUP là 6000, trùng với hàng 6, chính là giá trị chúng ta cần vì thuộc về John.

Khớp chính xác so với khớp gần đúng

Khớp chính xác là kiểu khớp được dùng nhiều nhất trong VLOOKUP. Điều này có nghĩa hàm chỉ trả về kết quả nếu tìm thấy giá trị cần tra cứu khớp chính xác ở cột đầu tiên của vùng xác định.

Ví dụ, nếu dùng khớp chính xác khi tìm lương của John, hàm VLOOKUP chỉ trả về kết quả nếu có một hàng chứa "John" ở cột đầu tiên và mức lương của anh ấy ở cột thứ hai.

Ngược lại, khớp gần đúng cho phép linh hoạt hơn khi tìm giá trị. Điều này hữu ích khi làm việc với dữ liệu số hoặc dữ liệu có thể có sai khác nhỏ (ví dụ do làm tròn hoặc định dạng).

Tóm lại:

• Chính xác (FALSE): Tìm khớp tuyệt đối.
• Gần đúng (TRUE): Yêu cầu dữ liệu đã sắp xếp và tìm giá trị gần nhất.

Lỗi thường gặp và cách khắc phục

Khi mới làm quen với VLOOKUP, một số lỗi phổ biến có thể xuất hiện. 

Xử lý lỗi #N/A

Một trong những lỗi thường gặp nhất khi dùng VLOOKUP là #N/A, viết tắt của "Not Available". Lỗi này thường xảy ra khi giá trị cần tìm không có trong vùng chỉ định.

Nguyên nhân cũng có thể do thiếu lookup_value hoặc dữ liệu chưa được sắp xếp khi dùng khớp gần đúng.

Để khắc phục, hãy kiểm tra lại chính tả của giá trị tra cứu và đảm bảo nó tồn tại ở cột đầu tiên của vùng bạn đang tìm. Đồng thời, nếu dùng khớp gần đúng, hãy chắc chắn dữ liệu được sắp xếp tăng dần.

Một vấn đề khác có thể là không khớp kiểu dữ liệu. Ví dụ, nếu một cột chứa số ở dạng văn bản còn cột khác chứa số thực, VLOOKUP có thể hoạt động không đúng.

Vấn đề với số thứ tự cột

Một lỗi khác có thể xảy ra là lỗi #REF!. Nếu col_index_num vượt quá số cột trong table_array, sẽ phát sinh lỗi #REF!.

Hàm HLOOKUP

Để tìm một giá trị ở hàng trên cùng của bảng và trả về giá trị từ một hàng được chỉ định trong cùng cột, hàm HLOOKUP thường là lựa chọn tốt nhất. Hãy xem nó hoạt động thế nào.

Cú pháp và tham số

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• lookup_value: Giá trị cần tìm ở hàng đầu tiên.
• table_array: Bảng cần tìm kiếm.
• row_index_num: Số thứ tự hàng để trả về giá trị.
• range_lookup: TRUE (gần đúng) hoặc FALSE (chính xác).

Cách dùng HLOOKUP: Hướng dẫn từng bước với ví dụ

=HLOOKUP("Q1", A1:D3, 2, FALSE)

Hàm HLOOKUP trên tìm "Q1" ở hàng 1 và trả về giá trị từ hàng 2 trong cùng cột. 

Hãy chạy hàm và xem kết quả.

Như bạn thấy ở trên, hàm sẽ trả về 1200. Vì hàm tìm giá trị ở hàng 2 dựa trên giá trị xác định ở hàng 1, 1200 là kết quả đúng mà chúng ta cần.

Các hàm INDEX và MATCH

Tiếp theo, hãy xem cách các hàm INDEX và MATCH trong Excel kết hợp để tra cứu giá trị. 

Cú pháp và ví dụ sử dụng

=INDEX(array, row_num, [column_num])

Hàm INDEX trả về giá trị tại hàng và cột cụ thể trong một vùng cho trước.

=MATCH(lookup_value, lookup_array, [match_type])

Hàm MATCH trả về vị trí tương đối của lookup_value trong lookup_array.

Dùng INDEX và MATCH cùng nhau

Khi kết hợp cả hai hàm INDEX và MATCH, bạn có một công cụ mạnh mẽ để tra cứu giá trị trong bảng hoặc vùng. Bộ đôi này cho phép bạn chỉ định chính xác vị trí của giá trị cần trả về, thay vì phải nhập thủ công số hàng và cột.

Hãy xem một ví dụ:

=INDEX(B2:B10, MATCH("John", A2:A10, 0))

Tổ hợp này tìm hàng có "John" xuất hiện trong A2:A10, rồi trả về giá trị tương ứng từ B2:B10.

Hãy xem hàm sẽ cho ra kết quả gì.

Khi dùng chung hai hàm, bạn đạt được kết quả tương tự như dùng VLOOKUP. Tuy nhiên, việc dùng INDEX và MATCH linh hoạt hơn trong việc chọn cột cần trả về, cũng như có thể tìm kiếm ở nhiều cột.

Hàm XLOOKUP (Excel 2019 trở đi)

Cuối cùng, hãy xem một trong những hàm tra cứu mới nhất của Excel, XLOOKUP.

Cú pháp và tham số

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Ví dụ thực tiễn

Chúng ta sẽ áp dụng hàm trên một tập dữ liệu và ví dụ đơn giản.

=XLOOKUP("John", A2:A10, B2:B10, "Not Found")

Hàm trên tìm "John" trong A2:A10 và trả về giá trị từ B2:B10; trả về "Not Found" nếu không tìm thấy.

Quan sát kết quả từ công thức trên, chúng ta thấy đầu ra “Not Found” được hiển thị. Điều này có nghĩa không có bản ghi nào với tên nhân viên “John”.

So sánh: LOOKUP vs. VLOOKUP vs. XLOOKUP

Hãy đặt các hàm này cạnh nhau để xem chúng khác nhau như thế nào.

Kết luận

Các hàm tra cứu là công cụ thiết yếu trong Excel để truy xuất dữ liệu hiệu quả. Dù LOOKUP, VLOOKUP và HLOOKUP có đất dụng võ của riêng mình, INDEX/MATCH và XLOOKUP cung cấp nhiều linh hoạt và sức mạnh hơn. Hiểu khi nào và cách dùng mỗi hàm có thể nâng cao đáng kể hiệu quả bảng tính và năng lực phân tích của bạn.

Nếu bạn muốn tìm thêm tài liệu về Excel, khóa học Data Preparation in Excel của chúng tôi là điểm khởi đầu tuyệt vời. Để nắm vững nền tảng Excel, lộ trình kỹ năng Excel Fundamentals là nơi tốt nhất để bắt đầu. Bạn cũng có thể xem các hướng dẫn Excel của chúng tôi về Các công thức Excel cơ bản hoặc Vlookup từ trang tính khác trong Excel nữa.