CÁCH DÙNG HÀM VLOOKUP GIỮA 2 SHEET TRONG EXCEL

Bài viết này, Học Excel Online sẽ giới thiệu bạn cách sử dụng hàm VLOOKUP giữa 2 sheet trong excel để sao chép dữ liệu từ một trang tính hoặc cửa sổ làm việc khác, VLOOKUP trong nhiều trang tính và tra cứu một cách linh động để trả về các giá trị từ các trang tính khác nhau.

Khi tra cứu một số trang thông tin trong Excel, rất hiếm khi tất cả dữ liệu nằm trên cùng một trang tính. Bạn sẽ phải tìm kiếm trên nhiều trang tính hoặc nhiều cửa sổ làm việc khác nhau. Tin tốt là Microsoft Excel cung cấp nhiều cách để thực hiện việc này và tin xấu là tất cả các cách đều phức tạp so với công thức VLOOKUP. Nhưng bạn chỉ cần một chút kiên nhẫn, chúng ta sẽ làm được.

Cách tìm kiếm dữ liệu bằng hàm VLOOKUP giữa 2 Sheet

Đối với người mới bắt đầu, hãy nghiên cứu trường hợp đơn giản nhất –  cách dùng hàm VLOOKUP giữa 2 sheet để sao chép dữ liệu. Nó rất giống với công thức VLOOKUP thông thường khi tìm kiếm trên cùng một trang tính. Sự khác biệt là bao gồm tên trang tính trong tham số table_array để cho công thức của bạn biết phạm vi tra cứu nằm trong trang nào.

Công thức chung cho hàm VLOOKUP từ một trang tính khác như sau:

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

Ví dụ: Chúng ta hãy kéo số liệu bán hàng từ báo cáo tháng 1 (Jan) sang trang Tóm tắt (Summary). Đối với điều này, chúng ta xác định các tham số sau:

• Lookup_values nằm trong cột A trên trang Tóm tắt và chúng ta tham chiếu đến ô dữ liệu đầu tiên là A2.
• Table_array là phạm vi A2:B6 trên trang tính Jan. Để tham chiếu đến nó, hãy đặt tiền tố cho tham chiếu phạm vi với tên trang tính, theo sau là dấu chấm than: Jan!$A$2:$B$6.

Xin lưu ý rằng chúng ta khóa phạm vi bằng tham chiếu ô tuyệt đối để ngăn nó thay đổi khi sao chép công thức sang các ô khác.

• Col_index_num là 2 vì chúng ta muốn sao chép một giá trị từ cột B, là cột thứ 2 trong trường Table array.
• Range_lookup được đặt thành FALSE để tìm kiếm kết quả khớp chính xác.

Đặt các đối số lại với nhau, chúng ta nhận được công thức này: 

=VLOOKUP(A2, Jan!$A$2:$B$6, 2, FALSE)

Kéo công thức xuống cột và bạn sẽ nhận được kết quả sau:

Theo cách tương tự, bạn cũng có thể dùng hàm VLOOKUP dữ liệu từ trang tính Feb và Mar:

=VLOOKUP(A2, Feb!$A$2:$B$6, 2, FALSE)

=VLOOKUP(A2, Mar!$A$2:$B$6, 2, FALSE)

Mẹo và lưu ý:

• Nếu trang tính chứa khoảng trắng hoặc các ký tự không phải chữ cái, tên trang tính phải được đặt trong dấu ngoặc kép, chẳng hạn như ‘Jan Sales’!$A$2:$B$6. Để biết thêm thông tin, vui lòng xem cách tham chiếu trang tính khác trong Excel.
• Thay vì nhập trực tiếp trên trang tính vào công thức, bạn có thể chuyển sang trang tính tra cứu và chọn phạm vi ở đó. Excel sẽ tự động chèn một tham chiếu với cú pháp chính xác, giúp bạn không khó khăn khi kiểm tra tên và khắc phục sự cố.

Hàm VLOOKUP từ một cửa sổ làm việc khác

Để hàm VLOOKUP giữa hai cửa sổ làm việc, bao gồm tên tệp trong dấu ngoặc vuông, theo sau là tên trang tính và dấu chấm than.

