+ Reply to Thread
Results 1 to 5 of 5

INDEX and MATCH together

  1. #1
    Registered User
    Join Date
    11-07-2012
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    1

    INDEX and MATCH together

    The formula below returns the last number in a single, changing column of bank balances.
    I don't know how it does it.

    I think I pulled it from this site before registering; it's the quality of the answer that prompted me to join. So...

    I have read several explanations of how INDEX and MATCH work together, and "how powerful they are" blah blah; but have yet to encounter an explanation that I can understand. To assist with anyone who offers an answer -- I am not a beginner.

    How does this work? What does the first argument in MATCH do?

    Thanks for any help.

    INDEX(I472:I1032,MATCH(9.99999999999999E+307,I472:I1032))
    Last edited by Knessing; 12-24-2012 at 11:45 AM. Reason: Improve post Title

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: How does this work?

    Please see the forum rules about proper thread titles and adjust accordingly...
    HTH
    Regards, Jeff

  3. #3
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: How does this work?

    Knessing, welcome to the forum.

    I have a very brief explanation of how INDEX/MATCH works in another thread on this forum, which I am quoting below for you - is that "plain English" enough to set you on the right path? I have also included a simple spreadsheet which was with the original post.

    Your formula is looking in the range I472:I1032 for the value 9.99999999999999E+307, and if it finds that value, it is returning it.


    Quote Originally Posted by BB1972 View Post
    Sure. The INDEX/MATCH combination basically looks for a value that you have input, and returns a corresponding value from a column of your choice. The syntax can be a bit awkward to get your head around at first, but remembering "What you want returned, what you're looking for, where you're looking for it" helped me. Starting small, with data I'm familiar with also helps (as I can generally spot whether the result is correct or not straight away.)

    Have a look at the attached - I imagine most people know the capital cities of Ireland, England & France. You can see that if you enter the name of the country you're searching for the capital of in F2, the capital city of that country is returned in H2. The formula in H2:

    Please Login or Register  to view this content.
    says return from B2:B4 ("INDEX(B2:B4,") if there is a match for F2 ("MATCH(F2,") in the search array A2:A4. The "0" at the end specifies that it must be an exact match.

    INDEX/MATCH is a very useful, versatile combination of formulas, which has many, many uses.

    Hope this helps.
    Attached Files Attached Files
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: INDEX and MATCH together

    its the same as this
    =MATCH(9.99999999999999E+307,A1:A100,1) if the 1 is left out it acts the same
    because the match cannot find 9.99999999999999E+307 (which is a very very big number) it returns the row of the last number it finds
    you can also use
    =LOOKUP(9.99999999999999E+307,A1:A100) this will return the last value it finds so you dont need to use index at all
    in reality you can just use any number that is bigger than the largest in your range
    eg =MATCH(MAX(A1:A100)+1,A1:A100,1)
    =LOOKUP(MAX(A1:A100)+1,A1:A100)
    or use something like 99^99
    =INDEX(A1:A100,MATCH(99^99,A1:A100,1))
    =LOOKUP(99^99,A1:A100)
    you can also do the same with text
    =INDEX(A1:A1100,MATCH(REPT("z",50),A1:A100,1))
    REPT("z",50)=50 z's
    so unless there is a text string with 50 z's which is unlikely to say the least then it will return the text value of the last cell with text in the range
    read about big number here
    http://www.excelforum.com/excel-gene...99e-307-a.html
    Last edited by martindwilson; 12-24-2012 at 12:34 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: INDEX and MATCH together

    This may help also Getting The Last Value In Range

+ 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