Có thể bạn quan tâm: Hàm Trả Về Nhiều Kết Quả Trong Excel: Hướng Dẫn Đầy Đủ Và Thực Tập
1. Giới thiệu
Microsoft Excel là một công cụ mạnh mẽ không chỉ để lưu trữ và tính toán dữ liệu mà còn để thực hiện các thao tác phân tích, tra cứu và liên kết dữ liệu một cách nhanh chóng. Trong quá trình làm việc, chúng ta thường phải tìm kiếm giá trị trong bảng, liên kết dữ liệu giữa các sheet hoặc workbook, và thực hiện các phép tính dựa trên kết quả tìm kiếm. Đó chính là những “hàm dò tìm” (lookup) và “hàm tham chiếu” (reference) mà chúng ta sẽ điểm qua trong bài viết này.
Bài viết sẽ trình bày chi tiết từng hàm, cách sử dụng, ưu nhược điểm, cách kết hợp, và những mẹo nhỏ giúp bạn làm việc hiệu quả hơn. Mỗi phần sẽ có ví dụ thực tế, giải thích logic, và kèm theo một số lưu ý quan trọng để tránh lỗi thường gặp. Bạn sẽ được trang bị đầy đủ kiến thức để làm chủ Excel trong các tình huống báo cáo, phân tích dữ liệu và tự động hoá công việc.
Có thể bạn quan tâm: Hàm Thống Kê Nhiều Điều Kiện Trong Excel: Cách Tối Ưu Hóa Phân Tích Dữ Liệu
2. Tổng quan về hàm dò tìm (Lookup Functions)
Các hàm dò tìm trong Excel được chia thành hai loại chính:
- Lookup functions sử dụng chỉ số: VLOOKUP, HLOOKUP, LOOKUP.
- Lookup functions dựa trên vị trí: XLOOKUP, XMATCH, INDEX + MATCH (đơn giản hơn).
2.1 VLOOKUP (Vertical Lookup)
Cú pháp:
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
lookup_value: Giá trị mà bạn muốn tìm.table_array: Phạm vi bảng dữ liệu.col_index_num: Số thứ tự cột trongtable_arraychứa giá trị trả về.range_lookup: TRUE (dùng tìm kiếm gần nhất) hoặc FALSE (tìm kiếm chính xác).
Ưu điểm:
– Dễ hiểu, phổ biến trong nhiều phiên bản Excel.
– Hoạt động tốt khi dữ liệu được sắp xếp theo thứ tự tăng dần (nếu range_lookup = TRUE).
Nhược điểm:
– Không thể tra cứu giá trị ở cột trái của lookup_value.
– Dễ bị lỗi khi cột trả về di chuyển.
– Chỉ hỗ trợ tìm kiếm theo chiều dọc.
Ví dụ thực tế:
Giả sử bạn có bảng dữ liệu nhân viên:
| Mã NV | Tên | Phòng | Lương |
|——-|—–|——-|——-|
| 001 | A | Kế toán | 10M |
| 002 | B | IT | 12M |
| 003 | C | Marketing | 9M |
Bạn muốn tra cứu lương của nhân viên có mã “002”:
=VLOOKUP("002", A2:D4, 4, FALSE)
Kết quả: 12M.
2.2 HLOOKUP (Horizontal Lookup)
Cách hoạt động tương tự VLOOKUP nhưng theo chiều ngang. Thường dùng khi dữ liệu được sắp xếp theo hàng.
2.3 LOOKUP
Cách đơn giản nhất nhưng hạn chế hơn. Có hai dạng:
– LOOKUP(vector, result_vector): Tìm giá trị trong một vector duy nhất.
– LOOKUP(lookup_value, lookup_vector, result_vector): Tìm giá trị trong một vector có sắp xếp.
2.4 XLOOKUP (Excel 365+)
XLOOKUP được thiết kế để thay thế VLOOKUP/HLOOKUP, đem lại tính linh hoạt cao.
Cú pháp:
XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)
lookup_value: Giá trị cần tìm.lookup_array: Dải dữ liệu chứa giá trị cần tìm.return_array: Dải dữ liệu chứa giá trị trả về.if_not_found: Giá trị trả về khi không tìm thấy (tùy chọn).match_mode: 0 = chính xác, -1 = chính xác hoặc nhỏ hơn, 1 = chính xác hoặc lớn hơn, 2 = wildcard.search_mode: 1 = tìm từ đầu, -1 = tìm từ cuối.
Ưu điểm:
– Tra cứu theo cả hai hướng (trái/điểm, dọc/điểm).
– Không cần sắp xếp dữ liệu.
– Hỗ trợ tìm kiếm “wildcard” và “range_lookup” mạnh hơn.
Ví dụ:
=XLOOKUP("002", A2:A4, D2:D4, "Không tìm thấy", 0, 1)
2.5 XMATCH (Excel 365+)
Trả về vị trí của giá trị tìm thấy trong mảng.
XMATCH("002", A2:A4, 0)
2.6 INDEX + MATCH
Cách lộ diện trong Excel, thường được dùng khi bạn cần tính linh hoạt cao hơn.
Cú pháp:
INDEX(return_array, MATCH(lookup_value, lookup_array, 0))
INDEXtrả về giá trị tại vị trí.MATCHtìm vị trí củalookup_valuetronglookup_array.
Ưu điểm:
– Cho phép tra cứu giá trị ở bất kỳ cột nào mà không bị ràng buộc.
– Dễ dàng kết hợp với các hàm khác như SUMIF, COUNTIF.
Ví dụ:
=INDEX(D2:D4, MATCH("002", A2:A4, 0))
Có thể bạn quan tâm: Hàm Thỏa Mãn 2 Điều Kiện Trong Excel
3. Tổng quan về hàm tham chiếu (Reference Functions)
Các hàm tham chiếu giúp bạn xác định vùng dữ liệu, lấy giá trị từ vùng khác, hoặc tự động điều chỉnh vùng khi bạn thêm/di chuyển dữ liệu. Các hàm này rất quan trọng trong việc xây dựng bảng tính linh động và tránh lỗi khi dữ liệu thay đổi.
3.1 INDIRECT
Cú pháp:
INDIRECT(ref_text, a1)
ref_text: Chuỗi ký tự đại diện cho tham chiếu ô (ví dụ: “Sheet1!A1”).a1: Nếu TRUE (hoặc bỏ qua) thì tham chiếu theo kiểu A1, nếu FALSE thì theo kiểu R1C1.
Ứng dụng:
– Kiểm tra giá trị trong ô khác mà không cần tham chiếu trực tiếp.
– Tạo bảng tính động: thay đổi sheet hoặc cột/điểm theo giá trị trong ô.
Ví dụ:
Bạn có ô B1 chứa tên sheet “Quản lý”. Bạn muốn lấy giá trị ô A1 trong sheet đó:
=INDIRECT(B1 & "!A1")
3.2 OFFSET
Cú pháp:
OFFSET(reference, rows, cols, height, width)
reference: Ô tham chiếu ban đầu.rows: Số hàng di chuyển.cols: Số cột di chuyển.height: Chiều cao vùng trả về (đơn vị ô).width: Độ rộng vùng trả về.
Ứng dụng:
– Tạo vùng dữ liệu động: Ví dụ, bạn cần tính tổng doanh thu trong 3 tháng cuối cùng.
– Kết hợp với SUM, AVERAGE để tính động.
Ví dụ:
=SUM(OFFSET(A1, 0, 0, 3, 1))
Tổng giá trị trong 3 ô từ A1 tới A3.
3.3 INDEX (cũng là hàm tham chiếu)
Có thể bạn quan tâm: Hàm Tìm Kiếm Có Điều Kiện Trong Excel: Hướng Dẫn Chi Tiết Và Thực Hành
Như đã đề cập trong phần VLOOKUP, INDEX có thể dùng như một hàm tham chiếu độc lập:
=INDEX(A1:D10, 5, 3)
Lấy giá trị ở hàng 5, cột 3 của vùng A1:D10.
3.4 CHOOSE
Cho phép chọn giá trị từ một danh sách dựa trên chỉ số:
=CHOOSE(2, "A", "B", "C")
Kết quả: “B”.
3.5 CHOOSE + ROW/ COLUMN
Kết hợp CHOOSE với ROW hoặc COLUMN để chuyển đổi giữa các vùng động.
4. Kết hợp hàm dò tìm và tham chiếu: Thực hành nâng cao
4.1 Tra cứu giá trị dựa trên nhiều điều kiện
Bạn có bảng dữ liệu bán hàng:
| Ngày | Sản phẩm | Kênh | Doanh thu |
|——|———-|——|———–|
| 01/01 | A | Online | 100k |
| 01/01 | B | Offline | 150k |
| 02/01 | A | Online | 200k |
| 02/01 | B | Offline | 120k |
Bạn muốn lấy doanh thu của sản phẩm A bán qua Online vào ngày 02/01.
Cách 1: INDEX + MATCH kết hợp:
=INDEX(D2:D5, MATCH(1, (A2:A5="02/01")(B2:B5="A")(C2:C5="Online"), 0))
Lưu ý: Đây là công thức mảng nên nhấn Ctrl+Shift+Enter (trong Excel 2026 trở xuống).
Cách 2: XLOOKUP với nhiều điều kiện (Excel 365+):
=XLOOKUP(1, IF((A2:A5="02/01")(B2:B5="A")(C2:C5="Online"),1,0), D2:D5, "Không tìm thấy")
4.2 Tạo danh sách động (Dynamic Named Ranges)
Bạn muốn tính tổng doanh thu trong tháng hiện tại mà không cần thay đổi công thức khi thêm dữ liệu.
Bước 1: Đặt tên vùng dữ liệu (Dynamic Named Range) bằng công thức OFFSET:
=OFFSET($A$2, 0, 0, COUNTA($A:$A)-1, 4)
Bước 2: Sử dụng trong công thức:
=SUMIFS(DynamicRange, DynamicRangeNgày, ">=01/"&MONTH(TODAY())&"/"&YEAR(TODAY()), DynamicRangeNgày, "<="&EOMONTH(TODAY(),0))
4.3 Kết hợp WITH INDIRECT và XLOOKUP
Bạn có nhiều sheet theo tháng: Sheet1, Sheet2, … Sheet12. Bạn muốn tra cứu doanh thu của sản phẩm A trong tháng hiện tại mà không cần thay đổi công thức.
Công thức:
=XLOOKUP("A", INDIRECT(TEXT(TODAY(),"mm") & "!A2:A100"), INDIRECT(TEXT(TODAY(),"mm") & "!D2:D100"), "Không tìm thấy")
Ở đây, TEXT(TODAY(),"mm") tạo chuỗi “01”, “02”, … “12” tương ứng với tháng. INDIRECT tạo tham chiếu tới sheet tương ứng.
4.4 Sử dụng XMATCH để tìm vị trí và OFFSET để lấy dữ liệu
Bạn muốn lấy giá trị trong ô ngay sau ô có giá trị “Total” trong cột A.
=OFFSET(A1, XMATCH("Total", A:A, 0), 1)
5. Lưu ý khi sử dụng hàm dò tìm và tham chiếu
- Sắp xếp dữ liệu: VLOOKUP với
range_lookup = TRUEyêu cầu dữ liệu sắp xếp theo thứ tự tăng dần. Nếu không, kết quả có thể sai. - Kiểm tra lỗi: Sử dụng hàm
IFERRORđể tránh hiển thị lỗi khi giá trị không được tìm thấy.=IFERROR(VLOOKUP(...), "Không tìm thấy") - Công thức mảng: Khi kết hợp nhiều điều kiện, hãy nhớ nhấn
Ctrl+Shift+Enternếu không dùng Excel 365. - Giới hạn vùng: Đối với XLOOKUP, nếu vùng lớn (thousands of rows), có thể ảnh hưởng đến hiệu suất. Cân nhắc dùng công thức mảng hoặc giới hạn vùng.
- Tham chiếu hiển thị: Khi di chuyển ô hoặc chèn cột, các hàm tham chiếu như
INDEXvàOFFSETsẽ tự động điều chỉnh. Tuy nhiên,VLOOKUPcó thể sai khi cột trả về bị di chuyển.
6. Mẹo nâng cao: Bảng tính tự động hoá
6.1 Kết hợp INDEX + MATCH + INDIRECT để tạo bảng tính động
Bạn có danh sách các bảng dữ liệu (mỗi bảng là một sheet). Bạn muốn tạo một ô tổng quan, tự động lấy dữ liệu từ sheet tương ứng dựa trên lựa chọn trong ô B1.
=SUM(INDEX(INDIRECT(B1 & "!B:B"), 2):INDEX(INDIRECT(B1 & "!B:B"), COUNTA(INDIRECT(B1 & "!B:B"))))
6.2 XLOOKUP với wildcard
Nếu bạn muốn tra cứu dữ liệu có ký tự thay thế, dùng dấu hoặc ?.
=XLOOKUP("Apple", A:A, B:B, "Không tìm thấy")
6.3 OFFSET + COUNTA để tạo vùng động
Nếu bạn muốn tính tổng doanh thu trong cột D, nhưng số hàng thay đổi:
=SUM(OFFSET(D2, 0, 0, COUNTA(D:D)-1, 1))
6.4 CHOOSE + ROW để chuyển đổi cột
Bạn có một bảng dữ liệu với các cột: A, B, C. Bạn muốn phép tính tùy chọn cột theo giá trị trong ô E1 (1, 2, 3).
=SUM(CHOOSE(E1, A:A, B:B, C:C))
7. Thực hành: Tạo một Dashboard đơn giản
Giả sử bạn có bảng dữ liệu bán hàng theo tuần:
| Tuần | Sản phẩm | Doanh thu |
|---|---|---|
| 1 | A | 100k |
| 1 | B | 150k |
| 2 | A | 200k |
| 2 | B | 120k |
| 3 | A | 300k |
| 3 | B | 180k |
Bạn muốn tạo một Dashboard với:
– Tổng doanh thu từng tuần.
– Doanh thu của mỗi sản phẩm.
– Doanh thu tổng cộng.
Bước 1: Tạo cột “Tổng doanh thu tuần” bằng công thức SUMIFS.
=SUMIFS(C:C, A:A, A2) (đặt công thức ở C2, kéo xuống)
Bước 2: Tạo bảng PivotTable để tóm tắt dữ liệu.
Bước 3: Sử dụng XLOOKUP để hiển thị doanh thu của sản phẩm cụ thể theo tuần được chọn trong ô B10.
=XLOOKUP(B10, A:A, C:C, "Không có dữ liệu")
Bước 4: Kết hợp với Conditional Formatting để làm nổi bật doanh thu cao hơn 200k.
8. Kết luận
Các hàm dò tìm và tham chiếu trong Excel là cốt lõi giúp bạn khai thác dữ liệu một cách linh hoạt và hiệu quả. Từ những hàm cơ bản như VLOOKUP, HLOOKUP đến những hàm mới hơn như XLOOKUP, XMATCH, các công thức mảng INDEX + MATCH, cùng với các hàm tham chiếu như INDIRECT, OFFSET, bạn có thể xây dựng các bảng tính động, bảng điều khiển, và hệ thống báo cáo tự động.
Những mẹo và ví dụ trong bài viết không chỉ giúp bạn hiểu rõ cách hoạt động của từng hàm mà còn cung cấp công cụ để áp dụng ngay vào công việc thực tế. Hãy thử áp dụng và tinh chỉnh cho phù hợp với dữ liệu của bạn, vì Excel thực sự mạnh mẽ nhất khi được khai thác tối đa.
Chúc bạn thành công trong việc trở thành “người làm chủ” Excel và nâng cao năng suất công việc!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.