Buzz
Bạn muốn biết cách sử dụng hàm VLOOKUP giữa 2 sheet, 2 file khác nhau trong Excel một cách dễ dàng? Hãy theo dõi hướng dẫn dưới đây để làm điều đó!
Bí quyết sử dụng hàm VLOOKUP giữa 2 sheet khác nhau
Thủ thuật sử dụng hàm VLOOKUP cho 2 sheet khác nhau trong Excel đây!
Hướng dẫn cụ thể sử dụng hàm VLOOKUP với 2 sheet khác nhau
Dưới đây là ví dụ minh họa cho việc sử dụng hàm VLOOKUP trên 2 sheet
-
Sheet 1 (nhanvien): Danh sách đầy đủ thông tin của nhân viên
Bước 1: Nhập công thức cho một nhân viên bất kỳ 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ả. Đã hoàn thành cách sử dụng hàm VLOOKUP giữa 2 sheet khác nhau trong Excel.
Máy tính là công cụ quan trọng giúp bạn sử dụng Excel hiệu quả. Tại Mytour, bạn có thể lựa chọn máy tính phù hợp với nhu cầu và ngân sách của mình.
Cách sử dụng hàm VLOOKUP giữa 2 file khác nhau
Ngoài việc sử dụng hàm VLOOKUP giữa 2 sheet trong Excel, bạn có thể áp dụng công thức này cho 2 file khác nhau như sau:
-
File 1 (hocsinh): Danh sách học sinh cần phân loại
-
File 2 (xeploai): Điều kiện để xếp loại học sinh
Dùng hàm VLOOKUP giữa hai file không phức tạp như dùng giữa hai sheet trong Excel.
Bước 1: Nhập công thức vào ô D2 như ảnh dưới đây.
Bước 2: Mở file 2 (đặt tên là dulieu.xlsx). Nhấn Ctrl và kéo từ ô A4 đến B7 để chọn dữ liệu A4:B7.
Bước 3: Quay lại file 1, gõ thêm vào công thức '2, TRUE' để nhận kết quả. Tương tự với cách dùng hàm VLOOKUP giữa hai sheet, bạn 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ổ
Việc sử dụng VLOOKUP kết hợp IFERROR trong nhiều cửa sổ tương tự như sử dụng VLOOKUP giữa hai file. Bạn sử dụng VLOOKUP trong IFERROR sau đó đặt tên cửa sổ trong dấu ngoặc vuông. Lưu ý đặt trước tên trang tính. Ví dụ:
=IFERROR(VLOOKUP(B2, [file1.xlsx]dulieu1!$B$2:$C$6, 2, FALSE), IFERROR(VLOOKUP(B2, [file2.xlsx]dulieu2!$B$2:$C$6, 2, FALSE),'Không tìm thấy'))
Cách sử dụng hàm VLOOKUP để lấy dữ liệu từ nhiều trang tính và đưa vào ô khác nhau
Sau khi biết cách sử dụng VLOOKUP giữa 2 sheet, 2 file trong Excel, bạn cũng cần tìm hiểu cách sử dụng VLOOKUP để trả dữ liệu từ các trang tính vào các ô khác nhau. Dưới đâ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ách kết hợp VLOOKUP với hàm IF cho bức tranh dưới đây như sau:
=VLOOKUP($A2, IF(B$1='đông', Bán_hàng_đông, IF(B$1='bắc', Bán_hàng_bắc, IF(B$1='nam', Bán_hàng_nam, IF(B$1='tây', Bán_hàng_tây)))), 2, FALSE)
- B1 là ĐÔNG thì tìm tại phạm vi có tên Bán_hàng_đông. Còn B1 là Bắc thì tìm trong phạm vi có tên Bán_hàng_bắc. Tương tự như vậy đối với Nam và Tây.
- 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('GIẢM_GIÁ'), 2, 0)
Trong công thức, VLOOKUP giúp tìm kiếm giá trị trong một phạm vi và trả về giá trị tương ứng ở cột khác trong phạm vi đó. INDIRECT được sử dụng để chuyển đổi một chuỗi văn bản thành một tham chiếu trong công thức.
Cách kết hợp hàm VLOOKUP với INDIRECT giữa 2 sheet
Dưới đây là cách sử dụng VLOOKUP giữa 2 sheet khác nhau kết hợp với INDIRECT trong Excel. Công thức 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 có tên là 'Danh_sách_tìm_kiếm' và liệt kê tên các trang tính vào.
Bước 2: Thực hiện đồng bộ dữ liệu và tham khảo mô tả chi tiết của công thức dưới đây.
- Tìm giá trị ô A2 (lookup_value).
- Phạm vi A2-A6 (lookup_range) trên 4 trang tính (Đông, Bắc, Nam, Tây).
- Trích xuất giá trị cột B (col_index_num) trong phạm vi A2-C6 (table_array).
Công thức:
=VLOOKUP($A2, INDIRECT('''&INDEX(Danh_sách_tìm_kiếm, MATCH(1, --(COUNTIF(INDIRECT('''& Danh_sách_tìm_kiếm & ''!$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 cách sử dụng tham chiếu ô tuyệt đối.
Bước 3: Nhập công thức vào ô đầu tiên (C2), sau đó nhấn Ctrl + Shift + Enter. Nhấn đúp chuột vào C2 và kéo công thức áp dụng cho cả cột. Trang tính sẽ trả về kết quả, và công thức sẽ hiển thị #N/A nếu không tìm thấy giá trị.
Để sửa lỗi #N/A khi sử dụng hàm VLOOKUP giữa 2 sheet, hãy nhập công thức sau:
=IFNA(VLOOKUP($A2, INDIRECT('''&INDEX(Danh_sách_tìm_kiếm, MATCH(1, --(COUNTIF(INDIRECT('''& Danh_sách_tìm_kiếm & ''!$A$2:$A$6'), $A2)>0), 0))&''!$A$2:$B$5'), 2, FALSE),'Không tìm thấy')
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(Danh_sách_tìm_kiếm, MATCH(1, --(COUNTIF(INDIRECT(''[Tên_tệp.xlsx]' & Danh_sách_tìm_kiếm & ''!$A$2:$A$6'), $A2)>0), 0)) & ''!$A$2:$B$5'), 2, FALSE), 'Không tìm thấy')
- Thay thế [Tên_tệp.xlsx] bằng tên của tệp Excel muốn tra cứu.
- Các trang tính trong tệp cần phải có tên tương ứng với những gì được liệt kê trong 'Danh_sách_tìm_kiếm'.
- Công thức sẽ trả về kết quả 'Không tìm thấy' 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ả về kết quả của 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(Danh_sách_tìm_kiếm, MATCH(1, –(COUNTIF(INDIRECT('''& Danh_sách_tìm_kiếm &''!$A$2:$C$6'), $A2)>0), 0)) &''!$A$2:$C$6'), {2,3}, FALSE)
- $A2: Giá trị cần tìm
- Danh_sách_tìm_kiếm: Phạm vi tên trang tính, chứa tên trang tính cần tra cứu.
- INDEX(Danh_sách_tìm_kiếm, MATCH(1, –(COUNTIF(INDIRECT('''& Danh_sách_tìm_kiếm &''!$A$2:$C$6'), $A2)>0), 0)): Trả về tên trang tính chứa giá trị cần tìm.
- INDIRECT('''&INDEX(Danh_sách_tìm_kiếm, MATCH(1, –(COUNTIF(INDIRECT('''& Danh_sách_tìm_kiếm &''!$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(Danh_sách_tìm_kiếm, 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(Danh_sách_tìm_kiếm, 4)
- Hàm INDEX sử dụng giá trị trả về từ MATCH để trả về giá trị 4 trong phạm vi được đặt tên là danh_sách_tìm_kiếm (TÂY).
Công thức tối giản khi kết hợp các hàm: VLOOKUP($A2, INDIRECT(“‘”&”Tây”&”‘!$A$2:$C$6”), 2, FALSE)
Ví dụ: Bạn có tệp excel có sheet tên “Tây”. Bạn muốn tìm giá trị của cột A từ hàng 2 đến 5 trên sheet Tây và trả về giá trị từ cột A. Trong bảng dữ liệu dưới đây, A2 là “Cam” trên sheet “Tây”. Công thức sẽ tìm giá trị “Cam” trong cột A, từ hàng 2 đến 5 trên sheet “Tây”. Sau đó, nó sẽ trả về giá trị của cột B trong phạm vi tìm kiếm, là “100”
Áp dụng hàm VLOOKUP giữa 2 sheet từ kết quả của COUNTIF và INDIRECT
Công thức được thể hiện như sau:
VLOOKUP($A2, INDIRECT('''&INDEX(Danh_sách_tìm_kiếm, MATCH(1, {0;0;0;1}, 0)) &''!$A$2:$C$6'), 2, FALSE)
Ví dụ: Bạn có danh sách các sheet với tên là '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 trong 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' trong cột A từ hàng 2 đến 6 trên Sheet4. Sau đó, trả về giá trị trong cột B là 'Value1'.
Chú ý khi 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, cần lưu ý một số điều quan trọng như sau:
- Khi đóng file chứa table_array trong quá trình nhập công thức, có thể gây lỗi #REF! khi Excel không thể kết nối được với file.
- Thay đổi tên, di chuyển hoặc xóa file chứa table_array sau khi nhập công thức cũng có thể gây lỗi #REF! khi Excel không thể tìm thấy file đó.
- Thay đổi tên sheet hoặc cấu trúc của table_array sau khi nhập công thức cũng có thể gây lỗi #REF! khi Excel không thể tìm thấy table_array.
Trên đây là một số lưu ý quan trọng khi áp dụng hàm VLOOKUP giữa 2 sheet, 2 file khác nhau trong Excel. Chúc bạn thành công và tiếp tục học hỏi! Nếu bạn muốn tìm hiểu thêm về hàm VLOOKUP, hãy để lại bình luận dưới bài viết!
- Xem thêm: Các bài viết về Tin học văn phòng
