Hướng dẫn cách sử dụng Vlookup Importrange trong Google Sheets [Ví dụ công thức]

GiGiKing·Tháng tư 1, 2024·0 comments

Với sự trợ giúp của hàm Vlookup, chúng ta có thể nhập các giá trị trùng khớp từ một Bảng tính sang bảng tính khác trong Google Sheets. Hãy để tôi chi tiết hướng dẫn cách sử dụng Vlookup Importrange trong Google Sheets.

Nếu bạn đã biết về cách sử dụng các hàm Vlookup và Importrange, thì việc sử dụng Vlookup+Importrange chỉ đơn giản như chơi đồ chơi của trẻ con.

Nhiều người dùng Google Spreadsheets nghi ngờ liệu họ có thể sử dụng Vlookup+Importrange hoặc Query+Importrange để nhập dữ liệu có điều kiện giữa hai tệp Google Sheets khác nhau.

Câu trả lời là bạn có thể sử dụng cả hai. Tuy nhiên, có một số khác biệt và tôi sẽ giải thích điều đó ở dưới đây.

Đây là một hướng dẫn nâng cao bao gồm không chỉ việc sử dụng Vlookup trong Importrange mà còn cách sử dụng Query trong Importrange trong Google Sheets.

Tôi biết bạn đã không mới với Vlookup hoặc Importrange, và đó là lý do tại sao bạn đang ở đây để tìm hiểu cách sử dụng Vlookup trong Importrange trong Google Sheets.

Tuy nhiên, nếu bạn nghĩ rằng đã đến lúc làm mới kỹ năng Vlookup/Importrange của mình, hãy xem hướng dẫn về các hàm Google Sheets của tôi.

Tôi sẽ giải thích những tips Vlookup và Importrange dưới đây trong bài viết này.

Cách sử dụng Vlookup với hàm Importrange trong Google Sheets?

Trong ví dụ này, tôi đang sử dụng hai tệp Google Sheets. Dù tên tệp không quan trọng, nhưng hãy ghi lại chúng để tham khảo.

Tệp 1 (Workbook 1): Sales.

Tệp 2 (Workbook 2): Analysis.

Chúng ta sẽ nhập dữ liệu từ tệp Google Sheets đầu tiên, tức là “Sales”, vào tệp Google Sheets thứ hai, tức là “Analysis”.

Điều này có nghĩa là tôi sẽ sử dụng các công thức kết hợp Vlookup+Importrange trong tệp “Analysis”.

Đây là dữ liệu mẫu trong tệp “Sales”. Phạm vi dữ liệu là A1:F và tên trang (tên bảng tính) là “Sheet1”.

Screenshot

Các ví dụ công thức cho việc sử dụng Vlookup với hàm Importrange trong Google Sheets

Tôi đang ở trang “Analysis”.

Hãy cho tôi chỉ cho bạn từ bên trong trang tính này cách tìm kiếm Sheet1!A1:F trong tệp “Sales” và trả về các giá trị.

Ví dụ cơ bản về cách sử dụng Vlookup Importrange trong Google Sheets

Trong tệp “Analysis”, tôi có khóa tìm kiếm (tên đầu tiên) “Justin” trong ô A2.

Tôi muốn Vlookup tên này trong A2:A trong Sheet1 trong tệp “Sale” và trả về giá trị tương ứng từ cột F.

Các bước:

  • Đầu tiên, mở tệp “Sales” và sao chép URL của Bảng tính từ thanh địa chỉ. Bạn phải đảm bảo rằng tab hoạt động là “Sheet1”, chứa dữ liệu cần tìm kiếm.

  • Đi đến tệp “Analysis” và chèn công thức Importrange sau vào ô B1.

=IMPORTRANGE("URL","Sheet1!A1:F")

  • Thay thế văn bản “URL” bằng URL Bảng tính đã sao chép.

  • Nếu nó trả về lỗi #REF, di chuột qua và “Cho phép truy cập”. Điều này giúp nhập dữ liệu từ một bảng tính nguồn (Sales) vào một bảng tính đích (Analysis).

  • Bọc nó bằng hàm Vlookup như sau.

=VLOOKUP(A2,IMPORTRANGE("URL","Sheet1!A1:F"),6,FALSE)
  • Công thức Vlookup Importrange này sẽ trả về giá trị 272160.00 từ cột trả về được chỉ định trong Vlookup, tức là cột thứ 6.