Ví dụ: để tìm kiếm giá trị A2 trong phạm vi A2:B6 trên trang tính Jan trong cửa sổ làm việc Sales_reports .xlsx, hãy sử dụng công thức này:

 =VLOOKUP(A2, [Sales_reports.xlsx]Jan!$A$2:$B$6, 2, FALSE)

Để biết chi tiết đầy đủ, vui lòng xem VLOOKUP từ một cửa sổ làm việc khác trong Excel.

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

Khi bạn cần tra cứu giữa nhiều hơn hai trang tính, giải pháp đơn giản nhất là sử dụng hàm VLOOKUP kết hợp với IFERROR. Ý tưởng là lồng một số hàm IFERROR để kiểm tra từng trang tính: nếu hàm VLOOKUP đầu tiên không tìm thấy kết quả phù hợp trên trang tính đầu tiên, hãy tìm kiếm trong trang tính tiếp theo.

IFERROR(VLOOKUP(…), IFERROR(VLOOKUP(…), …, “Not found”))

Để xem phương pháp này hoạt động như thế nào, chúng ta hãy xem xét ví dụ sau. Dưới đây là bảng Summary mà được yêu cầu điền tên và số số lượng hàng hóa trong trang tính WEST và EAST:

Đầu tiên, chúng ta sẽ kéo các mục . Đối với điều này, chúng ta dùng hàm VLOOKUP để tìm kiếm số lượng đơn hàng tròn A2 trên trang tính EAST và trả về giá trị từ cột B (cột thứ 2 trong table_array A2:C6). Nếu không tìm thấy kết quả phù hợp, hãy tìm kiems trang tính WEST. Nếu cả hai hàm VLOOKUP không thành công, hãy trả về giá trị “Not found”.

=IFERROR(VLOOKUP(A2, East!$A$2:$C$6, 2, FALSE), IFERROR(VLOOKUP(A2, West!$A$2:$C$6, 2, FALSE), “Not found”))

Để trả lại số lượng, chỉ cần thay đổi địa chỉ cột thành 3:

=IFERROR(VLOOKUP(A2, East!$A$2:$C$6, 3, FALSE), IFERROR(VLOOKUP(A2, West!$A$2:$C$6, 3, FALSE), “Not found”))

Mẹo: Nếu cần, bạn có thể chỉ định các bảng khác nhau trong một trang tính cho các hàm VLOOKUP khác nhau. Trong ví dụ này, cả hai trang tính tra cứu đều có cùng số hàng (A2:C6), những trang tính của bạn có thể có kích thước khác nhau.

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

Để hàm VLOOKUP giữa 2 sheet hay nhiều cửa sổ làm việc, hãy đặt tên cửa sổ làm việc trong dấu ngoặc vuông và đặt nó trước tên trang tính. Ví dụ: đây là cách bạn có thể dùng hàm VLOOKUP trong hai tệp khác nhau (Book1 và Book2) với một công thức duy nhất:

=IFERROR(VLOOKUP(A2, [Book1.xlsx]East!$A$2:$C$6, 2, FALSE),  IFERROR(VLOOKUP(A2, [Book2.xlsx]West!$A$2:$C$6, 2, FALSE),”Not found”))

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

Trong trường hợp bạn cần trả lại dữ liệu từ một số cột, việc tạo  col_index_num có thể giúp bạn tiết kiệm thời gian. Có một số điều chỉnh cần thực hiện:

• Đối với tham số col_index_num, hãy sử dụng hàm COLUMNS trả về số cột trong một mảng được chỉ định: COLUMNS($A$1:B$1). (Vị trí hàng không thực sự quan trọng, nó có thể là bất kỳ hàng nào.)
• Trong tham số lookup_value, hãy cố định tham chiếu cột bằng dấu $ ($A2), vì vậy nó vẫn cố định khi sao chép công thức sang các cột khác.

Kết quả là bạn nhận được một loại công thức mà sao chép các giá trị phù hợp từ các cột khác nhau, tùy thuộc vào cột mà công thức được sao chép vào:

=IFERROR(VLOOKUP($A2, East!$A$2:$C$6, COLUMNS($A$1:B$1), FALSE), IFERROR(VLOOKUP($A2, West!$A$2:$C$6, COLUMNS($A$1:B$1), FALSE), “Not found”))

