Tổng hợp cách dụng hàm vlookup giữa 2 sheet và 2 file trong Excel

Hàm VLOOKUP là một trong những hàm phổ biến và hữu ích nhất trong Excel, cho phép bạn tra cứu và lấy dữ liệu từ một bảng hoặc một phạm vi theo một giá trị khóa. Tuy nhiên, nhiều người dùng Excel gặp khó khăn khi sử dụng hàm VLOOKUP giữa 2 sheet hoặc 2 file khác nhau. Bài viết này sẽ tổng hợp các cách dụng hàm VLOOKUP giữa 2 sheet trong Excel chi tiết, cũng như các cách kết hợp hàm VLOOKUP với các hàm khác để tra cứu dữ liệu trên nhiều sheet hoặc file.

Giới thiệu về Hàm Vlookup

Hàm VLOOKUP trong Excel, cho phép bạn tra cứu và lấy dữ liệu từ một bảng hoặc một phạm vi theo một giá trị khóa. Bạn có thể sử dụng hàm VLOOKUP để tìm thông tin, so sánh dữ liệu, kết hợp bảng, tính toán giá trị và nhiều ứng dụng khác. Để sử dụng hàm VLOOKUP, bạn cần biết cú pháp, các tham số và các ví dụ minh họa của hàm.

Cú pháp của hàm VLOOKUP là:

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

Trong đó:

• lookup_value: là giá trị bạn muốn tra cứu, có thể là một số, một chuỗi, một ô hoặc một công thức.

• table_array: là phạm vi chứa dữ liệu bạn muốn lấy, bắt đầu từ cột chứa giá trị khóa. Bạn phải thêm tên sheet trước dấu chấm than (!) để chỉ ra rằng phạm vi này nằm trên sheet khác với sheet hiện tại.

• col_index_num: là số thứ tự của cột trong phạm vi table_array mà bạn muốn lấy dữ liệu.

• [range_lookup]: là một tham số tùy chọn, có thể là TRUE (mặc định) hoặc FALSE. Nếu là TRUE, hàm VLOOKUP sẽ tra cứu giá trị gần nhất với lookup_value trong cột đầu tiên của table_array. Nếu là FALSE, hàm VLOOKUP sẽ tra cứu giá trị chính xác với lookup_value trong cột đầu tiên của table_array.

Ví dụ: Cho bảng dữ liệu. Bạn muốn tra cứu tổng số lượng cam ở tháng 3, hãy sử dụng công thức sau:

1 ví dụ về hàm vlookup sử dụng cho nhiều Sheet

Công thức này sẽ tra cứu tên sản phẩm từ Sheet2 theo mã sản phẩm ở ô A2. Tham số table_array là Sheet2!A:B, tức là phạm vi A:B trên sheet có tên Sheet2. Tham số col_index_num là 2, tức là số thứ tự của cột B trong phạm vi A:B. Tham số [range_lookup] là FALSE, tức là yêu cầu tra cứu giá trị chính xác.

>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"

ĐĂNG KÝ MUA NGAY

Cách dụng hàm vlookup giữa 2 sheet

Cách dụng hàm vlookup giữa 2 sheet đơn giản nhất là sử dụng công thức sau:

=VLOOKUP(lookup_value, Sheet2!table_array, col_index_num, [range_lookup])

Trong đó:

• lookup_value là giá trị bạn muốn tra cứu, có thể là một số, một chuỗi, một ô hoặc một công thức.
• Sheet2!table_array là phạm vi chứa dữ liệu bạn muốn lấy, bắt đầu từ cột chứa giá trị khóa. Bạn phải thêm tên sheet trước dấu chấm than (!) để chỉ ra rằng phạm vi này nằm trên sheet khác với sheet hiện tại.
• col_index_num là số thứ tự của cột trong phạm vi table_array mà bạn muốn lấy dữ liệu.
• [range_lookup] là một tham số tùy chọn, có thể là TRUE (mặc định) hoặc FALSE. Nếu là TRUE, hàm VLOOKUP sẽ tra cứu giá trị gần nhất với lookup_value trong cột đầu tiên của table_array. Nếu là FALSE, hàm VLOOKUP sẽ tra cứu giá trị chính xác với lookup_value trong cột đầu tiên của table_array.

