10 Cách sử dụng hàm VLOOKUP giữa 2 sheet, 2 file

Có phải bạn đang tìm cách sử dụng hàm VLOOKUP giữa 2 sheet, 2 file khác nhau ở trong Excel đơn giản, chi tiết? Trong bài viết này, Sforum sẽ giải đáp cho bạn vấn đề này. Từ đó, góp phần giúp bạn sử dụng hàm VLOOKUP trong excel được hiệu quả hơn trong công việc và học tập.

Cách sử dụng hàm VLOOKUP giữa 2 sheet khác nhau

Cách sử dụng hàm VLOOKUP ở 2 sheet khác nhau với công thức chung như sau:

VLOOKUP(lookup_value, Sheet!range, col_index_num, [range_lookup])

Ví dụ có hai sheet gồm:

• Sheet 1 (nhanvien): Danh sách nhân viên
• Sheet 2 (thuong): Điều kiện xếp loại mức tiền thưởng.

Bước 1: Bạn hãy nhập công thức vào một ô cho một nhân viên bất kỳ. Ở đây, bạn nhập vào ô D3 =VLOOLUP(C3,thuong!$B$4:$C$6,2,FALSE)

Bước 2: Kéo công thức từ D3 đến D7 để nhận kết quả. Như vậy là bạn đã hoàn thành cách sử dụng hàm VLOOKUP giữa 2 sheet khác nhau ở trong Excel đơn giản.

Máy tính là một thiết bị không thể thiếu nếu bạn muốn sử dụng Excel hiệu quả. Hiện nay, có đa dạng dòng máy tính với hiệu năng tốt cùng nhiều mức giá khác nhau. Dưới đây là một số mẫu máy tính thông dụng được nhiều người lựa chọn tại CellphoneS:

Cách sử dụng hàm VLOOKUP giữa 2 file khác nhau

Bên cạnh cách sử dụng hàm VLOOKUP giữa 2 sheet trong Excel, bạn cũng có thể áp dụng công thức sử dụng ở 2 file khác nhau qua ví dụ dưới đây. Đầu tiên, bạn cần tạo 2 file:

• File 1 (hocsinh): Danh sách học sinh cần xếp loại

• File 2 (xeploai): Điều kiện để xếp loại học sinh

Các bước sử dụng hàm VLOOKUP ở 2 file khác nhau cũng đơn giản như cách sử dụng hàm VLOOKUP giữa 2 sheet trong Excel như sau:

Bước 1: Nhập công thức vào ô D2 như hình bên dưới.

Bước 2: Qua file 2 (được tạo với tên dulieu.xlsx). Nhấn Ctrl rồi kéo từ ô A4 đến B7 để chọn vùng dữ liệu A4:B7.

Bước 3: Quay về file 1, gõ thêm vào công thức '2, TRUE' để ra kết quả. Tương tự với cách sử dụng hàm VLOOKUP giữa 2 sheet, bạn cũng kéo công thức từ D2 đến D6 để nhận kết quả xếp loại chính xác.

Kết hợp hàm VLOOKUP với hàm IFERROR trong nhiều cửa sổ

Cách dùng hàm VLOOKUP kết hợp hàm IFERROR trong nhiều cửa sổ khá giống với cách sử dụng hàm VLOOKUP giữa 2 file. Bạn lòng hàm VLOOKUP vào IFERROR rồi để tên cửa sổ trong dấu ngoặc vuông. Chú ý là đặt nó trước tên trang tính, bạn sẽ có công thức ví dụ như 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'))

Cách dùng hàm VLOOKUP trả dữ liệu từ nhiều trang tính vào những ô khác nhau

Sau khi biết được cách sử dụng hàm VLOOKUP giữa 2 sheet, 2 file ở trong Excel, bạn cũng nên tìm hiểu cách dùng hàm VLOOKUP để trả dữ liệu từ các trang tính vào những ô khác nhau. Sau đây là hướng dẫn chi tiết mà bạn nên tham khảo.

