Note: If you are looking for more functions, we have a huge guide of 100+ Functions & Formulas for Google Sheets & Excel Basics<\/a>\u00a0<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Address-image-1-1-3.png” alt=”address-function-google-sheets-1″ title_text=”address-function-google-sheets-1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text admin_label=”Connections T – Automate between spreadsheets – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button-light vertical-banner-container” _builder_version=”4.27.4″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” saved_tabs=”all” global_colors_info=”{}” global_module=”255443″ theme_builder_area=”post_content”]<\/p>\n Automate data transfers between spreadsheets Find out how<\/a><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As is the case with all functions and formulas, the best way to understand ADDRESS is to see it in use. <\/p>\n Below are a few combinations of the function.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Address-image-2-1-2.png” alt=”address-function-google-sheets-2″ title_text=”address-function-google-sheets-2″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the snapshot above, notice that the R1C1 style notation has brought in square brackets [ and ] in rows 9 and 10.\u00a0<\/span>Please note that a value within the square bracket indicates either a relative row or column<\/em><\/strong>. So, for instance, if the result of the formula is R3C[1], that translates to this: 3rd row is absolute while the 1st column is relative.<\/p>\n So far, so good. Now, is there a way that we can use the output from the ADDRESS Google Sheets function as a cell reference, given that it returns only a text? Sure, there is! Use this output as an input to the INDIRECT function<\/em><\/strong>, and voila, we have the cell reference with us!<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Address-image-3-1-2.png” alt=”address-function-google-sheets-3″ title_text=”address-function-google-sheets-3″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The ADDRESS function can also be used to translate column numbers into column letters. Say you have a list of columns in numerical format, and want to translate these into column letters \u2013 this can be achieved by combining the ADDRESS and SUBSTITUTE functions.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Address-image-4-1.png” alt=”address-function-google-sheets-4″ title_text=”address-function-google-sheets-4″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you can see above, the ADDRESS function provides the cell reference, and the SUBSTITUTE function substitutes the row number (1) for an empty text string. What remains is the column letter! This is achieved with the formula =Substitute(Address(1,A5,4),”1″,””)<\/strong>.<\/p>\n Another use of the address is when you have a long list of data, and you want to know the address of a particular value. In the example below, I use the MATCH function alongside the ADDRESS function to find the address of a particular value. While the dataset is small in the example, you can imagine that it could be helpful in a very large dataset when you are searching for a single data point.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Address-image-5-1-2.png” alt=”address-function-google-sheets-5″ title_text=”address-function-google-sheets-5″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The MATCH function identifies the location of the cell that contains the largest height, and the ADDRESS function returns the address of this cell. This is done by typing =Address(Match(E2,$B:$B,0),2)<\/strong>.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n I\u2019ll end this topic with one last example. Here\u2019s an interesting use case for finding out the address of the cell that has minimum sales figures.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Address-image-6-1.png” alt=”address-function-google-sheets-5″ title_text=”address-function-google-sheets-5″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n That\u2019s how to get started with Google Sheets ADDRESS function. Looking for more spreadsheet tutorials and tips? Check out our other posts below!<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" The ADDRESS\u00a0Google Sheets function\u00a0returns a cell\u2019s address in the form of a text. It is noteworthy that the resulting address is a string and not the cell reference. Note: If you are looking for more functions, we have a huge guide of 100+ Functions & Formulas for Google Sheets & Excel Basics\u00a0Syntax=ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], […]<\/p>\n","protected":false},"author":40,"featured_media":29433,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"The ADDRESS<\/a> formula in Google Sheets returns a cell's address in form of a text. It is noteworthy that the resulting address is a string and not the cell reference.\nSyntax<\/h2>\n
\n
\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/run-automatically-connect-sheet-icons.webp\"){kind=icon}
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\n
<\/span><\/p>\nHow to use the ADDRESS function<\/h2>\n
Combine ADDRESS and INDIRECT<\/h3>\n
Translate column numbers to column letters<\/h3>\n
Find the address of a particular value<\/h3>\n
Address of the minimum value<\/h3>\n
Syntax<\/h3>\nADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])<\/strong><\/span>\n
\n \t
![\"ADDRESS](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/Adress.png\")
\n\n \t
Usage: ADDRESS Formula<\/h3>\nAs is the case always, we understand better with help of examples. So, here are a few combinations of the formula.\n\n
![\"ADDRESS](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/adress2.png\")
\n\nIn the snapshot above, we will notice that the R1C1 style notation has brought in square brackets [ and ] in rows 9 and 10. Please note that a value within the square bracket indicates either a relative row or column. So, for instance, if the result of the formula is R3C[1], that translates to this: 3rd row is absolute while the 1st column is relative.\n\nSo far, so good. Now, is there a way that we can use the output from ADDRESS formula as a cell reference, given that it returns only a text? Sure, there is! Use this output as an input to the INDIRECT formula, and voila, we have the cell reference with us!\n\n![\"ADDRESS](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/adress3-1.png\")
\n\nWe'll end this topic with one last example. Here's an interesting use case to finding out the address of the cell that has minimum sales figures.\n\n![\"ADDRESS](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/adress4.png\")
\n\n\u00a0","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-3375","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/3375","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=3375"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/3375\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/29433"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=3375"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=3375"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=3375"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}