Ví dụ: Cho hai bảng tính 1 và 2 như dưới đây. Trong đó, Sheet 1 là mục danh sách các nhân viên đang cần nhập số tiền thưởng tết. Sheet 2 là những điều kiện xếp loại ứng với số tiền thưởng tết.

Yêu cầu: Dùng hàm vlookup để tính ra số tiền thưởng tết của mỗi nhân viên.

Áp dụng hàm Vlookup giữa 2 sheet (1)

Để tính ra số tiền thưởng tết của mỗi nhân viên thông qua cả 2 Sheet1 và Sheet2. Bạn có thể sử dụng công thức sau tại ô D3:

D3=VLOOLUP(C3,Thuong!$A$1:$D$3)

Áp dụng hàm Vlookup giữa 2 sheet (2)

Giữ nguyên công thức vừa nhập, bạn nhấn chọn vào sheet 2 và chọn vùng dữ liệu chứa điều kiện lương A1: B4 sau đó sử dụng F4 để cố định lần lượt các ô dữ liệu A1:B4.

Áp dụng hàm Vlookup giữa 2 sheet (3)

Quay lại Sheet 1, thêm (2,1) vào cuối công thức vừa thiết lập như hình dưới đây:

Áp dụng hàm Vlookup giữa 2 sheet (4)

Nhấn Enter, sau khi kết quả ở ô D3 xuất hiện, bạn kéo công thức xuống dưới các ô còn lại của cột lương thưởng sẽ thu được kết quả chính xác như hình dưới đây:

Áp dụng hàm Vlookup giữa 2 sheet (5)

Cách dụng hàm vlookup giữa 2 file

Ngoài cách dụng hàm vlookup giữa 2 sheet, bạn có thể áp dụng giữa 2 file. Cách dùng tương tự, chỉ khác là bạn phải thêm đường dẫn của file chứa phạm vi table_array vào công thức. Công thức có dạng như sau:

=VLOOKUP(lookup_value, ‘[File_path]Sheet_name’!table_array, col_index_num, [range_lookup])

Trong đó:

• [File_path] là đường dẫn của file chứa phạm vi table_array, bao gồm tên file và phần mở rộng. Bạn có thể nhập đường dẫn bằng tay hoặc chọn file từ hộp thoại mở file khi nhập công thức. Bạn phải đặt đường dẫn trong dấu ngoặc vuông ([]).
• Sheet_name là tên của sheet chứa phạm vi table_array trong file đó. Bạn phải đặt tên sheet trong dấu nháy đơn (‘’).

Ví dụ: Bạn có 2 file như sau:

File thứ nhất gồm các thông tin về người nhận tiền thưởng.

Áp dụng hàm Vlookup giữa 2 file (1)

File thứ hai chứa thông tin về điều kiện thưởng:

Áp dụng hàm Vlookup giữa 2 file (2)

Để mở cùng lúc 2 file này trên màn hình làm việc, bạn chỉ cần mở 1 file ra trước, sau đó dưới thanh taskbar click chuột phải vào microsoft excel chọn file thứ 2 là xong.

Bước 1: Tại ô D3 nhập công thức Vlookup như hình dưới đây:

Áp dụng hàm Vlookup giữa 2 file (3)

Bước 2: Chuyển sang File 2, trước đó, bạn nên đặt tên cho file 2 là dulieu.xlsx, chọn vùng dữ liệu A1: B4. Sau đó, bạn dùng phím F4 và nhấn chọn từng ô dữ liệu trong vùng A1: B4 để cố định dữ liệu trong khu vực này.

Áp dụng hàm Vlookup giữa 2 file (4)

