Trong thời đại công nghệ số, Excel đã trở thành một công cụ không thể thiếu trong công việc hàng ngày của nhiều người. Đặc biệt, khi phải xử lý các bảng dữ liệu lớn, việc tìm kiếm và trích xuất thông tin chính xác từ hàng ngàn dòng dữ liệu có thể trở thành một cơn ác mộng. Đừng lo lắng! Excel cung cấp một bộ công cụ mạnh mẽ dưới dạng các hàm tra cứu dữ liệu như VLOOKUP, HLOOKUP, INDEX, MATCH, và XLOOKUP. Những hàm này chính là “siêu năng lực” giúp bạn biến những con số lộn xộn thành thông tin có giá trị một cách nhanh chóng và chính xác.
Trong bài viết này, chúng ta sẽ cùng khám phá từng hàm tra cứu dữ liệu trong Excel, từ những điều cơ bản nhất cho người mới bắt đầu, đến những kỹ thuật nâng cao dành cho các chuyên gia phân tích dữ liệu. Bạn sẽ học được cách sử dụng chúng, biết khi nào nên dùng hàm nào, và quan trọng nhất, là cách tránh những lỗi phổ biến mà ai cũng có thể mắc phải. Hãy cùng bắt đầu hành trình chinh phục Excel nhé!
Có thể bạn quan tâm: Game Vui Ben 10 Giải Cứu Thế Giới 2: Link Tải Miễn Phí + Hướng Dẫn Cài Đặt Chi Tiết
Giới thiệu về các hàm tra cứu dữ liệu trong Excel
Trước khi đi vào chi tiết từng hàm, hãy cùng tìm hiểu hàm tra cứu dữ liệu trong Excel là gì và tại sao chúng lại quan trọng đến vậy.
<>Xem Thêm Bài Viết:<>- Giftcode Vĩnh Hằng Kỷ Nguyên 2019: Danh sách Code Mới Nhất + Hướng Dẫn Nhập
- Mẫu Chữ Viết Giấy Khen Đẹp – Download Trọn Bộ Font Chữ Khen Thưởng Miễn Phí
- Tải hình ảnh bé gái dễ thương miễn phí, chất lượng cao
- Khách sạn Satya Đà Nẵng Hotel: Điểm Đến Nghỉ Dưỡng Lý Tưởng
- Bản Đồ Quy Hoạch Nhơn Mỹ Chợ Mới An Giang: Tầm Nhìn 2050
Hàm tra cứu dữ liệu là gì?
Hàm tra cứu dữ liệu là một nhóm các hàm trong Excel cho phép bạn tìm kiếm một giá trị cụ thể trong một dải ô (mảng dữ liệu) và trả về một giá trị tương ứng từ cùng một hàng hoặc cột. Nói một cách đơn giản, bạn cung cấp một “từ khóa” (giá trị cần tìm), Excel sẽ “lật” qua các bảng tính để tìm từ khóa đó, và sau đó trả về “thông tin” (giá trị bạn muốn biết) liên quan đến từ khóa đó.
Vì sao cần sử dụng hàm tra cứu?
- Tiết kiệm thời gian: Thay vì phải cuộn qua hàng ngàn dòng dữ liệu để tìm kiếm thông tin, bạn chỉ cần nhập một công thức duy nhất.
- Tăng độ chính xác: Con người có thể mắc sai sót khi tìm kiếm thủ công, nhưng một công thức Excel sẽ luôn trả về kết quả chính xác nếu được viết đúng.
- Tự động hóa: Khi dữ liệu nguồn được cập nhật, kết quả tra cứu cũng sẽ tự động cập nhật theo, giúp báo cáo của bạn luôn luôn mới nhất.
- Tích hợp linh hoạt: Các hàm tra cứu có thể được kết hợp với nhau hoặc với các hàm khác để tạo ra các công thức phức tạp, giải quyết những bài toán phân tích dữ liệu khó khăn.
Các hàm tra cứu phổ biến
Dưới đây là những cái tên “nổi tiếng” nhất trong thế giới hàm tra cứu dữ liệu:
- VLOOKUP: Tìm kiếm theo chiều dọc (theo cột).
- HLOOKUP: Tìm kiếm theo chiều ngang (theo hàng).
- INDEX + MATCH: Một bộ đôi linh hoạt và mạnh mẽ.
- XLOOKUP: Hàm mới, hiện đại, thay thế cho VLOOKUP và HLOOKUP.
Tìm hiểu chi tiết từng hàm tra cứu dữ liệu trong Excel
1. Hàm VLOOKUP – Tìm kiếm theo chiều dọc
Có thể bạn quan tâm: Bài Thi Trắc Nghiệm Bằng Lái Xe B2 – Download Phần Mềm Học Lý Thuyết Miễn Phí
VLOOKUP (Vertical Lookup) là hàm được sử dụng phổ biến nhất trong Excel. Nó hoạt động bằng cách tìm kiếm một giá trị trong cột đầu tiên của một bảng dữ liệu (bảng tra cứu), sau đó trả về một giá trị ở cùng hàng nhưng ở một cột khác mà bạn chỉ định.
Cú pháp
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Giải thích các đối số:
- lookup_value: Giá trị cần tìm kiếm. Đây thường là một ô tham chiếu hoặc một giá trị cụ thể.
- table_array: Bảng dữ liệu chứa thông tin cần tra cứu. Đây là dải ô bao gồm cả cột chứa giá trị cần tìm và các cột chứa giá trị cần trả về.
- colindexnum: Số thứ tự của cột trong tablearray mà bạn muốn trả về giá trị. Cột đầu tiên của tablearray là cột số 1.
- [rangelookup]: Là một giá trị logic (TRUE hoặc FALSE) để xác định kiểu tra cứu.
- FALSE: Tra cứu chính xác (exact match). Hàm sẽ tìm giá trị lookupvalue chính xác và trả về giá trị tương ứng.
- TRUE hoặc bỏ trống: Tra cứu gần đúng (approximate match). Hàm sẽ tìm giá trị lookupvalue hoặc giá trị gần nhất nhỏ hơn nó. Lưu ý: Cột đầu tiên của tablearray phải được sắp xếp theo thứ tự tăng dần khi sử dụng kiểu tra cứu này.
Ví dụ minh họa
Giả sử bạn có một bảng dữ liệu Bảng 1 như sau:
| A (Mã SP) | B (Tên SP) | C (Đơn giá) |
|---|---|---|
| SP001 | Bút bi | 5000 |
| SP002 | Sách vở | 20000 |
| SP003 | Máy tính | 1000000 |
Bạn muốn tìm Đơn giá của sản phẩm có Mã SP là “SP002”.
Công thức cần dùng:
=VLOOKUP("SP002", A2:C4, 3, FALSE)
Giải thích:
- “SP002” là giá trị cần tìm (lookup_value).
- A2:C4 là bảng dữ liệu (table_array), bao gồm cột A, B, C.
- 3 là số thứ tự của cột cần trả về giá trị (cột C – Đơn giá).
- FALSE là để tìm kiếm chính xác.
Kết quả trả về: 20000.
Những lưu ý quan trọng khi dùng VLOOKUP
- Cột tìm kiếm phải là cột đầu tiên: VLOOKUP chỉ tìm kiếm giá trị trong cột đầu tiên của table_array. Nếu giá trị cần tìm nằm ở cột thứ 2, 3, hoặc bất kỳ cột nào khác, VLOOKUP sẽ không thể hoạt động.
- Tham chiếu tuyệt đối: Khi sao chép công thức xuống các ô khác, bạn nên sử dụng tham chiếu tuyệt đối ($) cho table_array để tránh sai sót. Ví dụ: =VLOOKUP(A2, $D$2:$F$100, 3, FALSE).
- Tra cứu gần đúng: Khi sử dụng TRUE hoặc để trống đối số cuối cùng, hãy đảm bảo rằng cột đầu tiên được sắp xếp tăng dần. Nếu không, kết quả có thể không chính xác.
- Xử lý lỗi: Nếu VLOOKUP không tìm thấy giá trị cần tìm, nó sẽ trả về lỗi #N/A. Bạn có thể sử dụng hàm IFERROR để xử lý lỗi này. Ví dụ: =IFERROR(VLOOKUP(A2, B2:C10, 2, FALSE), “Không tìm thấy”).
2. Hàm HLOOKUP – Tìm kiếm theo chiều ngang
HLOOKUP (Horizontal Lookup) hoạt động tương tự như VLOOKUP, nhưng thay vì tìm kiếm theo chiều dọc (cột), nó tìm kiếm theo chiều ngang (hàng). Hàm này hữu ích khi dữ liệu của bạn được tổ chức theo hàng thay vì cột.
Cú pháp
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Giải thích các đối số:
- lookup_value: Giá trị cần tìm kiếm.
- table_array: Bảng dữ liệu chứa thông tin cần tra cứu.
- rowindexnum: Số thứ tự của hàng trong tablearray mà bạn muốn trả về giá trị. Hàng đầu tiên của tablearray là hàng số 1.
- [range_lookup]: Tương tự như VLOOKUP, có thể là TRUE (gần đúng) hoặc FALSE (chính xác).
Ví dụ minh họa
Giả sử bạn có một bảng dữ liệu như sau:
| A | B | C | D |
|---|---|---|---|
| Tháng | 1 | 2 | 3 |
| Doanh thu | 10000000 | 12000000 | 15000000 |
Bạn muốn tìm Doanh thu của Tháng 2.
Công thức cần dùng:
=HLOOKUP(2, A1:D2, 2, FALSE)
Giải thích:
- 2 là giá trị cần tìm (lookup_value).
- A1:D2 là bảng dữ liệu (table_array).
- 2 là số thứ tự của hàng cần trả về giá trị (hàng thứ 2 – Doanh thu).
- FALSE là để tìm kiếm chính xác.
Kết quả trả về: 12000000.
Khi nào nên dùng HLOOKUP?
- Khi dữ liệu được tổ chức theo hàng (hàng đầu tiên là tiêu đề, các hàng tiếp theo là dữ liệu).
- Khi bạn cần tìm kiếm một giá trị nằm ở hàng đầu tiên và trả về giá trị ở cùng cột nhưng ở một hàng khác.
3. Hàm INDEX + MATCH – Sự kết hợp linh hoạt
Nếu VLOOKUP và HLOOKUP là những công cụ “một chiều”, thì INDEX + MATCH chính là “siêu xe thể thao” trong thế giới hàm tra cứu. Sự kết hợp này mang lại sự linh hoạt vượt trội, cho phép bạn tìm kiếm theo bất kỳ hướng nào và không bị giới hạn bởi vị trí của cột tìm kiếm.
Cú pháp
=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]))
Giải thích các đối số:
- INDEX(array, rownum, [columnnum]):
- array: Dải ô (mảng) mà bạn muốn trả về giá trị.
- row_num: Số thứ tự của hàng trong array mà bạn muốn trả về giá trị.
- column_num: Số thứ tự của cột trong array mà bạn muốn trả về giá trị (nếu array là một phạm vi nhiều cột).
- MATCH(lookupvalue, lookuparray, [match_type]):
- lookup_value: Giá trị cần tìm kiếm.
- lookup_array: Dải ô (mảng) mà bạn muốn tìm kiếm giá trị trong đó.
- [match_type]: Loại khớp:
- 1 (mặc định): Tìm giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value. Dải ô phải được sắp xếp tăng dần.
- 0: Tìm giá trị chính xác bằng lookup_value. Dải ô không cần sắp xếp.
- -1: Tìm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value. Dải ô phải được sắp xếp giảm dần.
Ví dụ minh họa
Sử dụng lại bảng dữ liệu ở phần VLOOKUP:
| A (Mã SP) | B (Tên SP) | C (Đơn giá) |
|---|---|---|
| SP001 | Bút bi | 5000 |
| SP002 | Sách vở | 20000 |
| SP003 | Máy tính | 1000000 |
Bạn muốn tìm Tên SP của sản phẩm có Mã SP là “SP002”.
Công thức cần dùng:
=INDEX(B2:B4, MATCH("SP002", A2:A4, 0))
Giải thích:
- MATCH(“SP002”, A2:A4, 0) sẽ tìm giá trị “SP002” trong dải A2:A4 và trả về vị trí dòng (2).
- INDEX(B2:B4, 2) sẽ lấy giá trị ở dòng thứ 2 của dải B2:B4, đó là “Sách vở”.
Kết quả trả về: Sách vở.
Ưu điểm vượt trội của INDEX + MATCH
- Tìm kiếm theo bất kỳ hướng nào: Bạn có thể tìm kiếm theo cột và trả về giá trị theo hàng, hoặc ngược lại.
- Không bị giới hạn bởi vị trí cột: Cột tìm kiếm có thể ở bất kỳ đâu trong bảng dữ liệu, không nhất thiết phải là cột đầu tiên.
- Hiệu suất tốt hơn: Với các bảng dữ liệu lớn, INDEX + MATCH thường nhanh hơn VLOOKUP.
- An toàn khi chèn cột: Nếu bạn chèn thêm một cột vào giữa bảng dữ liệu, công thức INDEX + MATCH vẫn hoạt động chính xác, trong khi VLOOKUP có thể trả về sai cột.
4. Hàm XLOOKUP – Hàm tra cứu hiện đại
XLOOKUP là một trong những hàm mới nhất được Microsoft giới thiệu, và nó được xem là “người kế nhiệm” hoàn hảo cho VLOOKUP và HLOOKUP. XLOOKUP giải quyết hầu hết các hạn chế của các hàm tra cứu truyền thống, mang lại trải nghiệm người dùng tốt hơn và công thức đơn giản hơn.
Cú pháp
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Giải thích các đối số:
- lookup_value: Giá trị cần tìm kiếm.
- lookup_array: Dải ô (mảng) mà bạn muốn tìm kiếm giá trị trong đó.
- return_array: Dải ô (mảng) mà bạn muốn trả về giá trị.
- [ifnotfound]: Giá trị trả về nếu không tìm thấy giá trị cần tìm. (Tùy chọn)
- [match_mode]: Loại khớp:
- 0 (mặc định): Tìm giá trị chính xác.
- -1: Tìm giá trị chính xác hoặc giá trị nhỏ hơn gần nhất.
- 1: Tìm giá trị chính xác hoặc giá trị lớn hơn gần nhất.
- 2: Tìm giá trị chính xác bằng biểu thức chính quy (wildcard).
- [search_mode]: Chế độ tìm kiếm:
- 1 (mặc định): Tìm kiếm từ đầu đến cuối (1 đến cuối).
- -1: Tìm kiếm từ cuối về đầu (cuối về 1).
- 2: Tìm kiếm nhị phân (tăng dần).
- -2: Tìm kiếm nhị phân (giảm dần).
Ví dụ minh họa
Sử dụng lại bảng dữ liệu ở phần VLOOKUP:
| A (Mã SP) | B (Tên SP) | C (Đơn giá) |
|---|---|---|
| SP001 | Bút bi | 5000 |
| SP002 | Sách vở | 20000 |
| SP003 | Máy tính | 1000000 |
Bạn muốn tìm Tên SP của sản phẩm có Mã SP là “SP002”.
Công thức cần dùng:
=XLOOKUP("SP002", A2:A4, B2:B4)
Giải thích:
- “SP002” là giá trị cần tìm.
- A2:A4 là dải ô chứa giá trị cần tìm.
- B2:B4 là dải ô chứa giá trị cần trả về.
Kết quả trả về: Sách vở.
Những lợi ích tuyệt vời của XLOOKUP
- Công thức đơn giản: Không cần phải đếm số thứ tự cột như VLOOKUP.
- Tìm kiếm hai chiều: Có thể tìm kiếm theo chiều dọc hoặc chiều ngang mà không cần thay đổi cú pháp.
- Tự động xử lý lỗi: Bạn có thể dễ dàng chỉ định giá trị trả về khi không tìm thấy (đối số [if_not_found]).
- Tìm kiếm ngược: Có thể tìm kiếm từ cuối danh sách về đầu danh sách.
- Tìm kiếm gần đúng linh hoạt: Hỗ trợ nhiều chế độ khớp khác nhau.
So sánh các hàm tra cứu dữ liệu trong Excel
Để giúp bạn dễ dàng lựa chọn hàm phù hợp với nhu cầu của mình, dưới đây là một bảng so sánh nhanh giữa các hàm tra cứu:
| Tiêu chí | VLOOKUP | HLOOKUP | INDEX + MATCH | XLOOKUP |
|---|---|---|---|---|
| Hướng tìm kiếm | Theo cột (dọc) | Theo hàng (ngang) | Linh hoạt (cột hoặc hàng) | Linh hoạt (cột hoặc hàng) |
| Cột tìm kiếm | Phải là cột đầu tiên | Phải là hàng đầu tiên | Có thể ở bất kỳ đâu | Có thể ở bất kỳ đâu |
| Công thức | Trung bình | Trung bình | Phức tạp hơn | Đơn giản nhất |
| Hiệu suất | Tốt | Tốt | Tốt hơn | Tốt nhất |
| Xử lý lỗi | Cần dùng IFERROR | Cần dùng IFERROR | Cần dùng IFERROR | Có sẵn [ifnotfound] |
| Tìm kiếm ngược | Không | Không | Có (kết hợp với các hàm khác) | Có |
| Tìm kiếm gần đúng | Có | Có | Có | Có |
| Tìm kiếm nhị phân | Không | Không | Có (kết hợp với MATCH) | Có |
| Tương thích | Tất cả các phiên bản | Tất cả các phiên bản | Tất cả các phiên bản | Excel 365, Excel 2021 trở lên |
Các lỗi thường gặp và cách khắc phục
Có thể bạn quan tâm: Thay Đổi Tốc Độ Video Online Miễn Phí – Hướng Dẫn Chi Tiết Từng Bước
Khi sử dụng các hàm tra cứu dữ liệu trong Excel, bạn có thể gặp phải một số lỗi phổ biến. Dưới đây là các lỗi thường gặp và cách khắc phục:
1. Lỗi #N/A
Nguyên nhân: Không tìm thấy giá trị cần tìm trong dải ô tra cứu.
Cách khắc phục:
- Kiểm tra lại chính tả, khoảng trắng, hoặc định dạng dữ liệu (số, văn bản, ngày tháng).
- Sử dụng hàm IFERROR để xử lý lỗi: =IFERROR(VLOOKUP(A2, B2:C10, 2, FALSE), “Không tìm thấy”).
2. Lỗi #REF!
Nguyên nhân: Tham chiếu đến một ô hoặc dải ô không tồn tại (có thể do xóa cột hoặc hàng).
Cách khắc phục:
- Kiểm tra lại các tham chiếu trong công thức.
- Đảm bảo rằng các cột hoặc hàng mà công thức đang tham chiếu vẫn tồn tại.
3. Lỗi #VALUE!
Nguyên nhân: Đối số trong công thức không hợp lệ (ví dụ: col_index_num lớn hơn số cột trong table_array).
Cách khắc phục:
- Kiểm tra lại giá trị của col_index_num hoặc row_index_num.
- Đảm bảo rằng giá trị này nằm trong phạm vi hợp lệ.
4. Kết quả sai
Nguyên nhân: Thường do sử dụng sai chế độ tra cứu (TRUE thay vì FALSE) hoặc do dữ liệu chưa được sắp xếp đúng cách khi sử dụng tra cứu gần đúng.
Cách khắc phục:
- Luôn sử dụng FALSE cho tra cứu chính xác nếu bạn không chắc chắn.
- Nếu sử dụng tra cứu gần đúng, hãy đảm bảo rằng cột hoặc hàng đầu tiên được sắp xếp tăng dần.
Mẹo và thủ thuật nâng cao khi dùng hàm tra cứu
1. Kết hợp nhiều điều kiện
Đôi khi, bạn cần tìm kiếm một giá trị dựa trên nhiều điều kiện. Ví dụ: Tìm đơn giá của một sản phẩm cụ thể trong một chi nhánh cụ thể.
Cách làm: Sử dụng mảng công thức hoặc kết hợp các hàm. Một cách phổ biến là sử dụng INDEX + MATCH với mảng.
Có thể bạn quan tâm: Hướng Dẫn Cách Chuyển Google Sang Tiếng Việt Trên Máy Tính
=INDEX(C2:C10, MATCH(1, (A2:A10="SP001")(B2:B10="Chi nhánh A"), 0))
Lưu ý: Đây là công thức mảng, bạn cần nhấn Ctrl + Shift + Enter thay vì chỉ Enter (trong các phiên bản Excel cũ).
2. Tìm kiếm theo nhiều tiêu chí
Bạn có thể sử dụng XLOOKUP để tìm kiếm theo nhiều tiêu chí bằng cách nối các giá trị lại với nhau.
=XLOOKUP(A2&B2, D2:D10&E2:E10, F2:F10)
3. Tìm kiếm giá trị lớn nhất hoặc nhỏ nhất
Kết hợp VLOOKUP hoặc INDEX + MATCH với các hàm MAX, MIN để tìm giá trị lớn nhất hoặc nhỏ nhất trong một bảng dữ liệu.
=VLOOKUP(MAX(A2:A10), A2:C10, 2, FALSE)
4. Sử dụng Named Range (Vùng đặt tên)
Đặt tên cho các dải ô sẽ giúp công thức của bạn dễ đọc và dễ quản lý hơn.
=VLOOKUP(A2, BangDuLieu, 2, FALSE)
Lời kết
Việc thành thạo các hàm tra cứu dữ liệu trong Excel như VLOOKUP, HLOOKUP, INDEX + MATCH, và XLOOKUP sẽ giúp bạn xử lý dữ liệu một cách nhanh chóng, chính xác và chuyên nghiệp hơn bao giờ hết. Mỗi hàm đều có những ưu điểm và hạn chế riêng, việc lựa chọn hàm phù hợp phụ thuộc vào cấu trúc dữ liệu và yêu cầu cụ thể của công việc.
Hãy bắt đầu từ những ví dụ đơn giản, luyện tập thường xuyên, và dần dần khám phá các kỹ thuật nâng cao. Excel là một công cụ mạnh mẽ, và những hàm tra cứu chính là chìa khóa để mở ra tiềm năng vô hạn của nó. Chúc bạn thành công trên con đường chinh phục Excel!
