Có thể bạn quan tâm: Mẫu File Excel Kế Toán Nội Bộ: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Mở Đầu
Trong môi trường làm việc với dữ liệu, Microsoft Excel luôn là một công cụ không thể thiếu. Một trong những nhu cầu phổ biến nhất khi sử dụng Excel là tìm kiếm giá trị trong một bảng dữ liệu (lookup value in table). Dù bạn là người mới bắt đầu hay đã có kinh nghiệm, việc nắm vững các hàm lookup sẽ giúp tiết kiệm thời gian, giảm thiểu sai sót và nâng cao hiệu quả công việc. Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, từ cơ bản đến nâng cao, về cách sử dụng các hàm lookup trong Excel, bao gồm VLOOKUP, HLOOKUP, INDEX-MATCH, XLOOKUP, LOOKUP, và các kỹ thuật hỗ trợ khác như Data Validation, Conditional Formatting và Power Query.
Có thể bạn quan tâm: Excel Kế Toán Bán Hàng: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
1. Tổng Quan Về Các Hàm Lookup
1.1 VLOOKUP (Vertical Lookup)
- Mục đích: Tìm kiếm một giá trị trong cột đầu tiên của một bảng và trả về giá trị ở cột khác trong cùng một hàng.
- Cú pháp:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) - Tham số:
lookup_value: Giá trị cần tìm.table_array: Vùng dữ liệu chứa cả cột tìm kiếm và cột trả về.col_index_num: Số thứ tự cột trongtable_arraymà bạn muốn trả về.range_lookup:TRUE(tìm gần đúng) hoặcFALSE(tìm chính xác).
1.2 HLOOKUP (Horizontal Lookup)
- Mục đích: Tương tự VLOOKUP, nhưng tìm kiếm trên hàng đầu tiên và trả về giá trị trên cột khác.
- Cú pháp:
=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)
1.3 INDEX + MATCH
- Mục đích: Kết hợp hai hàm để tạo ra một công cụ tìm kiếm mạnh mẽ, linh hoạt hơn VLOOKUP/HLOOKUP.
- Cú pháp:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))- Ưu điểm:
- Không bị giới hạn bởi vị trí cột tìm kiếm (có thể tìm ở bất kỳ vị trí nào).
- Hiệu suất tốt hơn với bảng lớn.
- Hỗ trợ tìm kiếm từ phải sang trái.
1.4 XLOOKUP (Excel 365/2026 trở lên)
- Mục đích: Thay thế VLOOKUP/HLOOKUP, cung cấp tính năng mạnh mẽ và dễ sử dụng.
- Cú pháp:
=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode) - Đặc điểm nổi bật:
- Tìm kiếm cả theo chiều dọc và ngang.
- Hỗ trợ tìm kiếm ngược (search from last to first).
- Cho phép trả về giá trị mặc định khi không tìm thấy.
1.5 LOOKUP
- Mục đích: Phiên bản cũ, ít linh hoạt hơn nhưng vẫn hữu ích trong một số trường hợp.
- Cú pháp:
- Vector form:
=LOOKUP(lookup_value, lookup_vector, result_vector) - Array form:
=LOOKUP(lookup_value, array)
Có thể bạn quan tâm: Không Nhập Được Số Trong Excel: Nguyên Nhân, Giải Pháp Và Cách Khắc Phục Chi Tiết
2. Khi Nào Nên Dùng Hàm nào?
| Nhu cầu | Hàm đề xuất | Lý do |
|---|---|---|
| Tìm kiếm đơn giản, bảng không quá lớn, cột tìm kiếm luôn ở phía trái | VLOOKUP | Dễ nhớ, trực quan |
| Tìm kiếm ngang, dữ liệu bố trí theo hàng | HLOOKUP | Tương tự VLOOKUP nhưng theo hàng |
| Tìm kiếm đa chiều, cần trả về giá trị từ cột bất kỳ | INDEX-MATCH | Linh hoạt, không phụ thuộc vị trí cột |
| Excel 365/2026 trở lên, muốn một hàm duy nhất, hỗ trợ tìm ngược và tùy chọn lỗi | XLOOKUP | Đơn giản, mạnh mẽ |
| Tìm kiếm nhanh trong mảng 1 chiều, không cần tùy chọn phức tạp | LOOKUP | Thích hợp cho dữ liệu đã được sắp xếp |
3. Cài Đặt Dữ Liệu Mẫu
Để minh họa, chúng ta sẽ sử dụng một bảng sản phẩm:
| Mã SP | Tên Sản Phẩm | Loại | Giá Bán | Số Lượng |
|---|---|---|---|---|
| SP001 | Bàn Gỗ | Nội thất | 2,500,000 | 15 |
| SP002 | Ghế Nhựa | Nội thất | 850,000 | 30 |
| SP003 | Đèn LED | Điện | 320,000 | 45 |
| SP004 | Máy Lạnh | Điện | 6,200,000 | 8 |
| SP005 | Tủ Lạnh | Điện | 5,500,000 | 12 |
Giả sử chúng ta muốn:
- Lấy Giá Bán dựa trên Mã SP.
- Xác định Số Lượng dựa trên Tên Sản Phẩm.
- Kiểm tra Loại của một sản phẩm và trả về “Không tồn tại” nếu không tìm thấy.
4. Thực Hành Với VLOOKUP
4.1 Công Thức Cơ Bản
=VLOOKUP("SP003", A2:E6, 4, FALSE)
- Giải thích:
lookup_value="SP003".table_array=A2:E6(bảng dữ liệu).col_index_num=4(cột Giá Bán).range_lookup=FALSE(tìm chính xác).
Kết quả: 320,000.
4.2 Xử Lý Khi Không Tìm Thấy (IFERROR)
=IFERROR(VLOOKUP("SP999", A2:E6, 4, FALSE), "Không tồn tại")
Nếu mã không tồn tại, hàm trả về “Không tồn tại” thay vì lỗi #N/A.
4.3 Tìm Kiếm Nhiều Giá Trị (Array Formula)
Trong Excel 365, bạn có thể nhập mảng:
=VLOOKUP({"SP001","SP004"}, A2:E6, 4, FALSE)
Kết quả trả về mảng {2,500,000; 6,200,000}.
5. Thực Hành Với HLOOKUP
Giả sử bảng được sắp xếp ngang:
| A | B | C | D | E |
|---|---|---|---|---|
| Mã SP | SP001 | SP002 | SP003 | SP004 |
| Giá Bán | 2,500,000 | 850,000 | 320,000 | 6,200,000 |
Tìm giá bán của SP003:
=HLOOKUP("SP003", A1:E2, 2, FALSE)
Kết quả: 320,000.
6. Kết Hợp INDEX + MATCH
6.1 Lấy Giá Bán Dựa trên Mã SP
=INDEX(D2:D6, MATCH("SP004", A2:A6, 0))
MATCH("SP004", A2:A6, 0)trả về vị trí hàng (4).INDEX(D2:D6, 4)trả về giá trị ở hàng thứ 4 của cột Giá Bán.
Kết quả: 6,200,000.
6.2 Tìm Số Lượng Dựa trên Tên Sản Phẩm
=INDEX(E2:E6, MATCH("Ghế Nhựa", B2:B6, 0))
Kết quả: 30.
6.3 Tìm Kiếm Từ Phải Sang Trái
Nếu cột Mã SP nằm ở phía phải, VLOOKUP sẽ không hoạt động. INDEX-MATCH giải quyết:
=INDEX(A2:A6, MATCH("6,200,000", D2:D6, 0))
Kết quả: SP004.
7. XLOOKUP – Hàm Đa Năng Mới
7.1 Cú Pháp Cơ Bản
=XLOOKUP("SP002", A2:A6, D2:D6, "Không tồn tại")
lookup_value:"SP002".lookup_array:A2:A6(cột Mã SP).return_array:D2:D6(cột Giá Bán).if_not_found:"Không tồn tại".
Kết quả: 850,000.
7.2 Tìm Kiếm Ngược (Search Mode = -1)
Giả sử chúng ta có danh sách ngày bán hàng và muốn lấy giá trị gần nhất trước ngày cho trước.
| Ngày | Doanh Thu |
|---|---|
| 01/01/2026 | 10,000,000 |
| 05/01/2026 | 12,500,000 |
| 10/01/2026 | 9,800,000 |
=XLOOKUP(DATE(2026,1,7), A2:A4, B2:B4, "Không có", 1, -1)
match_mode=1(tìm gần đúng lớn hơn hoặc bằng).search_mode=-1(tìm ngược từ cuối lên đầu).
Kết quả: 12,500,000 (giá trị ngày 05/01/2026).
7.3 Trả Về Nhiều Cột (Array Return)
=XLOOKUP("SP003", A2:A6, D2:E6, "Không tồn tại")
Kết quả trả về một mảng {320,000; 45} (Giá Bán và Số Lượng).
8. LOOKUP – Khi nào nên dùng?
LOOKUP hoạt động khi lookup_vector và result_vector đã được sắp xếp tăng dần. Ví dụ:
=LOOKUP(350000, D2:D6, B2:B6)
Tìm giá trị nhỏ nhất trong cột Giá Bán không vượt quá 350,000 và trả về tên sản phẩm tương ứng. Kết quả: Đèn LED.
Lưu ý: Nếu dữ liệu không được sắp xếp, LOOKUP có thể trả về kết quả sai.
9. Kỹ Thuật Nâng Cao
9.1 Data Validation + VLOOKUP để Tạo Dropdown Động
Có thể bạn quan tâm: Excel Lock Cell In Formula: Hướng Dẫn Chi Tiết, Mẹo Thủ Và Ứng Dụng Thực Tiễn
- Tạo danh sách dropdown:
Chọn ô A10 → Data → Data Validation → List → nhập
=$A$2:$A$6(danh sách Mã SP).Hiển thị thông tin:
- Ô B10 (Tên Sản Phẩm):
=VLOOKUP(A10, $A$2:$C$6, 2, FALSE) - Ô C10 (Giá Bán):
=VLOOKUP(A10, $A$2:$E$6, 4, FALSE)
Khi người dùng chọn Mã SP, các thông tin liên quan tự động cập nhật.
9.2 Conditional Formatting Dựa trên Kết Quả Lookup
- Mục tiêu: Đánh dấu các sản phẩm có Số Lượng < 10.
- Công thức:
=VLOOKUP($A2,$A$2:$E$6,5,FALSE)<10 - Định dạng: Đổ nền màu đỏ.
9.3 Power Query – Tìm Kiếm Khi Dữ Liệu Lớn
Khi bảng dữ liệu lên hàng nghìn dòng, việc dùng hàm lookup truyền thống có thể chậm. Power Query cho phép:
- Nhập dữ liệu → Transform Data.
- Merge Queries → Chọn bảng chính và bảng lookup → Chọn cột khóa → Kiểu join (Left Outer, Inner…).
- Expand cột kết quả để lấy giá trị mong muốn.
Kết quả: Một bảng mới đã được “join” các thông tin một cách nhanh chóng, không gây lag.
9.4 Sử Dụng ARRAYFORMULA (Google Sheets) – Tương Đương Với Excel 365
Trong Google Sheets, hàm ARRAYFORMULA kết hợp với VLOOKUP hoặc XLOOKUP để trả về mảng kết quả:
=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A10, Sheet2!A:B, 2, FALSE), "Không tồn tại"))
10. Xử Lý Lỗi Thông Thường
| Lỗi | Nguyên Nhân | Cách Khắc Phục |
|---|---|---|
#N/A | Không tìm thấy giá trị | Sử dụng IFERROR(..., "Không tồn tại") hoặc kiểm tra dữ liệu. |
#VALUE! | Đối số không hợp lệ (ví dụ: col_index_num không phải số) | Đảm bảo các tham số đúng kiểu. |
#REF! | Tham chiếu ô bị xóa hoặc không tồn tại | Kiểm tra lại phạm vi table_array. |
#NAME? | Tên hàm không đúng hoặc không được hỗ trợ trong phiên bản Excel hiện tại | Kiểm tra phiên bản Excel (XLOOKUP chỉ có trong 365/2026+). |
#SPILL! | Mảng kết quả không đủ không gian | Đảm bảo các ô xung quanh trống. |
11. Tối Ưu Hóa Hiệu Suất
- Giảm phạm vi bảng: Sử dụng
Table(Ctrl+T) để Excel tự động mở rộng khi thêm dữ liệu, thay vì dùng toàn cột (A:A). - Sử dụng
XLOOKUPhoặcINDEX-MATCH: TránhVLOOKUPvớirange_lookup = TRUEtrên bảng lớn vì phải sắp xếp dữ liệu. - Tắt tính năng tính toán tự động (đối với bảng cực lớn) → Formulas → Calculation Options → Manual, sau đó nhấn
F9để tính lại khi cần.
12. Thực Hành Tổng Hợp: Xây Dựng Bảng Báo Cáo Bán Hàng
12.1 Dữ liệu nguồn
- Bảng Sản Phẩm (như trên).
- Bảng Đơn Hàng:
| Mã ĐH | Mã SP | Ngày | Số Lượng Bán |
|---|---|---|---|
| DH001 | SP001 | 01/02/2026 | 3 |
| DH002 | SP003 | 03/02/2026 | 5 |
| DH003 | SP005 | 05/02/2026 | 2 |
| DH004 | SP002 | 07/02/2026 | 7 |
| DH005 | SP004 | 10/02/2026 | 1 |
12.2 Yêu cầu
- Tính Doanh Thu cho mỗi đơn hàng (
Giá Bán Số Lượng Bán). - Thêm Tên Sản Phẩm và Loại vào bảng Đơn Hàng.
- Tổng hợp Doanh Thu theo Loại.
12.3 Bước Thực Hiện
- Thêm cột “Tên Sản Phẩm” (cột D):
excel =XLOOKUP(B2, Products!A:A, Products!B:B, "Không tồn tại") - Thêm cột “Giá Bán” (cột E):
excel =XLOOKUP(B2, Products!A:A, Products!D:D, 0) - Tính Doanh Thu (cột F):
excel =E2C2 - Thêm cột “Loại” (cột G):
excel =XLOOKUP(B2, Products!A:A, Products!C:C, "Không tồn tại") - Tổng hợp Doanh Thu theo Loại:
- Tạo bảng tóm tắt:
| Loại | Tổng Doanh Thu |
|——|—————-|
| Nội thất |=SUMIFS(Orders!F:F, Orders!G:G, "Nội thất")|
| Điện |=SUMIFS(Orders!F:F, Orders!G:G, "Điện")| - Kết quả:
- Nội thất:
2,500,0003 + 850,0007 = 7,350,000 - Điện:
320,0005 + 5,500,0002 + 6,200,0001 = 15,800,000
- Nội thất:
12.4 Định Dạng Báo Cáo
- Conditional Formatting: Highlight các đơn hàng có doanh thu > 5,000,000 (đỏ).
- PivotTable: Đối với bảng lớn, dùng PivotTable → Rows = Loại, Values = Sum of Doanh Thu.
13. Những Câu Hỏi Thường Gặp (FAQ)
13.1 VLOOKUP có thể tìm theo cột bất kỳ không?
Không. VLOOKUP chỉ tìm trong cột đầu tiên của table_array. Để tìm ở cột khác, dùng INDEX-MATCH hoặc XLOOKUP.
13.2 Khi nào nên dùng EXACT trong MATCH?
Khi bạn muốn tìm kiếm phân biệt chữ hoa/chữ thường. Ví dụ: MATCH("sp001", A2:A6, 0) sẽ trả về lỗi nếu dữ liệu là “SP001”. Sử dụng MATCH(TRUE, EXACT(A2:A6, "sp001"), 0) (đòi hỏi công thức mảng).
13.3 XLOOKUP có thể trả về nhiều giá trị không?
Có. Khi return_array là một cột hoặc hàng đa chiều, XLOOKUP trả về một mảng. Đặt công thức trong một dải ô đủ rộng để “spill” kết quả.
13.4 Làm sao để tra cứu trong bảng có nhiều cột trùng lặp?
Sử dụng helper column (cột trợ giúp) để tạo một key duy nhất (ví dụ: =A2&B2) rồi tra cứu bằng key này.
13.5 Power Query có thể thực hiện lookup trong thời gian thực?
Power Query thực hiện import và transform dữ liệu, không phải tính toán thời gian thực trong sheet. Sau khi load, dữ liệu sẽ được cập nhật khi bạn refresh query.
14. Kết Luận
Việc lookup value in table là một kỹ năng cốt lõi trong Excel, giúp bạn nhanh chóng trích xuất và tổng hợp thông tin từ các bộ dữ liệu phức tạp. Từ những hàm truyền thống như VLOOKUP và HLOOKUP đến các giải pháp hiện đại như INDEX-MATCH, XLOOKUP, và Power Query, mỗi công cụ đều có ưu và nhược điểm riêng. Việc lựa chọn hàm phù hợp dựa trên:
- Độ phức tạp của dữ liệu,
- Phiên bản Excel đang sử dụng,
- Yêu cầu về hiệu suất và tính linh hoạt,
sẽ giúp tối ưu hoá quy trình làm việc và giảm thiểu lỗi. Hãy thực hành thường xuyên, kết hợp các kỹ thuật bổ trợ như Data Validation, Conditional Formatting, và PivotTable để xây dựng những báo cáo chuyên nghiệp, chính xác và nhanh chóng.
Tip cuối cùng: Khi làm việc với bảng dữ liệu lớn, luôn ưu tiên XLOOKUP (nếu có) hoặc INDEX-MATCH và cân nhắc chuyển sang Power Query để xử lý dữ liệu trong giai đoạn ETL (Extract‑Transform‑Load). Điều này không chỉ cải thiện tốc độ mà còn giúp quản lý dữ liệu một cách có hệ thống hơn.
Chúc bạn thành công trong việc ứng dụng các hàm lookup và nâng cao năng suất làm việc với Excel!
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ẻ.