Bước 3: Quay lại File 1, nhập thêm (2,0) vào dưới cùng công thức rồi nhấn Enter. Sau khi kết quả được trả về, bạn thực hiện sao chép công thức sẽ nhận được toàn bộ kết quả thưởng của từng nhân viên.

Áp dụng hàm Vlookup giữa 2 file (5)

Lưu ý: Để sử dụng hàm VLOOKUP ở 2 file khác nhau, bạn phải mở cả 2 file đồng thời. Nếu bạn đóng file chứa phạm vi table_array, công thức sẽ không hoạt động và hiển thị lỗi #REF!.

Cách dùng hàm vlookup nhiều sheet kết hợp với hàm IFERROR

Nếu bạn muốn tra cứu dữ liệu từ nhiều sheet khác nhau, bạn có thể áp dụng cách dùng hàm VLOOKUP với hàm IFERROR để xử lý các trường hợp không tìm thấy giá trị khóa. Hàm IFERROR có cú pháp như sau:

=IFERROR(value, value_if_error)

Trong đó:

• value: là giá trị hoặc công thức bạn muốn kiểm tra lỗi.
• value_if_error: là giá trị hoặc công thức bạn muốn trả về nếu value bị lỗi.

Ví dụ: Bạn có 3 sheet như sau:

Ví dụ 3 sheet

Bạn muốn tra số lượng hoa quả ở Sheet1 theo Sheet2 và Sheet3 thì cần làm theo các bước sau: 

Bước 1: Chọn chọn ô C2 cần điền số lượng sản phẩm và lồng hàm VLOOKUP vào hàm IFERROR. Tại đây, bạn nhập công thức như hình dưới đây:

Nhập công thức vào ô C2

Bước 2: Ta chuyển qua sheet 2 và kéo chọn từ ô A2:B5 xong bấm F4 để cố định dòng và cột và nhập thêm "2,FALSE)" vào công thức như hình dưới đây.

Nhập thêm "2,FALSE)" vào cuối công thức

Bước 3: Lặp lại thao tác ở bước 1, sau đó chuyển qua sheet3 và thực hiện tương tự bước 2. Lưu ý, ở bước này bạn cần nhập thêm cụm từ  "Not found" vào công thức để đề phòng nếu không tìm thấy dữ liệu sẽ trả về giá trị "Not found". Quay trở lại sheet1 (slsp) là thành công.

Công thức đầy đủ

Bước 4: Kéo công thức từ dòng C2 xuống C10 để trả về kết quả tìm kiếm cho tất .

Bảng kết quả

Công thức này sẽ tra cứu điểm thi của sinh viên từ Sheet2 trước, nếu không tìm thấy giá trị khóa, sẽ tra cứu tiếp từ Sheet3. Nếu vẫn không tìm thấy giá trị khóa, sẽ trả về lỗi #N/A.

>>> Xem thêm: Hàm VLOOKUP kết hợp với hàm IF sử dụng thế nào? Ví dụ cụ thể

Hàm VLOOKUP trong nhiều cửa sổ làm việc

Cũng giống như khi dùng hàm VLOOKUP giữa 2 File, khi bạn dùng hàm VLOOKUP giữa nhiều cửa sổ làm việc thì hãy lồng hàm này bên trong hàm IFERROR. Cùng với đó, bạn cần đặt tên cửa sổ làm việc trong dấu ngoặc vuông và đặt nó làm tiền tố trước tên trang tính. 

Công thức tổng quát sau:

=IFERROR(VLOOKUP(B2, [file1.xlsx]dulieu1!$B$2:$C$6, 2, FALSE), IFERROR(VLOOKUP(B2, [file2.xlsx]dulieu2!$B$2:$C$6, 2, FALSE),"Not found"))

Làm cho địa chỉ cột thành Vlookup nhiều cột

Nếu bạn muốn tra cứu dữ liệu từ nhiều cột khác nhau trong cùng một phạm vi table_array, bạn có thể sử dụng hàm VLOOKUP kết hợp với hàm COLUMN theo cú pháp như sau:

=IFERROR(VLOOKUP(lookup_value, table_array, COLUMN(column_index_range), False), "")

