Hướng dẫn, thủ thuật về Máy tính - Laptop - Tablet

VLOOKUP là một hàm cơ bản trong Excel trên máy tính, được dùng để tìm kiếm thông tin, dữ liệu theo phạm vi cột hoặc dòng trong bảng. Sau đây, hãy cùng Thế Giới Di Động tìm hiểu về cách dùng hàm VLOOKUP giữa 2 sheet, 2 file khác nhau trong Excel nhé!

1. Tìm kiếm dữ liệu bằng hàm VLOOKUP giữa 2 Sheet

Cũng giống như hàm VLOOKUP thông thường, cho phép tìm kiếm và sao chép dữ liệu trên cùng một trang tính. Tuy nhiên, điểm khác biệt ở đây là tham số table_array còn có thêm tên trang tính để cho công thức của bạn biết phạm vi tra cứu nằm trong trang nào.

Sau đây là ví dụ cụ thể:

Cho 2 sheet khác nhau với: Sheet 1 (nhanvien) là danh sách nhân viên đang cần nhập số tiền thưởng , sheet 2 (thuong) là điều kiện xếp loại ứng với tiền thưởng.

2 sheet đã được tạo

Đầu tiên, thiết lập công thức hàm VLOOKUP bằng cách chọn 1 ô cho 1 nhân viên cần tính tiền thưởng. Tại đây chúng ta chọn ô D3 và nhập công thức:

=VLOOLUP(C3,thuong!$B$4:$C$6,2,FALSE)

Với:

Nhập công thức như hình

Sau khi nhập xong, kéo công thức từ cột D3 xuống D7 ta được kết quá như sau:

Và đây là kết quả

2. Tìm kiếm dữ liệu bằng hàm VLOOKUP giữa 2 File

Tìm kiếm dữ liệu bằng hàm VLOOKUP giữa 2 File chỉ có một khác biệt nhỏ so với tìm kiếm dữ liệu bằng hàm VLOOKUP giữa 2 sheet. Hãy cùng theo dõi ví dụ sau để hiểu rõ hơn nhé:

Ta tạo 2 file khác nhau, với file 1 (hocsinh) là danh sách học sinh cần điền xếp loại và file 2 (xeploai) là điều kiện để xếp loại tương ứng với số điểm.

Cách dùng hàm VLOOKUP giữa 2 File (file 1)

Cách dùng hàm VLOOKUP giữa 2 File (file 2)

Để tìm kiếm dữ liệu bằng hàm VLOOKUP giữa 2 file, ta làm theo các bước sau:

- Bước 1: Tại ô D2, ta nhập công thức tương tự như 2 sheet trên.

Thực hiện bước 1

- Bước 2: Chuyển sang file 2 (đã được tạo từ trước với tên là dulieu.xlsx), chọn vùng dữ liệu A4:B7 bằng cách nhấn phím Ctrl kéo từ ô A4 đến B7.

Thực hiện bước 2

- Bước 3: Quay trở lại file 1 và nhập thêm "2, TRUE" vào công thức thì ta được kết quả sau:

Thực hiện bước 3

Kéo công thức từ cột D2 đến D6 tương tự như 2 sheet là bạn đã hoàn thành tìm kiếm dữ liệu bằng 2 hàm VLOOKUP giữa 2 File.

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

Để có thể tra cứu dữ liệu từ 2 trang tính trở lên, người dùng có thể dùng hàm VLOOKUP hết hợp với hàm IFERROR với ý tưởng là lồng hàm IFERROR để kiểm tra từng trang tính.

Hãy cùng theo dõi ví dụ sau đây để tìm hiểu rõ hơn về cách hoạt động của phương pháp này:

Ta có 3 sheet: Sheet 1 (slsp) dùng để điền số lượng của một sản phẩm, sheet 2 (sanpham1) chứa một phần sản phẩm, sheet 3 (sanpham2) cũng tương tự sheet 2.

3 sheet của ví dụ trên

Để thực hiện phương pháp này, ta thực hiện các bước sau:

