Hàm VLOOKUP là một công cụ mạnh mẽ và không thể thiếu trong Excel, giúp người dùng tìm kiếm và trích xuất dữ liệu một cách hiệu quả. Tuy nhiên, khi nhu cầu xử lý dữ liệu ngày càng phức tạp, việc sử dụng VLOOKUP giữa hai sheet, hai file hoặc thậm chí nhiều trang tính trở nên cần thiết. Bài viết này sẽ đi sâu vào hướng dẫn chi tiết cách sử dụng hàm VLOOKUP trong các tình huống nâng cao này, mang đến cho bạn những kiến thức và kỹ năng tối ưu hóa quy trình làm việc trên Excel.
I. VLOOKUP Giữa 2 Sheet: Nâng Cao Khả Năng Tìm Kiếm Dữ Liệu
Nội dung
Khi dữ liệu của bạn được phân bổ trên nhiều trang tính (sheet) khác nhau trong cùng một file Excel, việc sử dụng VLOOKUP sẽ yêu cầu bạn chỉ định rõ phạm vi tìm kiếm thuộc về trang tính nào. Điều này giúp Excel xác định chính xác vị trí dữ liệu cần tra cứu.
1. Cú Pháp VLOOKUP Giữa 2 Sheet
Cú pháp cơ bản của hàm VLOOKUP khi làm việc với hai sheet như sau:
VLOOKUP(lookup_value, Sheet!range, col_index_num, [range_lookup])
Trong đó:
lookup_value: Giá trị bạn muốn tìm kiếm.Sheet!range: Đây là phần quan trọng.Sheetlà tên của trang tính chứa dữ liệu bạn muốn tra cứu, theo sau là dấu chấm than (!) và sau đó là phạm vi dữ liệu (range) trên trang tính đó. Ví dụ:thuong!$B$4:$C$6.col_index_num: Số thứ tự của cột trongrangemà bạn muốn trả về giá trị.[range_lookup]: Xác định xem bạn muốn tìm kiếm chính xác (FALSE) hay gần đúng (TRUE).
2. Ví Dụ Thực Tế
Giả sử bạn có hai sheet:
- Sheet 1 (nhanvien): Chứa danh sách nhân viên và các thông tin liên quan, bao gồm cả cột cần điền điểm thưởng.
- Sheet 2 (thuong): Chứa bảng xếp loại và mức thưởng tương ứng.
Để điền số tiền thưởng cho nhân viên trong sheet nhanvien, bạn có thể sử dụng công thức VLOOKUP tham chiếu đến sheet thuong. Tại ô D3 của sheet nhanvien (nơi bạn muốn hiển thị tiền thưởng cho nhân viên đầu tiên), bạn nhập công thức:
=VLOOKUP(C3, thuong!$B$4:$C$6, 2, FALSE)
C3: Là giá trị xếp loại của nhân viên trong sheetnhanvien.thuong!$B$4:$C$6: Là phạm vi chứa bảng xếp loại và mức thưởng trên sheetthuong. Dấu$giúp cố định phạm vi này khi bạn sao chép công thức.2: Cho biết bạn muốn lấy giá trị từ cột thứ hai trong phạm vithuong!$B$4:$C$6(là cột tiền thưởng).FALSE: Yêu cầu tìm kiếm chính xác xếp loại.
Sau khi nhập công thức, kéo ô D3 xuống các ô còn lại trong cột D để áp dụng cho tất cả nhân viên.
Minh họa ví dụ VLOOKUP giữa 2 sheet
II. VLOOKUP Giữa 2 File: Mở Rộng Phạm Vi Tìm Kiếm
Khi dữ liệu của bạn nằm trong hai file Excel khác nhau, VLOOKUP vẫn có thể hoạt động, tuy nhiên cú pháp tham chiếu sẽ có thêm tên file.
1. Cách Tìm Kiếm Dữ Liệu Bằng VLOOKUP Giữa 2 File
Quy trình tương tự như VLOOKUP giữa 2 sheet, nhưng bạn cần chỉ định rõ tên file Excel chứa dữ liệu.
Các bước thực hiện:
- Chuẩn bị: Mở cả hai file Excel. Một file chứa dữ liệu gốc (ví dụ:
hocsinh.xlsx) và một file chứa bảng tra cứu (ví dụ:xeploai.xlsx). - Nhập công thức: Tại ô bạn muốn hiển thị kết quả trong file
hocsinh.xlsx(ví dụ: ô D2), nhập công thức. Khi đến phần chỉ định phạm vi (table_array), bạn cần:- Chuyển sang file
xeploai.xlsx. - Chọn phạm vi dữ liệu bạn muốn tra cứu (ví dụ:
A4:B7). Excel sẽ tự động thêm đường dẫn đầy đủ đến file và sheet. - Nhấn
F4để cố định phạm vi. - Hoàn thiện công thức với
col_index_numvàrange_lookup.
- Chuyển sang file
Ví dụ, nếu bạn muốn tìm xếp loại cho học sinh dựa trên điểm số từ file xeploai.xlsx, công thức tại ô D2 của file hocsinh.xlsx có thể là:
=VLOOKUP(C2,'[xeploai.xlsx]Sheet1'!$A$4:$B$7, 2, FALSE)
C2: Điểm số của học sinh trong filehocsinh.xlsx.'[xeploai.xlsx]Sheet1'!$A$4:$B$7: Tham chiếu đến phạm viA4:B7trênSheet1của filexeploai.xlsx. Tên file được đặt trong dấu ngoặc vuông[].2: Cột chứa xếp loại.FALSE: Tìm kiếm chính xác.
Minh họa VLOOKUP giữa 2 file
Sau khi nhập công thức, kéo xuống để áp dụng cho các học sinh khác.
III. Hàm VLOOKUP Trên Nhiều Trang Tính và File: Tối Ưu Hóa Với IFERROR và INDIRECT
Khi làm việc với nhiều hơn hai trang tính hoặc nhiều file, việc lồng ghép các hàm sẽ giúp bạn quản lý dữ liệu hiệu quả hơn.
1. Kết Hợp VLOOKUP Với IFERROR
Hàm IFERROR rất hữu ích để xử lý các trường hợp VLOOKUP không tìm thấy kết quả, tránh hiển thị lỗi #N/A. Bạn có thể lồng nhiều hàm IFERROR để kiểm tra tuần tự nhiều trang tính hoặc file.
Công thức chung:
=IFERROR(VLOOKUP(lookup_value, Sheet1!range, col_index_num, FALSE), IFERROR(VLOOKUP(lookup_value, Sheet2!range, col_index_num, FALSE), ..., "Not found"))
Ví dụ: Tìm số lượng sản phẩm (slsp) từ sanpham1 và sanpham2.
- Bước 1: Nhập công thức VLOOKUP đầu tiên vào ô C2, tìm kiếm trong sheet
sanpham1:=IFERROR(VLOOKUP(A2, sanpham1!$A$2:$B$5, 2, FALSE), ...) - Bước 2: Lồng thêm một hàm
IFERRORkhác cho sheetsanpham2:=IFERROR(VLOOKUP(A2, sanpham1!$A$2:$B$5, 2, FALSE), IFERROR(VLOOKUP(A2, sanpham2!$A$2:$B$5, 2, FALSE), "Not found"))
Công thức này sẽ tìm trong sanpham1 trước. Nếu không tìm thấy, nó sẽ tiếp tục tìm trong sanpham2. Nếu vẫn không tìm thấy, nó sẽ trả về “Not found”.
Minh họa VLOOKUP với IFERROR
2. Sử Dụng VLOOKUP Với INDIRECT
Hàm INDIRECT cho phép bạn tạo tham chiếu đến các ô hoặc phạm vi dựa trên chuỗi văn bản. Điều này rất mạnh mẽ khi làm việc với nhiều sheet hoặc file có tên động.
Công thức VLOOKUP trên nhiều trang tính với INDIRECT:
=VLOOKUP(lookup_value, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'" & Lookup_sheets & "'!lookup_range"), lookup_value)>0), 0))&"'!table_array"), col_index_num, FALSE)
Lookup_sheets: Là một phạm vi tên chứa danh sách tên các sheet bạn muốn tra cứu.lookup_range: Phạm vi tìm kiếm trên mỗi sheet.table_array: Phạm vi dữ liệu trả về trên mỗi sheet.
Lưu ý: Công thức này thường cần được nhập dưới dạng công thức mảng (nhấn Ctrl + Shift + Enter).
Ví dụ: Tìm kiếm giá trị “X” trên các sheet “Sheet1”, “Sheet2”, “Sheet3”, “Sheet4”.
Bạn cần tạo một phạm vi tên Lookup_sheets chứa danh sách các tên sheet này. Sau đó, sử dụng công thức trên để tra cứu.
Minh họa VLOOKUP với INDIRECT
3. Kết Hợp Các Hàm Để VLOOKUP Động
Để VLOOKUP trả về dữ liệu từ nhiều trang tính hoặc file một cách linh hoạt, bạn có thể kết hợp VLOOKUP với IF hoặc INDIRECT.
-
Sử dụng
IFvới các phạm vi tên: Nếu bạn có các phạm vi tên được định nghĩa trước cho từng sheet (ví dụ:East_Sales,North_Sales), bạn có thể dùng hàmIFđể chọn đúng phạm vi.=VLOOKUP($A2, IF(B$1="east", East_Sales, IF(B$1="north", North_Sales, ...)), 2, FALSE)Công thức này sẽ chọn phạm vi tra cứu dựa trên giá trị ở ô
B1. -
Sử dụng
INDIRECTvới tên file: Để tra cứu dữ liệu từ các file Excel khác nhau, bạn có thể xây dựng chuỗi tham chiếu bao gồm tên file.=IFNA(VLOOKUP($A2, INDIRECT("'[Tên_tệp.xlsx]" & INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'[Tên_tệp.xlsx]" & Lookup_sheets & "'!$A$2:$A$6"), $A2)>0), 0)) & "'!$A$2:$B$5"), 2, FALSE), "Not found")Trong đó,
[Tên_tệp.xlsx]là tên file Excel bạn muốn tra cứu.
4. VLOOKUP Trả Về Nhiều Cột
Khi bạn cần lấy nhiều giá trị liên quan từ bảng tra cứu, VLOOKUP truyền thống chỉ trả về một cột. Để khắc phục điều này, bạn có thể sử dụng VLOOKUP kết hợp với mảng hoặc các hàm khác.
Công thức VLOOKUP giữa 2 sheet và trả về nhiều cột:
=VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'"& Lookup_sheets &"'!$A$2:$C$6"), $A2)>0), 0))&"'!$A$2:$C$6"), {2,3}, FALSE)
{2,3}: Đây là mảng chỉ mục cột, cho biết bạn muốn lấy giá trị từ cột thứ 2 và cột thứ 3.
Khi nhập công thức này, bạn cần chọn một phạm vi ô có cùng số lượng cột với mảng {2,3} (tức là 2 ô), sau đó nhấn Ctrl + Shift + Enter.
Minh họa VLOOKUP trả về nhiều cột
IV. Các Hàm Hỗ Trợ Quan Trọng: COUNTIF, INDIRECT, INDEX, MATCH
Để hiểu sâu hơn về cách VLOOKUP hoạt động trong các tình huống phức tạp, việc nắm vững các hàm hỗ trợ là rất quan trọng:
- COUNTIF: Đếm số lần xuất hiện của một giá trị trong một phạm vi. Khi kết hợp với
INDIRECT, nó giúp xác định sheet nào chứa giá trị cần tìm. - INDIRECT: Chuyển đổi chuỗi văn bản thành tham chiếu ô hoặc phạm vi thực tế. Rất hữu ích để tạo tham chiếu động đến các sheet hoặc file khác nhau.
- INDEX và MATCH: Thường được sử dụng cùng nhau để tìm kiếm và trả về giá trị tại một vị trí cụ thể trong bảng. Chúng linh hoạt hơn VLOOKUP trong một số trường hợp, đặc biệt khi bạn cần tìm kiếm từ trái sang phải hoặc khi cột tìm kiếm không nằm ở vị trí đầu tiên.
INDEX(Lookup_sheets, MATCH(1, {0;0;0;1}, 0))là một cách hiệu quả để xác định tên sheet chứa giá trị cần tìm.
V. Lời Kết
Việc thành thạo hàm VLOOKUP và các kỹ thuật kết hợp với các hàm khác như IFERROR, INDIRECT, INDEX, MATCH sẽ nâng cao đáng kể khả năng xử lý dữ liệu của bạn trong Excel. Từ việc tra cứu đơn giản giữa hai sheet đến việc tổng hợp dữ liệu từ nhiều file phức tạp, những kiến thức này sẽ giúp bạn tiết kiệm thời gian, giảm thiểu sai sót và làm việc hiệu quả hơn. Hãy thực hành thường xuyên với các ví dụ trên để làm chủ hoàn toàn công cụ mạnh mẽ này.