Trong đó:

• Lookup_value: Là giá trị mà bạn muốn tìm kiếm
• Table_array: Là phạm vi dữ liệu trong đó bạn muốn tìm kiếm giá trị
• Column_index_range: Là phạm vi cột mà bạn muốn trả về kết quả

Ví dụ: Bạn có sheet như sau:

Ví dụ cho một sheet

Yêu cầu: tìm tên của một loại trái cây dựa trên ID của nó, ví dụ tìm tên của Orange theo mã ID là 303. 

Giả sử giá trị cần tìm (lookup_value) nằm trong ô A4 và bảng dữ liệu (table_array) nằm trong các ô A2:B6.

Bạn có thể sử dụng công thức dưới đây ở ô E2 để lấy tên trái cây:

E2=IFERROR(VLOOKUP(A$, $A$1:$B$5, 2, FALSE), "")

Trong đó:

• VLOOKUP(A4, $A$1:$B$5, 2, FALSE): Phép tính này sẽ tìm giá trị trong ô A2 (303) trong cột đầu tiên của phạm vi bảng A1:B5. Vì column_index_range là 2, nó sẽ trả về giá trị tương ứng từ cột thứ hai (B). Trong trường hợp này, nó sẽ tìm thấy "Orange" trong ô B4.
• IFERROR(result, ""): Nếu hàm VLOOKUP trả về một kết quả hợp lệ (ví dụ: "Orange"), hàm IFERROR sẽ trả về giá trị đó. Tuy nhiên, nếu hàm VLOOKUP gặp phải lỗi (giá trị lookup_value không có trong table_array), nó sẽ trả về một chuỗi trống ("").

Nếu thực hiện đúng, công thức sẽ trả về "Orange" vì lookup_value (303) được tìm thấy trong cột đầu tiên của bảng và "Orange" là giá trị tương ứng trong cột thứ hai.

Công thức trả về giá trị đúng

Nếu lookup_value là 949 (mã không có trong bảng), công thức sẽ trả về một chuỗi trống ("") vì hàm VLOOKUP sẽ gặp lỗi, và hàm IFERROR sẽ xử lý lỗi đó bằng cách cung cấp một chuỗi trống làm kết quả.

Công thức trả về giá trị bị lỗi

Hàm VLOOKUP trên nhiều trang tính kết hợp với hàm INDIRECT

Nếu bạn muốn tra cứu dữ liệu từ nhiều trang tính khác nhau trong cùng một file, bạn có thể sử dụng hàm VLOOKUP kết hợp với hàm INDIRECT. Cú pháp như sau:

VLOOKUP(lookup_value, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, –(COUNTIF(INDIRECT("'" & Sheet_list & "'!lookup_range"), lookup_value)>0), 0)) & "'!table_array"), col_index_num, FALSE)

Trong đó:

• Lookup_sheets: Là tên trang tính cần tra cứu

• Lookup_value: Giá trị cần tìm kiếm

• Lookup_range: Phạm vi cột trong trang tính tra cứu nơi tìm kiếm giá trị tra cứu

• Table_array: Chuỗi dữ liệu trong trang tính

• -Col_index_num: Số cột trả về một giá trị trong bảng

Ví dụ: Bạn có 4 trang tính như sau:

Bảng ví dụ

Yêu cầu: Tìm kiếm giá trị "X" trong các sheet này và trả về giá trị từ cột thứ 2 của phạm vi tìm kiếm.

Công thức sẽ tìm kiếm giá trị "X" trong cột A từ hàng 2 đến hàng 6 trên các sheet "Sheet1", "Sheet2", "Sheet3" và "Sheet4". Nếu tìm thấy, nó sẽ trả về giá trị tương ứng từ cột B, tức là "Value1" (từ sheet "Sheet1"). Trong trường hợp không tìm thấy, công thức sẽ trả về lỗi #N/A.

Cách sử dụng công thức để VLOOKUP trên các trang tính

