Comments on: Excel ADDRESS function with formula examples

To create a cell reference in Excel, you can type the column and row coordinates manually. Alternatively, you can get a cell address from the row and column numbers supplied to the ADDRESS function. Continue reading →

Table of contents

Comments page 2. Total comments: 67

  1. Hi, I have data that is enetered on sheet 1 and have selected a range of that data that is important to automatically appear on sheet2. I would then like to have that data on sheet2 which is in cell E27, search a given range A1 to H22 for a match and provide me with the cell reference of that match. Is this possible?
    Any help you can provide would be fantastic, thank you.

  2. I have a partial text in same column multiple times & require to retrieve row number of each.
    Formula. =MATCH(DG5&”*”,$AK:$AK,0)*1 Returns 27 Exact match
    When I copy down *2 it returns 54 which isn’t a match as it multiply first row number by 2.
    DG = partial text
    AK:AK. = column reference

    Regards

    Tony

    1. Hello!
      Sorry, it's not quite clear what you are trying to achieve. Without seeing your data it is difficult to give you any advice. Could you please describe it in more detail? Why are you multiplying the formula by 2?

      1. Hi Alexander

        I don’t won’t to multiply, need to find row number of each partial text in the same column, the formula =MATCH(DG5&”*”,$AK$AK,0)
        How do I change the formula to return row number 1st partial text, 2nd partial text, 3rd partial text & so on in same column?

        Regards

        Tony

        1. Hi Alexander

          Sent a sample excel sheet for your review.

          Regards

          Tony

  3. I am trying to compare two colums and get the cell address. for example if column 1 has a match in column 2 what is the cell address of the match in column 2

    1. Hello!
      Please check out the following article on our blog, it’ll be sure to help you with your task: Compare 2 columns to find duplicates using Excel formulas
      I believe the following formula will help you solve your task:

      =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique",ADDRESS(MATCH(A1,$B$1:$B$10000,0),2))

      I hope I answered your question. If something is still unclear, please feel free to ask.

  4. Svetlana,
    Thank you. This is exactly what I was looking for after about searching through 20 articles, and to evaluate a short column of positive nonzero integers.
    =MODE(B13:INDIRECT(B5)) Where B5 contains =ADDRESS(COUNTIF(B13:B1000,">0")+ROW(B12),2,4)
    And where B13 is the first cell in the data, and the last cell is calculated by the ADDRESS function.
    This is also the first time I used a formula in the ":" range syntax as shown.

  5. I am trying to figure out which functions to use to find ”number of people attending”, from a column with blank cells and zeros. However I have the cost of each invitation, tax, and the amount it adds up to. Help!

    1. Hello!
      Your question is not related to using the ADDRESS function. I recommend that you study this guide on counting the number of values and, if necessary, ask your question there.

  6. Hi, I am trying to increment the row number of the value of a cell. This value references another workbook.
    The formula in cell A1 which gives me the absolute address from the other workbook is =CELL("ADDRESS",INDEX('[WORKBOOKNAME.XLS]WORKSHEETNAME'!$A:$A,MATCH("*WA(*",'[WORKBOOKNAME.XLS]WORKSHEETNAME'!$A:$A,0)))
    The value of this results in:
    '[WORKBOOKNAME.XLS]WORKSHEETNAME'!$A:$251
    I now want to use this address in another formula but increment by 1 for each row it is used in
    Is this possible, many thanks

    1. Hello!
      I’m sorry but your task is not entirely clear to me.
      $A:$251 - this link is not possible.
      Write down exactly what you are getting now and what you want to get when you move to the next line. Describe your problem in more detail.

      1. Ultimately I want to use the cell address of a cell from another workbook.
        In order to do this I search a column in the different workbook for a matching string and return its cell address;
        =CELL("ADDRESS",INDEX('[WORKBOOKNAME.XLS]WORKSHEETNAME'!$A:$A,MATCH("*WA(*",'[WORKBOOKNAME.XLS]WORKSHEETNAME'!$A:$A,0)))
        this works and gives me a result in cell A1 of...
        '[WORKBOOKNAME.XLS]WORKSHEETNAME'!$A:$251
        I then want to use this cell address in another formula to retrieve the value of the cells and offset cells in the other workbook.

        I don't want to use VBA.

  7. =ADDRESS(MATCH('ROOM1'!$Q$11'ROLL NUMBERS'!A:A,0)+1,1) NOT GETTING REFRENCE OF SECOND SHEET FROM SHEET 1

    1. Hello!
      I cannot validate the formula on your data. But something like this will work for you

      =ADDRESS(MATCH(Sheet6!Q11,Sheet16!A:A,0)+1,1)

      Hope this is what you need.

  8. I am trying to get the string from cell address.
    something that does this: =SomeFormula(A1) returns A1 as string.

    thank you in advance.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =ADDRESS(ROW(),COLUMN(),4,1)

  9. I have such SUMIFS formula working fine:
    =SUMIFS('Taches Dev'!V3:V$1000,'Taches Dev'!B$3:B$1000,A2,'Taches Dev'!C$3:C$1000,L$1)
    where
    'Taches Dev'!V3:V$1000 is my sum range.

    In another 2 cells, I have formulas returning the adresses of the two bounds of my range
    =ADRESSE(3;EQUIV(M1;'Taches Dev'!A2:AI2;0);;;"Taches Dev") --> Taches Dev'!V3
    =ADRESSE(1000;EQUIV(M1;'Taches Dev'!A2:AI2;0);;;) --> V$1000

    I'd like to use directly these 2 formulas into the SUMIFS function for determining my sum range.

    But I'm getting an error.
    Any idea?
    Many Thanks in advance

  10. is there any function in excel that finds the requied value in a range and returns Left most column value and top most row value in that range for the found cell.

  11. is there any function in excel that finds the requied value in a range and returns Left most column value and top most row value in that range for the found cell.

  12. Hi Svetlana Cheusheva. I have this formula:
    =CELL("address";INDEX(Sheet2!A2:D5;MATCH(Sheet1!A1;Sheet2!D2:D5;0);4;1))
    That returns:
    '[Book1 (version 1).xlsb]Sheet2'!$D$1
    How to show not the file name?

  13. I’m trying to find the address of the First five row numbers in a given column where continuously the cell values are >= X, There can be multiple instances where we can find the continuous rows where cell values are >=X, but I need to find the first occurrence only.

    For example in column A, continuously values in the 5 rows i.e., A6 A7 A8 A9 A10 are >=x and also in row number A15 A16 A17 A18 A19, etc.
    The row# A6 to A10 is the first occurrence (first five consecutive values) and I need the address of the row numbers as output i.e., A6: A10

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)