Khi nhập hàm vào cột B, COLUMNS($A$1:B$1) cho kết quả là 2 yêu cầu VLOOKUP trả về mổ giá trị từ cột thứ 2 trong bảng.

Khi sao chép vào cột C (tức là bạn kéo công thức từ B2 sang C2), B$1 sẽ thay đổi thành C$1 vì tham chiếu cột là tương đối. Do đó, COLUMNS($A$1:C$1) đánh giá là 3 VLOOKUP trả về giá trị từ cột thứ 3.

Công thức này hiệu quả với 2 – 3 trang tra cứu. Nếu bạn có nhiều hơn, hàm IFERROR lặp lại sẽ trở nên quá cồng kềnh. Ví dụ tiếp theo cho thấy cách tiếp cận phức tạp hơn một chút nhưng rất hiệu quả.

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

Một cách khác để hàm VLOOKUP giữa nhiều trang tính trong Excel là sử dụng kết hợp các hàm VLOOKUP và INDIRECt. Phương pháp này yêu cầu một chút chuẩn bị, nhưng cuối cùng, bạn sẽ có một công thức rút gọn hơn để VLOOKUP bất kỳ bảng tính nào.

Công thức chung cho hàm VLOOKUP giữa 2 sheet 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 – tên phạm vi bao gồm 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 tra cứu.
• Col_index_num – số cột trong bảng mà từ đó trả về một giá trị.

Để công thức hoạt động chính xác, hãy ghi nhớ những lưu ý sau:

Công thức này để hoạt động được phải nhấn tổ hợp phím Ctrl + Shift + Enter. Tất cả các trang tính phải có cùng thứ tự các cột.

Vì chúng ta sử dụng một mảng bảng cho tất cả các trang tính tra cứu, hãy chỉ định phạm vi lớn nhất nếu các trang tính của bạn có số hàng khác nhau.

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

Để VLOOKUP nhiều trang tính cùng một lúc, hãy thực hiện các bước sau:

1. VIết ra tất cả các tên bảng tra cứu ở đó trong cửa sổ làm việc của bạn và đặt tên cho phạm vi đó (Lookup_sheets trong trường hợp này).

       2. Đồng bộ dữ liệu của bạn. Trong ví dụ này, chúng ta sẽ:

• Tìm kiếm giá trị A2 (lookup_value) 
• Trong phạm vi A2:A6 (lookup_range) trong bốn trang tính (East, North, South và West)
• Kéo các giá trị từ cột B, là cột 2 (сol_index_num) trong phạm vi dữ liệu A2:C6 (table_array). 

Với các tham số trên, công thức có dạng như sau:

=VLOOKUP($A2, INDIRECT(“‘”&INDEX(Lookup_sheets, MATCH(1, –(COUNTIF(INDIRECT(“‘”& Lookup_sheets&”‘!$A$2:$A$6″), $A2)>0), 0)) &”‘!$A$2:$C$6”), 2, FALSE)

Xin lưu ý rằng chúng ta cố định cả hai phạm vi ($A$2:$A$6 and $A$2:$C$6) với tham chiếu ô tuyệt đối.

      3. Nhập công thức vào ô trên cùng (B2 trong ví dụ này) và nhấn Ctrl + Shift + Enter để hoàn thành.

      4. Nhấn đúp chuột hoặc kéo fill handle (một nốt màu đen ở góc phải – bên dưới của ô được chọn) để sao chép công thức xuống cột. 

Kết quả là chúng ta có công thức tra cứu số lượng trong 4 bảng tính và lấy ra mặt hàng tương ứng. Nếu không tìm thấy số lượng đơn hàng cụ thể, lỗi #N/A sẽ hiển thị như trong hàng 14:

Để trả về số lượng, chỉ cần thay thế 2 bằng 3 trong tham số col_index_num vì số lượng nằm trong cột thứ 3 của băng:

=VLOOKUP($A2, INDIRECT(“‘”&INDEX(Lookup_sheets, MATCH(1, –(COUNTIF(INDIRECT(“‘” & Lookup_sheets & “‘!$A$2:$A$6”), $A2)>0), 0)) & “‘!$A$2:$C$6”), 3, FALSE)

Nếu bạn muốn thay thế ký hiệu lỗi #N/A bằng văn bản, hãy đưa công thức IFNA: 