- Bước 1: Chọn 1 ô cho 1 sản phẩm 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 chúng ta chọn ô C2 và nhập công thức:

Nhập công thức tính vào trong ô

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

Minh họa bước 2

- Bước 3: Thực hiện lại bước 1, sau đó chuyển qua sheet3 (sanpham2) và thực hiện tương tự bước 2. Lưu ý, ở bước này ta nên nhập thêm "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.

Sau khi đã thực hiện xong bước 3

- Bước 4: Kéo công thức từ dòng C2 xuống C10 để hoàn tất tìm kiếm dữ liệu.

Thực hiện bước 4

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

Cách dùng hàm VLOOKUP trong nhiều cửa sổ làm việc cũng tương tự như tìm kiếm dữ liệu bằng hàm VLOOKUP giữa 2 File. Bạn chỉ cần lồng hàm VLOOKUP vào trong hàm IFERROR, đặ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. Ví dụ như công thức sau:

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

Để tạo công thức VLOOKUP với nhiều cột bằng cách sử dụng hàm IFERROR và hàm COLUMN, bạn có thể sử dụng công thức sau đây:

Trong đó:

Hàm COLUMN(column_index_range) trả về chỉ mục của cột trong phạm vi column_index_range. Bằng cách sử dụng hàm này, bạn có thể tạo ra một công thức VLOOKUP linh hoạt để trả lại kết quả từ nhiều cột.

Nếu công thức VLOOKUP không tìm thấy giá trị, hàm IFERROR sẽ trả về một giá trị trống (hoặc bạn có thể thay thế bằng một giá trị khác tuỳ ý).

Hãy thay đổi lookup_value, table_array và column_index_range theo nhu cầu của bạn để áp dụng công thức cho trường hợp cụ thể.

Cùng theo dõi ví dụ sau để rõ hơn:

Ta có một bảng như sau trong một bảng tính Excel:

Ví dụ về làm cho địa chỉ cột thành VLOOKUP nhiều cột

Bây giờ, giả sử bạn muốn tìm tên của một loại trái cây dựa trên ID của nó (ví dụ: 303). Bạn có thể sử dụng công thức IFERROR và VLOOKUP để làm điều này.

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 sau trong ô khác (giả sử ô F2) để lấy tên trái cây:

Công thức này sẽ thực hiện các thao tác sau đây:

VLOOKUP(A4, $A$1:$B$5, 2, FALSE): Nó 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 (ví dụ: giá trị lookup_value không có trong table_array), nó sẽ trả về một chuỗi trống ("").

Vì vậy, trong ví dụ này, 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.

Trường hợp đúng

Nếu lookup_value là 999 (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ả.

Trường hợp bị lỗi

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

Kết hợp hàm VLOOKUP và hàm INDIRECT cũng là một cách giúp hàm VLOOKUP giữa nhiều trang tính trong Excel. Công thức như sau:

Trong đó:

Lưu ý:

Ví dụ:

Giả sử bạn có một danh sách các sheet trong workbook có tên "Sheet1", "Sheet2", "Sheet3" và "Sheet4". Bạn muốn 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.

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

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"). Nếu không tìm thấy, nó sẽ trả về giá trị 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.

Để sử dụng công thức VLOOKUP trên nhiều trang tính cùng một lúc, bạn có thể thực hiện các bước 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 bạn và đặt tên cho phạm vi đó. Ví dụ, bạn có thể tạo một phạm vi tên là "Lookup_sheet" và liệt kê tên các trang tính vào đó.

Tạo phạm vi bằng tổ hợp phím Ctrl + F3

- 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:

• Tìm kiếm giá trị trong ô A2 (lookup_value).
• Trong phạm vi từ A2 đến 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).

