[et_pb_section fb_built=”1″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Combining the power of VLOOKUP and IMPORTRANGE is a great way to transfer specific data between two spreadsheets. Expand the horizons of both functions and apply vlookup across Google Sheets.<\/span><\/p>\n Continue reading to use VLOOKUP across Google Sheets and discover how Sheetgo can provide a great alternative.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”overview” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let\u2019s review how IMPORTRANGE works. It\u2019s straightforward and only needs two arguments: the URL of the source spreadsheet and the range to import from it.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =IMPORTRANGE(spreadsheet_url, range_string)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n A few pointers to keep in mind:<\/span><\/p>\n Check out our <\/span>ultimate guide to IMPORTRANGE<\/span><\/a>.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”understanding” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The VLOOKUP function searches across a range, one row at a time. If it finds a specific value in the first column, it returns a value in the same row from a specified column.<\/span> As its name indicates, it\u2019s commonly used to look up data.<\/span><\/p>\n It takes four arguments:<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =VLOOKUP(search_key, range, index, [is_sorted])<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Check out our <\/span>deep dive into this function<\/span><\/a>.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”combining” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Using these two functions together allows us to apply VLOOKUP between two Google Sheets. Something that would be impossible otherwise.<\/span><\/p>\n The idea is simple, we just have to use IMPORTRANGE as the VLOOKUP range. Below is the basic syntax for this combination.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =VLOOKUP(search_key, range, index, [is_sorted])<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let\u2019s take a look at what that formula would look like with real information.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =VLOOKUP(A3, IMPORTRANGE(“2JLmFDIKcZPATrjWF_9js8Pvw3d2mWzSzYypB1s4z8uo\/edit#gid=1703103179”, “follow_up!A:B”), 2, FALSE))<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This approach is good for importing small data sets.<\/b> However, if working with large spreadsheets, it\u2019s better to use VLOOKUP on the source spreadsheet and import the result.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”going” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The VLOOKUP can only be applied to one search key at a time. That means you would have to copy-paste it across necessary cells. To avoid this, we can add ARRAYFORMULA.\u00a0<\/span><\/p>\n The formula below is an improved version. It includes this other function and provides a range for the VLOOKUP search key.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =<\/span>ARRAYFORMULA<\/b>(<\/b><\/span>VLOOKUP(<\/span>A3:A<\/b><\/span>, IMPORTRANGE(“2JLmFDIKcZPATrjWF_9js8Pvw3d2mWzSzYypB1s4z8uo”, “follow_up!A:B”), 2, FALSE)<\/span>)<\/b><\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Unfortunately, this new formula also introduces an issue: the arrayformula returns a series of errors if information is missing.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”cleaning” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The errors introduced by the last modification can be fixed by constraining the number of rows in the array. For that we will use ARRAY_CONSTRAIN to add this limitation. Again, the modifications are in bold.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =<\/span>ARRAY_CONSTRAIN(<\/b><\/span>ARRAYFORMULA(VLOOKUP(A3:A, IMPORTRANGE(“2JLmFDIKcZPATrjWF_9js8Pvw3d2mWzSzYypB1s4z8uo”, “follow_up!A:B”), 2, FALSE)), <\/span>COUNTA(A3:A), 1)<\/b><\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The only significant change in this formula is the use of COUNTA for the number of rows. This function will count all the cells containing values that VLOOKUP can try to match.<\/span><\/p>\n And that\u2019s the final iteration of this formula, these final changes made it simpler and more efficient.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”common” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Using both VLOOKUP and IMPORTRANGE can significantly enhance your data management. Here are some use cases that highlight how this combination could be useful.<\/span><\/p>\n Employee Information<\/b><\/p>\n Inventory Management<\/b><\/p>\n Financial Analysis<\/b><\/p>\n With the power of VLOOKUP and IMPORTRANGE on your side, you can manage and analyze data across multiple spreadsheets.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”using” _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Unfortunately relying heavily on both of these functions can be problematic. <\/span>Importrange breaks often and slows down spreadsheets<\/b>.<\/span><\/p>\n We have a solution: Sheetgo connections. <\/b>This feature can transfer data like IMPORTRANGE and process it like VLOOKUP. But it\u2019s more efficient, as it allows you to configure when to transfer the data. It also gives you a lot of processing options and allows you to introduce forms to gather data.<\/span><\/p>\n Since Sheetgo is a no-code tool you can forget about using and maintaining all these complicated functions. Just set up your connections and keep important data close at hand.<\/span><\/p>\n Cut out data handling tasks and free up time for more important tasks, <\/span>try out Sheetgo today<\/span><\/a>.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”use” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now you know how to put these two great functions together. For a related set of complementary functions, check out <\/span>how to combine QUERY and IMPORTRANGE<\/span><\/a>.<\/span><\/p>\nAn overview of IMPORTRANGE<\/span><\/h2>\n
\n
Understanding VLOOKUP<\/span><\/h2>\n
\n
Combining VLOOKUP and IMPORTRANGE<\/span><\/h2>\n
Going a step further with ARRAYFORMULA<\/span><\/h3>\n
Cleaning up with ARRAY_CONSTRAIN<\/span><\/h3>\n
Common use cases<\/span><\/h2>\n
\n
<\/ul>\n
\n
\n
Using Sheetgo to transfer and process data<\/span><\/h2>\n
Use VLOOKUP between two Google Sheets<\/span><\/h2>\n