Sự phân tích công thức Vlookup:

  • search_key: A2

  • Tham chiếu ô A2 trong “Analysis” (tệp đích) chứa khóa tìm kiếm, đó là tên “Justin”.

  • range: IMPORTRANGE(“URL”,”Sheet1!A1:F”)

  • Phạm vi tìm kiếm, tham số thứ hai trong công thức, nằm trong một tệp khác, tức là “Sales”.

  • index: 6

  • Chỉ số ở đây là số cột thứ 6 (cột F), từ đó Vlookup trả về giá trị.

  • is_sorted: FALSE

  • Nó đảm bảo khớp chính xác của khóa tìm kiếm trong cột đầu tiên của bảng.

Vlookup Importrange với nhiều khóa tìm kiếm trong cùng một cột

Lưu ý: Tôi khuyến nghị bạn kiểm tra hướng dẫn này, Vlookup Array use, trước khi tiếp tục – Cách sử dụng Vlookup để trả về Kết quả Mảng trong Google Sheets.

Vlookup+Importrange hỗ trợ hầu hết các biến thể Vlookup tiên tiến.

Nhưng công thức có thể phức tạp do thay thế phạm vi Vlookup bằng Importrange.

Hãy sử dụng Vlookup trong Importrange với nhiều giá trị tìm kiếm.

Trong tệp “Analysis”, tôi có các khóa tìm kiếm sau trong phạm vi A2:A4.

Justin

Andrew

Jonathan

Dưới đây là công thức kết hợp Vlookup Importrange trong Google Sheets với nhiều khóa tìm kiếm trong cùng một cột.

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A,IMPORTRANGE("URL","Sheet1!A1:F"),6,0)))

Bạn có thể chỉ ra sự khác biệt giữa công thức này và công thức trước không?

Vlookup này là một Công thức Mảng. Điều đó có nghĩa là chúng ta mong đợi kết quả mảng từ Vlookup.

Vì Vlookup không phải là Công thức Mảng, nên đã là một công thức mảng bằng cách bọc nó bằng hàm ArrayFormula.

Hơn nữa, vì các khóa tìm kiếm nằm trong A2:A4, nên sử dụng A2: A (phạm vi vô hạn để bao gồm các khóa tìm kiếm trong tương lai).

Bao gồm IFERROR để tránh lỗi #N/A.

Nhiều Điều kiện/Tiêu chí trong Các Cột khác nhau trong Công thức Vlookup+Importrange

Công thức này có thể gây nhầm lẫn cho nhiều người dùng so với hai công thức trước.

Vì vậy, tôi rất khuyến nghị bạn tham khảo hướng dẫn này – Cách sử dụng VLOOKUP với Nhiều Tiêu chí trong Google Sheets. Ở đây, trong hướng dẫn này, chỉ có một sự khác nhau nhỏ.

Trong việc sử dụng thông thường, Vlookup tìm kiếm xuống cột đầu tiên trong một phạm vi.

Trong Vlookup với nhiều tiêu chí, công thức tìm kiếm xuống hai cột (bạn có thể bao gồm nhiều cột hơn). Một lần nữa, chúng ta muốn làm điều đó trong một phạm vi đã nhập.

Hãy xem Cách Vlookup cột đầu tiên + một cột khác trong Vlookup Importrange.

Lần này, tôi muốn tìm kiếm các khóa tìm kiếm xuống các cột tìm kiếm A và B trong phạm vi đã nhập, có nghĩa là tên đầu tiên và tên cuối cùng.

Khóa tìm kiếm (A2:B4):

Justin | Jackson

Andrew | Edwards

Jonathan | Reed

Công thức:

=ARRAYFORMULA(VLOOKUP(A2:A&B2:B,{IMPORTRANGE("URL","Sheet1!A2:A")&IMPORTRANGE("URL","Sheet1!B2:B"),IMPORTRANGE("URL","Sheet1!C2:F")},5,FALSE))

Bạn phải thay thế tất cả ba URL trong công thức bằng URL “Sales”.

Logic trong công thức này như sau:

  1. Kết hợp các khóa tìm kiếm bằng cách sử dụng dấu và.

  2. Kết hợp các cột liên quan trong phạm vi Vlookup bằng dấu và.

  3. Trước đây, cột đầu ra là cột thứ 6 trong bảng tìm kiếm. Bây giờ chúng ta chỉ có năm cột vì đã kết hợp hai cột trong phạm vi. Vì vậy, sử dụng số chỉ mục cột 5 thay vì 6.