Lưu ý rằng trong công thức trên, cả 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 ô đầu tiên (ví dụ: ô C2) và 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: Nhấp đúp chuột vào ô đầu tiên (ví dụ: ô 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) để sao chép công thức xuống cột. Điều 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ả là bạn sẽ có công thức VLOOKUP để tra cứu giá trị trên nhiều trang tính và trả về kết quả tương ứng. Nếu không tìm thấy giá trị cụ thể, công thức sẽ trả về lỗi #N/A.

Kết quả hiện ra những vẫn còn lỗi #N/A

Bạn có thể khắc phục lỗi #N/A bằng cách đưa hàm IFNA vào công thức:

Ta được kết quả sau:

Khắc phục lỗi #N/A bằng cách chèn hàm IFNA

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

Công thức chung này (hoặc bất kỳ biến thể nào của nó) cũng có thể được sử dụng để VLOOKUP trên nhiều trang tính trong một cửa sổ làm việc khác. Điều này có nghĩa là bạn có thể chèn tên cửa sổ làm việc vào công thức, như được hiển thị trong công thức dưới đây:

Trong công thức trên, bạn cần thay thế [Tên_tệp.xlsx] bằng tên của tệp Excel mà bạn muốn tra cứu trên nó. Ví dụ, nếu tên tệp là "Book1.xlsx", bạn sẽ thay thế [Tên_tệp.xlsx] thành "Book1.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".

Với công thức trên, bạn có thể áp dụng VLOOKUP trên nhiều trang tính trong một 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".

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

Hàm VLOOKUP giữa 2 sheet và trả về nhiều cột

Để có thể dùng hàm VLOOKUP giữa 2 sheet và trả về nhiều cột, ta dùng công thức sau:

Trong đó:

Với công thức này, bạn có thể tra cứu giá trị từ các cột khác nhau trong phạm vi dữ liệu trên nhiều trang tính và trả về nhiều cột tương ứng.

Hàm VLOOKUP giữa 2 sheet và trả về nhiều cột

COUNTIF và INDIRECT

Hàm INDIRECT thực hiện xây dựng các tham chiếu cho tất cả các sheet cần tra cứu và hàm COUNTIF có chức năng đếm tần suất xuất hiện của giá trị tra cứu (A2) trong mỗi sheet:

Nói rõ hơn là tạo một (Lookup_sheets) và tham chiếu ô ($A$2:$A$6), sau đó cung cấp cho hàm INDIRECT một chuỗi văn bản được thêm dấu nháy đơn và dấu chấm than vào đúng vị trí để tạo tham chiếu bên ngoài.

Hàm COUNTIF quét qua phạm vi A2:A6 để đếm tần suất xuất hiện của A2 trong trang tính và trả về kết quả phù hợp với mỗi trang tính. Từ dữ liệu, số thứ tự trong ô A2 (101) được tìm thấy trong trang tính WEST, tức là giá trị là 4 trong phạm vi được đặt tên, vì vậy COUNTIF trả về mảng này:

{0;0;0;1}

Sau tất cả các phép biến đổi, ta có công thức như sau:

Ví dụ:

Ta có một danh sách các sheet trong workbook có tên "Sheet1", "Sheet2", "Sheet3", và "Sheet4". Chúng ta muốn tìm kiếm giá trị trong cột A từ hàng 2 đến hàng 6 trên sheet thứ 4 (Sheet4), và trả về giá trị từ cột thứ 2 trong phạm vi tìm kiếm.

Giá trị trong ô A2 là "X", và trên sheet thứ 4 (Sheet4), chúng ta có dữ liệu như sau:

Ví dụ về COUNTIF và INDIRECT

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 sheet thứ 4 (Sheet4). Sau đó, nó sẽ trả về giá trị từ cột B trong phạm vi tìm kiếm là "Value1".

INDEX và MATCH

Tại thời điểm này, khi kết hợp 2 hàm MATCH và INDEX, ta được:

Hàm MATCH dùng để tìm kiếm giá trị 1 trong mảng {0;0;0;1} và trả về vị trí của nó là 4:

Lúc này, hàm INDEX lấy giá trị được trả về từ hàm MATCH và dùng như một tham số hàng và trả về giá trị 4 trong phạm vi được đặt tên là lookup_sheets là WEST.