Kết hợp hàm VLOOKUP với hàm IF

Công thức kết hợp VLOOKUP với hàm IF cho hình bên dưới như sau:

=VLOOKUP($A2, IF(B$1='east', East_Sales, IF(B$1='north', North_Sales, IF(B$1='south', South_Sales, IF(B$1='west', West_Sales)))), 2, FALSE)

• B1 là EAST thì tìm tại phạm vi có tên East_Sales. Còn B1 là North thì tìm trong phạm vi có tên North_Sales. Tương tự như vậy đối với South và West.
• Hàm IF sẽ trả về một phạm vi rồi chuyển qua table_array của hàm VLOOKUP. Dải này sẽ kéo giá trị khớp với cột thứ 2 tương ứng trên trang tính.
• Dùng tham chiếu hỗn hợp (mixed reference) cho ra giá trị ($A2 – cột tuyệt đối và hàng tương đối). Cùng với phép kiểm tra logic IF (B$1 – cột tương đối và hàng tuyệt đối). Từ đó, giúp bạn sao chép công thức qua những ô khác mà không có sự thay đổi. Excel sẽ tự điều chỉnh tham chiếu dựa trên vị trí của cột và hàng.

Sử dụng công thức VLOOKUP INDIRECT

Sử dụng hàm VLOOKUP INDIRECT theo công thức bên dưới như sau:

=VLOOKUP(B2, INDIRECT('GIAMGIA'), 2, 0)

Hàm VLOOKUP trong công thức trên giúp tìm kiếm giá trị trong một phạm vi cụ thể. Sau đó trả về giá trị liên quan ở cột khác trong phạm vi nhất định đó. Còn hàm INDIRECT được dùng để chuyển một dãy văn bản thành một tham chiếu ở trong công thức đó.

Cách sử dụng hàm VLOOKUP kết hợp hàm INDIRECT giữa 2 sheet

Dưới đây là cách sử dụng hàm VLOOKUP giữa 2 sheet khác nhau kết hợp với hàm INDIRECT ở trong Excel. Công thức được áp dụng 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)

Sử dụng công thức VLOOKUP trên nhiều trang tính

Bước 1: Tạo một phạm vi tên là 'Lookup_sheet' rồi liệt kê tên những trang tính vào.

Bước 2: Thực hiện đồng bộ dữ liệu, tham khảo mô tả chi tiết công thức dưới đây.

• Tìm giá trị ô A2 (lookup_value).
• Phạm vi A2-A6 (lookup_range) ở 4 trang tính (East, North, South, West).
• Trích xuất giá trị cột B (col_index_num) ở phạm vi A2-C6 (table_array).

Ta có công thức:

=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 ý: Hai phạm vi ($A$2:$A$6 và $A$2:$B$5) được cố định bằng việc dùng tham chiếu ô tuyệt đối.

Bước 3: Nhập công thức ở ô đầu tiên (C2) rồi nhấn Ctrl + Shift + Enter. Nhấn đúp chuột vào C2, kéo công thức áp dụng cho cả cột. Trang tính trả về kết quả, công thức hiển thị #N/A nếu không tìm thấy giá trị.

Để khắc phục lỗi #N/A trong cách sử dụng hàm VLOOKUP giữa 2 sheet này, hãy nhập 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')

Tìm kiếm dữ liệu bằng hàm VLOOKUP và INDIRECT giữa nhiều cửa sổ làm việc

Công thức hàm 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')

• Thay thế [Tên_tệp.xlsx] bằng tên tệp Excel muốn tra cứu.
• Các trang tính trong tệp cần có tên tương ứng với những gì được liệt kê trong 'Lookup_sheets'.
• Công thức trả về kết quả 'Not found' nếu không tìm thấy giá trị tìm kiếm.

Kết hợp hàm VLOOKUP và INDIRECT giữa 2 sheet để trả kết quả về nhiều cột

Cách sử dụng hàm VLOOKUP giữa 2 sheet khác nhau kết hợp với hàm INDIRECT ở trong Excel có công thức 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)

