+ Reply to Thread
Results 1 to 5 of 5

Performing Case-sensitive Lookup, please explain how this code works

  1. #1
    Registered User
    Join Date
    06-01-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    38

    Performing Case-sensitive Lookup, please explain how this code works

    =INDEX(C33:C37,MATCH(TRUE,EXACT(J29,B33:B37),0)) <--- How did (TRUE,EXACT(J29,B33:B37) became 3 which then became =INDEX($C$33:$C$37,3)

    Also,how did the dollar sign got there?

    C33:C37
    $0.115
    $0.185
    $0.245
    $0.275
    $0.435

    J29 = West

    B33:37
    NW
    WEST
    West
    MidWest
    East

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Performing Case-sensitive Lookup, please explain how this code works

    That's actually an Array formula and you should see curly brackets around it.

    {=INDEX(C33:C37,MATCH(TRUE,EXACT(J29,B33:B37),0))}

    So the EXACT(J29,B33:B37) is creating an array: FALSE, FALSE, TRUE, FALSE, FALSE

    And then MATCH(TRUE,EXACT(J29,B33:B37),0), returns 3 because the third entry is TRUE.

    That gives you: INDEX(C33:C37,3) which returns the third value in the array C33:C37 which is $0.245.

    The dollar is presumably because the cell is formatted as currency.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    06-01-2013
    Location
    Columbus, ms
    MS-Off Ver
    Excel 2013
    Posts
    68

    Re: Performing Case-sensitive Lookup, please explain how this code works

    Hi Dwexdwex,
    In order for this formula to work it must be entered as an array formula (by pressing CTRL + SHIFT + ENTER). As an array formula MATCH(TRUE,EXACT(J29,B33:B37) looks in every cell between b33 and b37, one at a time. If it finds an exact copy of cell J29 within that range it returns true and continues to the next item. the result is a series of results that looks like this {FALSE, FALSE, TRUE, FALSE, FALSE}. Match then looks in this series of results, NOT cells b33:b37, and determines that the third entry in the series matches what it is looking for, namely TRUE and thus the result of 3. Index then looks up the third entry in the range c33:c37 which is 0.245.

    The Dollar signs come from the formatting which has been chosen. You can change it by going to Home>Numbering>Number Formatting drop down box.

  4. #4
    Forum Contributor
    Join Date
    04-23-2013
    Location
    Abu Dhabi United Arab Emirates
    MS-Off Ver
    Excel 2010
    Posts
    178

    Re: Performing Case-sensitive Lookup, please explain how this code works

    Hi Dwexdwex,

    the match(true...) returns the position of the first TRUE within that array, for example the position of the first character in Cell B33:B37 that is an exact match of one in Cell J29; the INDEX function returns the code in Column C that is stored in corresponding position

    Reference: F1 Get the most out of excel functions & formulas.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Performing Case-sensitive Lookup, please explain how this code works

    Thanks for the rep.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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