Còn không hiểu? Xem phạm vi kết hợp mới được trả về bởi các công thức Importrange nhiều và dấu &.

Cải thiện hiệu suất bằng LET

Một trong những vấn đề khi sử dụng nhiều importrange là nó ảnh hưởng đến hiệu suất. Nó có thể làm chậm tính toán.

Hàm LET mới đã giải quyết vấn đề này. Chúng ta có thể viết lại công thức trên như sau.

=ARRAYFORMULA(LET(sk,A2:A&B2:B,rg,IMPORTRANGE("URL","Sheet1!A2:F"),VLOOKUP(sk,{CHOOSECOLS(rg,1)&CHOOSECOLS(rg,2),CHOOSECOLS(rg,{3,4,5,6})},5,0)))

Cú pháp LET: LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression)

  • name1: sk

  • value_expression: A2:A&B2:B

  • name2: rg

  • value_expression2: IMPORTRANGE(“URL”,”Sheet1!A2:F”)

sk là tên khóa tìm kiếm của Vlookup, và rg là tên của công thức Importrange (phạm vi Vlookup).

  • formula_expression: VLOOKUP(sk,{CHOOSECOLS(rg,1)&CHOOSECOLS(rg,2),CHOOSECOLS(rg,{3,4,5,6})},5,0)

Tôi đã sử dụng Vlookup trong phần biểu thức công thức. CHOOSECOLS là các cột trích xuất từ phạm vi nhập. Sau đó, kết hợp hai cột đầu tiên tìm kiếm.

Trong ví dụ Vlookup Importrange trên, chúng ta chỉ sử dụng importrange một lần.

Còn thú vị hơn là chúng ta có thể sử dụng hàm Importrange với XLOOKUP để nhập giá trị.

Xlookup Importrange thay thế:

=ARRAYFORMULA(LET(sk,A2:A&B2:B,rg,IMPORTRANGE("URL","Sheet1!A2:F"),XLOOKUP(sk,CHOOSECOLS(rg,1)&CHOOSECOLS(rg,2),CHOOSECOLS(rg,6))))

Cú pháp: XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

Chúng ta chỉ sử dụng các đối số được đánh dấu đậm.

Query như một sự thay thế cho Vlookup trong Importrange trong Google Sheets

Bạn có thể sử dụng Query như một phương án thay thế cho Vlookup. Chúng ta có thể sử dụng khả năng lọc của nó để có được kết quả cần thiết trong một trang tính thứ hai từ trang tính đầu tiên.

Query với Importrange của Google Sheets để nhập các giá trị phù hợp

Ví dụ:

Khóa tìm kiếm trong ô A2 trong tệp “Analysis” là “Justin”.

Query có thể khớp giá trị này trong dữ liệu Importrange (“Sales”) và trả về giá trị tương ứng từ cột 6.

=Query(IMPORTRANGE(“URL”,”Sheet1!A2:F”),”Select Col6 where Col1='”&A2&”‘”)

Nếu bạn có nhiều khóa tìm kiếm trong Cột A, thì bạn phải kéo công thức này xuống.

Tất nhiên, chúng ta có thể bao gồm tất cả các khóa tìm kiếm cùng một lúc bằng cách sử dụng Matches regular expression match trong Query.

Dưới đây là một ví dụ khác về công thức thay thế Vlookup Importrange trong Google Sheets.

Lần này hãy xem cách tìm kiếm “xuống” hai cột bằng Query.

Nó tương tự với nhiều điều kiện trong công thức Vlookup Importrange.

=QUERY(IMPORTRANGE(“URL”,”Sheet1!A2:F”),”Select Col6 where Col1='”&A2&”‘ and Col2='”&B2&”‘”)

Bạn có thể kéo nó xuống để bao gồm nhiều khóa tìm kiếm.

Kết luận

Query trong Importrange trong Google Sheets ghi điểm hơn Vlookup Importrange chỉ ở một điểm.

Đó là sự đơn giản của Query trong việc sử dụng và hiểu.

Bỏ qua điều đó, Vlookup Importrange tốt hơn rất nhiều vì có thể nhanh chóng điền một kết quả mảng.

Sử dụng hàm LET như ví dụ của tôi khi bạn muốn kết hợp nhiều công thức Importrange cho Tìm kiếm. Đó là cách tăng hiệu suất.

Trong vi dụ Vlookup Importrange trên, chúng ta chỉ sử dụng importrange một lần.

Welcome to Crawlan.com

GiGiKing

Related posts