=IFNA(VLOOKUP($A2, INDIRECT(“‘”&INDEX(Lookup_sheets, MATCH(1, –(COUNTIF(INDIRECT(“‘” & Lookup_sheets & “‘!$A$2:$A$6”), $A2)>0), 0)) & “‘!$A$2:$C$6”), 3, FALSE), “Not found”)

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 nhiều trang tính trong một cửa sổ làm việc khác. Điều này cho thấy, hãy chèn tên cửa sổ làm việc bên trong INDIRECT như được hiển thị trong công thức dưới đây:

=IFNA(VLOOKUP($A2, INDIRECT(“‘[Book1.xlsx]” & INDEX(Lookup_sheets, MATCH(1, –(COUNTIF(INDIRECT(“‘[Book1.xlsx]” & Lookup_sheets & “‘!$A$2:$A$6”), $A2)>0), 0)) & “‘!$A$2:$C$6”), 2, FALSE), “Not found”)

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

Nếu bạn muốn thấy nhiều dữ liệu từ một số cột, công thức mảng nhiều ô có thể thực hiện điều đó trong một lần. Để tạo một công thức như vậy, hãy cung cấp một hằng số mảng cho tham số col_index_num.

Trong ví dụ này, chúng tôi muốn trả về tên mục (cột B) và số lượng (cột C), lần lượt là cột thứ 2 và thứ 3 trong bảng. Vì vậy, mảng bắt buộc là {2,3}.

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

Để nhập chính xác công thức vào nhiều ô, đây là những gì bạn cần làm:

• Trong hàng đầu tiên, hãy chọn tất cả các ô sẽ được điền (B2:C2 trong ví dụ này).
• Nhập công thức và nhấn Ctrl + Shift + Enter. Thao tác này nhập cùng một công thức vào các ô đã chọn, sẽ trả về một giá trị khác nhau trong mỗi cột.
• Kéo công thức xuống các hàng còn lại.

Công thức này hoạt động như thế nào

Để hiểu rõ hơn, hãy chia nhỏ công thức thành nhiều hàm riêng lẻ:

=VLOOKUP($A2, INDIRECT(“‘”&INDEX(Lookup_sheets, MATCH(1, –(COUNTIF(INDIRECT(“‘”& Lookup_sheets&”‘!$A$2:$A$6″), $A2)>0), 0)) &”‘!$A$2:$C$6”), 2, FALSE)

Làm việc từ trong ra ngoài, đây là những gì công thức thực hiện.

COUNTIF và INDIRECT

Tóm lại, INDIRECT xây dựng các tham chiếu cho tất cả các trang tính cần tra cứu và COUNTIF đếm số lần xuất hiện của giá trị tra cứu (A2) trong mỗi trang tính:

–(COUNTIF( INDIRECT(“‘”&Lookup_sheets&”‘!$A$2:$A$6”), $A2)>0)

Chi tiết hơn

Đầu tiên, bạn kết hợp tên (Lookup_sheets) và tham chiếu ô ($A$2:$A$6), 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 và cung cấp chuỗi văn bản kết quả cho hàm INDIRECT để tham chiếu động đến các trang tính cần tra cứu:

INDIRECT({“‘East’!$A$2:$A$6”; “‘South’!$A$2:$A$6”; “‘North’!$A$2:$A$6”; “‘West’!$A$2:$A$6”})

COUNTIF kiểm tra từng ô trong phạm vi A2:A6 trên mỗi trang tính tra cứu so với giá trị trong A2 trên trang tính chính và trả về lượng kết quả phù hợp cho mỗi trang tính. Trong tập dữ liệu, số thứ tự trong ô A2 (101) được tìm thấy trong trang tính WEST, là thứ 4 trong phạm vi được đặt tên, vì vậy COUNTIF trả về mảng này:

{0;0;0;1}

Tiếp theo, bạn so sánh từng phần tử của mảng với 0: 

Điều này tạo một mảng các giá trị TRUE (lớn hơn 0) và FALSE (bằng 0), mà bạn gán các giá trị 1 và 0 bằng cách sử dụng một số (-_) kép và kết quả là:

{0; 0; 0; 1}

