+ Reply to Thread
Results 1 to 10 of 10

How do I use the address/match function to return an entire range reference - ie: C:C

  1. #1
    Registered User
    Join Date
    07-02-2009
    Location
    San Diego
    MS-Off Ver
    Excel 2003
    Posts
    12

    How do I use the address/match function to return an entire range reference - ie: C:C

    I can get the match function to tell me that something is in column 3

    I can get the address function to return the reference of a column and a row: ie: $C$3

    But I can't get a function that just says "hey it's in column $C:$C,


    the goal is to have the sum range in my sum if/sumifs formulas not be static but instead be able to change based on whatever my lookup value is. So that way I can have a more dynamic formula and not worry about always making sure everything is in the same column or maintains the same column format

    So match formula tells me the column a lookup is in, then i want the sumif formula to sum the range of that column. This would have many other applications though

    See the attached excel file for a more detailed example of what i'm trying to do.

    I would prefer a non array formula solution as i suck at array formulas at the moment


    Thanks for any help
    Attached Files Attached Files

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: How do I use the address/match function to return an entire range reference - ie:

    The "standard" way to obtain the column when it's variable is to use INDEX and MATCH with a zero for the row index, e.g. in your case

    =INDEX(B:G,0,MATCH(I5,B1:G1,0))

    MATCH matches I5 against B1:G1 and the zero (as row index) means you get the whole column....so within SUMIF that would be

    =SUMIF(A:A,I1,INDEX(B:G,0,MATCH(I5,B1:G1,0)))

    but if you are really only returning a single value (at the intersection of the row and column matches) then INDEX/MATCH/MATCH is probably "better"

    =INDEX(B2:G4,MATCH(I1,A2:A4,0),MATCH(I5,B1:G1,0))

    That method can return a text value if required, unlike SUMIF
    Audere est facere

  3. #3
    Registered User
    Join Date
    07-02-2009
    Location
    San Diego
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: How do I use the address/match function to return an entire range reference - ie:

    Thank you

    Does this work for finding just the row range too

    IE using index with match function for row and then leaving the column field as 0

    also is there a way to get this part of your formula

    "INDEX(B:G,0,MATCH(I5,B1:G1,0)))" to actually return the column range in text format or address format IE: $F:$F

    or are there any formulas that will return a range in that format

    thanks

  4. #4
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    Re: How do I use the address/match function to return an entire range reference - ie:

    You could use a match to find a cell, then wrap that in the Cell Address function to get the lcoation, ie on your sample =Cell("Address",I5) the cell that contains lizard, returns $I$5 you could using left and right text functions strip that donw to I5 and again that could further be stripped back and joined to give you I:I
    Windows 7 using Office 2007 & 2010

    Remember your [ code ] [ /code ] tags, makes reading soooo much easier

  5. #5
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    Re: How do I use the address/match function to return an entire range reference - ie:

    For instance =INDIRECT(LEFT(RIGHT(CELL("address",I3),3),1)&":"&LEFT(RIGHT(CELL("address",I3),3),1)) the result based on where Lizard is I:I

    Wrap in Sum to get the totals if thats what you need, but if it's just the column name based on where Lizard is the above will do it

  6. #6
    Registered User
    Join Date
    07-02-2009
    Location
    San Diego
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: How do I use the address/match function to return an entire range reference - ie:

    excel doesn't make it easy sometimes

    Does microsoft have a place where you can submit ideas for future Excel formulas.

    Ie

    Search(lookup value, 0 or 1)

    It would search the entire spreadsheet for a value then return the column it's in if the 2nd field is 0 and the row it's in if the 2nd field is 1

    or Range(lookup value, 0 or 1)

    It searches the entire spreadsheet for a value(1st instance) then returns the column range it's in in address format Ie: E:E if the 2nd field is 0, and the row range it's in in address format ie 1:1 if the 2nd field is 1

    That would make things so much easier than having to do index + match, + left right indirect and so on
    Last edited by heyzeus17; 10-02-2011 at 04:08 PM.

  7. #7
    Registered User
    Join Date
    07-02-2009
    Location
    San Diego
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: How do I use the address/match function to return an entire range reference - ie:

    So i've been tinkering around with this formula

    =INDEX(B:G,0,MATCH(I5,B1:G1,0))

    and I can get it to work for my purposes where I can switch columns around/change the order and it still works great, so that's awesome.

    But I'm wondering if it is possible to have both the row and column variable without some array formula.

    I'm guessing for the row to be variable you would also have to have headers in the row column.

    I've tried taking this formula:

    =INDEX(B:G,0,MATCH(I5,B1:G1,0)) and Nesting an index & match function inside the B1:G1 part, but then it seems to be circular to me where the nested function would have to have a function nested in it and so on....

    and then my brain collapses from too much nesting and it doesn't work, so i think i'm running in circles if i try to make both the row and column variable

    I tried throwing it into this too: =SUMIF(A:G,I1,INDEX(A:G,0,MATCH(I5,1:1,0)))

    and tried making the blue highlighted part variable and couldn't get that to work.

    It's like I got it to work so the columns


    the data I'm actually working with has headers all in row 1, and then in the columns below row one is data variables

    I would like it so if the headers were in row 5 or 10 or 7 everything would still work. The idea is that I can know to paste everying in so headers are in row 1 but if someone else pastes the raw data into a sheet with the headers in row 2 nothing will work

  8. #8
    Valued Forum Contributor khamilton's Avatar
    Join Date
    10-08-2009
    Location
    IL
    MS-Off Ver
    Excel 2007
    Posts
    345

    Re: How do I use the address/match function to return an entire range reference - ie:

    So is your example different than what you are really working with?
    Please acknowledge the response you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

  9. #9
    Registered User
    Join Date
    07-02-2009
    Location
    San Diego
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: How do I use the address/match function to return an entire range reference - ie:

    Quote Originally Posted by khamilton View Post
    So is your example different than what you are really working with?
    it's sort of similar

    I can't show the data i'm working with(proprietary) and it's kind of hard to recreate it

    the only real difference is there are no headers in the row/column A with my data

    the data I'm working with has headers all in row 1 from column A to Column K

    the rows below the column headers are filled with the data, some of the raw data sheets have unique data that occurs only once, others have it occurring multiple times

    on the sheets where a value only occurs once or is unique it would be cool to be able to do variable rows

  10. #10
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How do I use the address/match function to return an entire range reference - ie:

    Does microsoft have a place where you can submit ideas for future Excel formulas.
    It does but ....
    it's sort of similar
    makes it somewhat tricky
    Perhaps you should review/understand DLLs' sugestion before you embark upon that route ...
    Last edited by Marcol; 10-02-2011 at 08:39 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1