Sử dụng ví dụ trên để làm rõ cách công thức để VLOOKUP trên các trang tính cùng một lúc. Các bước thực hiện như sau:

Bước 1: Viết tên của tất cả các trang tính mà bạn muốn tra cứu vào trong cửa sổ làm việc của mình và đặt tên cho phạm vi đó. Ở ví dụ, chúng tôi sẽ tạo một phạm vi là "Lookup_sheet" và liệt kê tên các trang tính vào đó.

Tạo phạm vi

Bước 2: Đồng bộ dữ liệu của bạn. Ví dụ sau đây mô tả cách thực hiện công thức VLOOKUP trên nhiều trang tính:

• lookup_value: Tìm kiếm giá trị trong ô A2.
• Phạm vi từ A2 - A6 (lookup_range) trên bốn trang tính (East, North, South và West).
• Trích xuất giá trị từ cột B (cột thứ 2) (col_index_num) trong phạm vi dữ liệu từ A2 đến C6 (table_array).

Công thức sử dụng:

=VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheet, MATCH(1, --(COUNTIF(INDIRECT("'"& Lookup_sheet&"'!$A$2:$A$6"), $A2)>0), 0))&"'!$A$2:$B$5"), 2, FALSE)

Lưu ý: Trong công thức trên, hai phạm vi ($A$2:$A$6) và ($A$2:$B$5) đều được cố định bằng cách sử dụng tham chiếu ô tuyệt đối.

Bước 3: Nhập công thức vào ô C2, sau đó nhấn tổ hợp phím Ctrl + Shift + Enter để hoàn thành công thức dạng mảng. Điều này sẽ áp dụng công thức cho tất cả các ô trong cột.

Bước 4: Để sao chép công thức xuống cột, bạn nhấp đúp chuột vào ô C2 hoặc kéo nốt fill handle (nút đen ở góc dưới cùng bên phải của ô đang chọn). Thao tác này sẽ áp dụng công thức cho tất cả các ô trong cột và trả về giá trị tương ứng từ các trang tính khác nhau.

Kết quả vẫn còn lỗi N/A

Nếu không tìm thấy giá trị cụ thể, công thức sẽ trả về lỗi #N/A. Cách khắc phục lỗi này là đưa hàm IFNA vào công thức:

=IFNA(VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheet, MATCH(1, --(COUNTIF(INDIRECT("'"& Lookup_sheet&"'!$A$2:$A$6"), $A2)>0), 0))&"'!$A$2:$B$5"), 2, FALSE),"Not found")

Bảng kết quả trả về như sau:

Kết quả sau khi khắc phục lỗi N/A

VLOOKUP nhiều trang tính giữa các cửa sổ làm việc

Thay vì làm việc giữa các trang tính, bạn sẽ sử dụng hàm VLOOKUP để làm việc trên nhiều cửa sổ làm việc. Công thức chung như sau:

=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")

Với công thức tổng quát bên trên, bạn cần thay thế [Tên_tệp.xlsx] bằng tên của tệp Excel mình cần tra cứu trên đó. Nếu tên tệp là "Book2.xlsx", bạn sẽ thay thế [Tên_tệp.xlsx] thành "Book2.xlsx".

Đồng thời, bạn cũng cần chắc chắn rằng các trang tính trong tệp Excel đó có tên tương ứng với những gì được liệt kê trong phạm vi tên "Lookup_sheets".

Từ công thức chúng tôi vừa gợi ý bên trên, bạn có thể áp dụng hàm VLOOKUP trên nhiều trang tính giữa các cửa sổ làm việc khác nhau. Nếu không tìm thấy giá trị tìm kiếm, công thức sẽ trả về "Not found".

Kết quả VLOOKUP nhiều trang tính giữa các cửa sổ làm việc

Hàm vlookup trong excel 2 sheet và trả về nhiều cột

Nếu bạn muốn tra cứu dữ liệu giữa 2 sheet và trả về nhiều cột, bạn có thể sử dụng hàm VLOOKUP kết hợp với hàm INDIRECT và INDEX theo cú pháp như sau:

=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)

Trong đó:

• $A2: Là giá trị cần tìm kiếm.

• Lookup_sheets: Là phạm vi tên trang tính mà bạn đã đặt trước đó, chứa tên của các trang tính mà bạn muốn tra cứu.

• INDEX(Lookup_sheets, MATCH(1, –(COUNTIF(INDIRECT("'"& Lookup_sheets &'"!$A$2:$C$6"), $A2)>0), 0)): Sẽ trả về tên của trang tính chứa giá trị tìm kiếm. Công thức này sử dụng hàm MATCH để tìm vị trí của giá trị tìm kiếm trong phạm vi từ A2:C6 trên các trang tính và sau đó sử dụng hàm INDEX để trích xuất tên của trang tính.

• INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, –(COUNTIF(INDIRECT("'"& Lookup_sheets &"'!$A$2:$C$6"), $A2)>0), 0)) &"'!$A$2:$C$6"): Tạo một tham chiếu động đến phạm vi dữ liệu trên trang tính được xác định bởi tên trang tính.

• {2,3}: Là một mảng chỉ mục cột. Công thức VLOOKUP sẽ trả về giá trị trong cột thứ 2 và 3 của phạm vi dữ liệu.

• FALSE: Đảm bảo hàm VLOOKUP sẽ tìm kiếm giá trị chính xác.

Kết quả hàm VLOOKUP giữa 2 sheet và trả về nhiều cột

Lỗi không thực hiện được công thức vlookup giữa 2 sheet và cách khắc phục

Một trong những lỗi thường gặp khi sử dụng hàm VLOOKUP giữa 2 file khác nhau là lỗi #REF!, có nghĩa là Excel không thể tìm thấy phạm vi table_array bạn đã chỉ định. Nguyên nhân có thể là:

• Bạn đã đóng file chứa phạm vi table_array khi nhập công thức. Điều này sẽ khiến Excel không thể liên kết đến file đó và hiển thị lỗi #REF!.

• Bạn đã di chuyển, đổi tên hoặc xóa file chứa phạm vi table_array sau khi nhập công thức. Điều này sẽ khiến Excel không thể tìm thấy file đó và hiển thị lỗi #REF!.

• Bạn đã thay đổi tên sheet hoặc cấu trúc của phạm vi table_array sau khi nhập công thức. Điều này sẽ khiến Excel không thể tìm thấy phạm vi table_array bạn đã chỉ định và hiển thị lỗi #REF!.

• Để khắc phục lỗi này, bạn có thể thực hiện các phương pháp sau:

• Kiểm tra lại đường dẫn của file chứa phạm vi table_array trong công thức và đảm bảo rằng file đó vẫn tồn tại và không bị di chuyển, đổi tên hoặc xóa.

• Mở file chứa phạm vi table_array và giữ nó mở khi nhập hoặc tính toán lại công thức.

• Kiểm tra lại tên sheet và cấu trúc của phạm vi table_array trong file đó và đảm bảo rằng chúng không bị thay đổi sau khi nhập công thức.

• Nếu bạn đã thay đổi tên sheet hoặc cấu trúc của phạm vi table_array, bạn phải cập nhật lại công thức để phản ánh những thay đổi đó.

Lỗi và cách sửa lỗi hàm VLOOKUP

Tổng kết

Hàm VLOOKUP là một hàm rất hữu ích trong Excel, cho phép bạn tra cứu và lấy dữ liệu từ một bảng hoặc một phạm vi theo một giá trị khóa. Tuy nhiên, để sử dụng hàm VLOOKUP hiệu quả, bạn cần nắm vững các cách dụng hàm vlookup giữa 2 sheet hoặc 2 file khác nhau, cũng như các cách kết hợp hàm VLOOKUP với các hàm khác để tra cứu dữ liệu trên nhiều sheet hoặc file. Hy vọng bài viết này sẽ giúp bạn sử dụng hàm VLOOKUP một cách thành thạo và hiệu quả trong Excel.