Thao tác này là cảnh báo trước các tình huống xấu khi tra cứu trang tính bao gồm một số sự cố tra cứu dữ liệu, trong trường hợp đó COUNTIF sẽ trả về số lượng lớn hơn 1, trong khi chúng ta chỉ yêu cầu số 1 và 0 trong mảng cuối cùng.

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

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

INDEX và MATCH

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

INDEX(Lookup_sheets, MATCH(1, {0;0;0;1}, 0))

Hàm MATCH có hình dạng như khớp chính xác (exact match) (0 trong tham số cuối cù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:

INDEX(Lookup_sheets, 4)

Hàm INDEX sử dụng số được trả về bởi hàm MATCH như tham số hàng (row_num) và trả về giá trị 4 trong phạm vi được đặt tên là lookup_sheets là WEST.

Vì vậy, công thức tiếp tục tối giản thành:

 VLOOKUP($A2, INDIRECT(“‘”&”West”&”‘!$A$2:$C$6”), 2, FALSE)

VLOOKUP và INDIRECT 

Hàm INDIRECT xử lý chuỗi văn bản bên trong nó:

INDIRECT(“‘”&”West”&”‘!$A$2:$C$6”)

Và chuyển đổi thành một tham chiếu đi đến tham số table_array của hàm VLOOKUP:

VLOOKUP($A2, ‘West’!$A$2:$C$6, 2, FALSE)

Cuối cùng, công thức VLOOKUP tìm kiếm giá trị A2 trong cột đầu tiên của phạm vi A2:C6 trên trang tính WEST và trả về kết quả khớp từ cột thứ 2.

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

Trước hết, hãy xác định chính xác từ “động” (“dynamic”) có nghĩa là gì trong ngữ cảnh này và công thức trước như thế nào.

Trong trường hợp bạn có lượng lớn dữ liệu ở cùng một định dạng được chia thành nhiều bảng tính, bạn muốn trích xuất thông tin từ các trang tính khác nhau vào các ô khác nhau. Hình ảnh dưới đây sẽ minh họa khái niệm này:

Không giống như các công thức trước, lấy một giá trị từ một trang tính cụ thể dựa trên một số nhận biết duy nhất, 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Ó hai giải pháp khác nhau cho phần này. Trong cả hai trường hợp, bạn cần thực hiện một số công việc như chuẩn bị và tạo tên phạm vi cho ô dữ liệu trong mỗi bảng tra cứu. Đối với ví dụ này, chúng ta xác định các phạm vi sau:

• East_Sales – A2: B6 trên trang tính East
• North_Sales – A2: B6 trên trang tính North
• South_Sales – A2: B6 trên trang tính South
• West_Sales – A2: B6 trên trang tính West

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

Nếu bạn có số lượng trang tính hợp lý để tra cứu, bạn có thể chèn hàm IF vào công thức để chọn trang tính dựa trên từ khóa trong các ô được xác định trước (trong trường hợp này là các ô từ B1 đến D1).

Với giá trị tra cứu trong A2, công thức 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)

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; nếu B1 là South, hãy tìm trong phạm vi có tên South_Sales và nếu B1 là West, hãy tìm trong phạm vi có tên West_Sales.

Phạm vi được IF trả về sẽ 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 Ì (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ì vậy, chúng ta nhập công thức vào B2, sao chép nó sang phải và kéo xuống nhiều cột và hàng khi cần thiết và nhận được những kết quả sau:

INDIRECT VLOOKUP

Khi làm việc với nhiều trang tính, nhiều cấp độ lồng nhau có thể làm cho công thức quá dài và khó đọc. 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

=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)

Ở đây, chúng ta nối tham chiếu với ô chứa tên phạm vi duy nhất (B1) và phần chung (_Sales). Điều này tạo ra một chuỗi văn bản như “East_Sales”, mà INDIRECT chuyển thành tên phạm vi mà Excel có thể hiểu được.

Kết quả là bạn sẽ có được một công thức đơn giản có thể hoạt động được trên bất kỳ số lượng trang tính nào:

Bài viết trên chúng tôi đã hướng dẫn thêm một cách dùng hàm vlookup. Cảm ơn bạn đã đọc và hãy theo dõi Học Excel Online để có thể bổ sung thêm nhiều kiến thức, thành tạo excel trong những blog tiếp theo nhé!