Từ đó có thể suy ra được công thức tối giản của 2 hàm khi kết hợp lại với nhau:

Ta có ví dụ sau:

Giả sử bạn có một tệp Excel có một sheet có tên là "West" và bạn muốn tìm kiếm giá trị trong cột A từ hàng 2 đến hàng 5 trên sheet "West" và trả về giá trị từ cột thứ 2 trong phạm vi tìm kiếm đó.

Nếu giá trị trong ô A2 là "Cam", và trên sheet "West", chúng ta có dữ liệu như sau:

Ví dụ về INDEX và MATCH

Công thức sẽ tìm kiếm giá trị "Cam" trong cột A từ hàng 2 đến hàng 5 trên sheet "West". Sau đó, nó sẽ trả về giá trị từ cột B trong phạm vi tìm kiếm, tức là "100".

VLOOKUP và INDIRECT

Ta có ví dụ:

Tìm phí ship theo số lượng của từng đơn hàng bằng hàm VLOOKUP và hàm INDIRECT.

Giải thích:

Ví dụ về VLOOKUP và INDIRECT

5. Hàm VLOOKUP động trả về dữ liệu từ nhiều trang tính vào các ô khác nhau

Để hiểu rõ hơn về khải niệm "động" hay còn được gọi là "dynamic", chúng ta hãy cùng xem ảnh minh họa sau:

Hình minh họa hàm VLOOKUP động

Điểm khác biệt ở đây là lần này chúng ta đang tìm kiếm cách trích xuất các giá trị từ một số trang tính cùng một lúc.

Có 2 giải pháp khác nhau và trong cả 2 giải pháp, bạn đều phải chuẩn bị và tạo tên pham vị dữ liệu trong mỗi bảng tra cứu. Các phạm vi trong ví dụ này được xác định như sau:

- Vietnam_Sales - A2: B3 trên trang tính Vietnam.

- Lao_Sales - A2: B3 trên trang tính Lao.

- Campuchia_Sales - A2: B3 trên trang tính Campuchia.

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

Ta có công thức như sau:

Nếu B1 là EAST, hãy tìm trong phạm vi có tên East_Sales; nếu B1 là North, hãy tìm trong phạm vi có tên North_Sales và tương tự với South và West.

Hàm IF sẽ thực hiện và trả về một phạm vi và chuyển đến table_array của hàm VLOOKUP, dải này kéo giá trị khớp từ cột thứ 2 trên trang tính tương ứng.

Việc sử dụng thông minh các tham chiếu hỗn hợp (mixed reference) cho giá trị tra cứu ($A2 - cột tuyệt đối và hàng tương đối) và phép kiểm tra logic IF (B$1 - cột tương đối và hàng tuyệt đối) cho phép sao chép công thức sang các ô khác mà không có bất kỳ thay đổi nào - Excel điều chỉnh tham chiếu dựa trên vị trí tương đối của hàng và cột.

Ví dụ về kết hợp hàm VLOOKUP và hàm IF

INDIRECT VLOOKUP

Nếu làm việc với nhiều trang tính và bạn cảm thấy quá dài dòng và khó hiểu thì đây là một cách tốt hơn nhiều là tạo một hàm vlookup động với sự trợ giúp của INDIRECT:

Công thức trên dùng hàm VLOOKUP tìm kiếm giá trị trong một phạm vi và trả về giá trị liên quan từ cột khác trong phạm vi đó. Sau đó dùng hàm INDIRECT để chuyển đổi một chuỗi văn bản thành một tham chiếu trong công thức.

Ví dụ về INDIRECT VLOOKUP

SĂN NGAY MÃ GIẢM SỐC TGDĐ TẠI ĐÂY!!

Bài viết trên đã hướng dẫn cách dùng hàm VLOOKUP giữa 2 sheet, 2 file khác nhau trong Excel. Cảm ơn bạn đã theo dõi và hẹn gặp lại ở các bài viết sau.