• $A2: Giá trị cần tìm
• Lookup_sheets: Phạm vi tên trang tính, chứa tên trang tính muốn tra cứu.
• INDEX(Lookup_sheets, MATCH(1, –(COUNTIF(INDIRECT('''& Lookup_sheets &''!$A$2:$C$6'), $A2)0), 0)): Trả về tên trang tính chứa giá trị cần tìm.
• 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 sheet được xác định bởi tên trang tính.
• {2,3}: Chỉ mục cột.
• FALSE: Đảm bảo tìm kiếm giá trị chính xác.

Cách kết hợp hàm VLOOKUP và INDIRECT dựa trên kết quả của hàm INDEX và MATCH

Công thức kết hợp hai hàm MATCH và INDEX là: INDEX(Lookup_sheets, MATCH(1, {0;0;0;1}, 0)). Trong đó:

• Dùng hàm MATCH tìm giá trị 1 trong {0;0;0;1} và trả về vị trí 4: INDEX(Lookup_sheets, 4)
• Hàm INDEX dùng giá trị trả về từ MATCH rồi trả về giá trị 4 trong phạm vi được đặt tên là lookup_sheet (WEST).

Công thức tối giản khi kết hợp của các hàm: VLOOKUP($A2, INDIRECT(“‘”&”West”&”‘!$A$2:$C$6”), 2, FALSE)

Ví dụ: Bạn có tệp excel có sheet tên “West”. Đang muốn tìm giá trị của cột A từ hàng 2 đến 5 tại sheet West và cho ra giá trị từ cột A. Trong bảng dữ liệu như bảng dưới đây, A2 là “Cam” trên sheet “West”. Công thức sẽ tìm giá trị “Cam” cột A, hàng 2 đến 5 ở sheet “West”. Sau đó, trả về giá trị cột B ở phạm vi tìm kiếm, là “100”

Cách sử dụng hàm VLOOKUP giữa 2 sheet từ kết quả của hàm COUNTIF và INDIRECT

Công thức như sau:

VLOOKUP($A2, INDIRECT('''&INDEX(Lookup_sheets, MATCH(1, {0;0;0;1}, 0)) &''!$A$2:$C$6'), 2, FALSE)

Ví dụ: Bạn có danh sách các sheet tên 'Sheet1', 'Sheet2', 'Sheet3', 'Sheet4'. Muốn tìm giá trị cột A từ hàng 2 đến 6 trên Sheet4. Sau đó, trả về giá trị từ cột 2 ở phạm vi tìm kiếm.

Ta có bảng dữ liệu với A2 là 'X' trên Sheet4. Công thức tìm giá trị 'X' cột A từ hàng 2 đến 6 trên Sheet4. Sau đó, trả về giá trị ở cột B là 'Value1'

Lưu ý về cách sử dụng hàm VLOOKUP giữa 2 file, 2 sheet

Ngoài việc biết cách sử dụng hàm VLOOKUP trong Excel giữa 2 file, 2 sheet khác nhau ở trong Excel, bạn cũng nên biết về một số lưu ý khi áp dụng hàm như sau:

• Khi bạn đóng file chứa table_array trong lúc nhập sẽ khiến Excel không kết nối được với file và xuất hiện #REF!.
• Đổi tên, di chuyển, xóa file chứa table_array khi nhập công thức làm Excel không tìm được file đó và báo #REF!.
• Đổi tên sheet hoặc cấu trúc table_array khi đã nhập công thức khiến Excel không tìm được table_array và hiện lỗi #REF!.

Trên đây là toàn bộ thông tin về cách sử dụng hàm VLOOKUP giữa 2 sheet, 2 file khác nhau ở trong Excel. Hy vọng bạn sẽ thực hiện thành công và hỗ trợ cho công việc, học tập thật tốt. Nếu muốn thêm về hàm VLOOKUP, hãy để lại bình luận bên dưới bài viết bạn nhé!

• Xem thêm bài viết:Tin học văn phòng