Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Lookup for a value in Unsorted data

  1. #1
    Board Regular
    Join Date
    Mar 2004
    Posts
    333
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Lookup for a value in Unsorted data

    Hi!
    I have 2 columns A & B that their data are like these
    A B
    500 0
    501 1
    502 5
    503 0
    504 2
    505 2
    506 4
    507 0
    508 3
    509 5
    510 1
    511 0

    I want to keep the data on column B that way (unsorted) but i want to find the first position the 0, the 1, the 5 etc. is, starting from bottom to top. and for return to get the value of the column A.
    So if i want to find the 0 i ll get as result the 511
    So if i want to find the 5 i ll get as result the 509
    So if i want to find the 4 i ll get as result the 506 and goes on.

    Thanks in advance
    Stathis

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Lookup for a value in Unsorted data

    Quote Originally Posted by stakar
    Hi!
    I have 2 columns A & B that their data are like these
    A B
    500 0
    501 1
    502 5
    503 0
    504 2
    505 2
    506 4
    507 0
    508 3
    509 5
    510 1
    511 0

    I want to keep the data on column B that way (unsorted) but i want to find the first position the 0, the 1, the 5 etc. is, starting from bottom to top. and for return to get the value of the column A.
    So if i want to find the 0 i ll get as result the 511
    So if i want to find the 5 i ll get as result the 509
    So if i want to find the 4 i ll get as result the 506 and goes on...
    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: 11.0 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    1
    XY YX
    2
    5000 0511
    3
    5011 1510
    4
    5025 2505
    5
    5030 3508
    6
    5042 4506
    7
    5052 5509
    8
    5064
    9
    5070
    10
    5083
    11
    5095
    12
    5101
    13
    5110
    Sheet1

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    The formula in E2, which is copied down, is:

    =LOOKUP(2,1/($B$2:$B$13=D2),$A$2:$A$13)

  3. #3
    Board Regular
    Join Date
    Mar 2004
    Posts
    333
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks a lot!!
    It was exactly what i wanted to do!!

    Stathis

  4. #4
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup for a value in Unsorted data

    Now, that's slick.
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  5. #5
    Board Regular
    Join Date
    May 2003
    Location
    Katy, Texas
    Posts
    3,829
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup for a value in Unsorted data

    Just Jon has expressed his appreciation for Aladin's solution! Me, I can see the brilliant results, but, when I find the description for LOOKUP, I find:
    =LOOKUP(lookup_value,lookup_vector,result_vector).

    Would you be so kind as to explain the logic of your formula (for E2)?:
    =LOOKUP(2,1/($B$2:$B$13=D2),$A$2:$A$13)

    From the description, your formula would seem to indicate that:
    lookup_value=2
    lookup_vector = 1/($B$2:$B$13=D2)

    I just can't understand what is going on here???

    Thanks.

  6. #6
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup for a value in Unsorted data

    Quote Originally Posted by RalphA
    Just Jon has expressed his appreciation for Aladin's solution! Me, I can see the brilliant results...


    Well it's briliant!

    1/($B$2:$B$13=D2) is the lookup array, an array that will look something like:

    #DIV/0!, #DIV/0!, 1, #DIV/0!, 1, #DIV/0!

    Looking up 2 in that will return the position of the last "1".
    "Fair Winds and Following Seas"

  7. #7
    Board Regular
    Join Date
    May 2003
    Location
    Katy, Texas
    Posts
    3,829
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup for a value in Unsorted data

    Thank you, Fairwinds! I understood it, pretty much, to mean that it returns the error code for division by 0 when the value in parenthesis is false (a 0), and it returns a 1 for 1/1 when true. I usually have only a vague glimmer of understanding how the handling of arrays is done, but, this time, I do believe I was able to follow your detailed explanations. Thanks again!

    However... (sorry), I still don't understand how it can report the position of the fourth (the last) 0 in the data range. Also, how does it determine the correct answer for the one instance of 4?

  8. #8
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup for a value in Unsorted data

    Looking up 2 here is the same principal as using "BigNum". I'm sure you can find an explanation if you search for Aladins posts on BigNum.
    "Fair Winds and Following Seas"

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Lookup for a value in Unsorted data

    Quote Originally Posted by RalphA
    ...However... (sorry), I still don't understand how it can report the position of the fourth (the last) 0 in the data range. Also, how does it determine the correct answer for the one instance of 4?
    Quote Originally Posted by RalphA
    ...However... (sorry), I still don't understand how it can report the position of the fourth (the last) 0 in the data range. Also, how does it determine the correct answer for the one instance of 4?
    Fairwinds's explanation/exposition is just about right.

    =LOOKUP(2,1/($B$2:$B$13=D2),$A$2:$A$13)

    is structured as:

    LOOKUP(LookupValue,LookupVector,ResultVector)

    where a vector can be an array like {2,3,7,9} or range object like X3:X6.

    The way the formula is set up exploits the fact that

    (A)

    LOOKUP(n,Ref)
    VLOOKUP(n,Ref,1,1)
    INDEX(Ref,MATCH(n,Ref,1)

    will all return the last numerical value from Ref when n is a number that cannot occur in Ref, an example use of which is:

    =LOOKUP(9.99999999999999E+307,A:A);

    And the fact that

    (B)

    the lookup functions ignore error values.

    Since the LookupVector in the formula of interest, that is,

    1/($B$2:$B$13=D2),

    is an array object, I should also add the fact that

    (C)

    the LOOKUP function is capable of returning computed arrays, without control+shift+enter (SumProduct is another example).

    Understanding (A) is essential. It's the topic of a discussion in:

    http://tinyurl.com/5l4j7

    Since

    =LOOKUP(9.99999999999999E+307,A2:A25)

    returns the last numerical value from A2:A25, which is not perse the last value, the following is an obvious extension:

    =LOOKUP(9.99999999999999E+307,A2:A25,B2:B25)

    will return the value from B2:B25 which is associated with the last numerical value in A2:A25. (9.99999999999999E+307 is often referred to as BigNum.)

    Given the foregoing,

    =LOOKUP(2,1/($B$2:$B$13=D2),$A$2:$A$13)

    becomes intelligible for:

    The lookup value 2 is a BigNum with respect to 1/($B$2:$B$13=D2), because 1 divided by any number cannot be equal to or greater than 2. One can replace 2, if so desired, with the BigNum itself (that is: 9.99999999999999E+307).

    ($B$2:$B$13=D2)

    is a conditional that is bound to evaluate to an array consisting of logical values like:

    {TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}

    Then:

    1/{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}

    Since 1 and 0 are Excel's numerical equivalents of TRUE and FALSE under coercion, we get a calculated array like:

    {1;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;1}

    Since the last numerical value is the 12th item in the foregoing array, LOOKUP will retrieve the 12th item from $A$2:$A$13, the ResultVector.

    Let's take up the formula which corresponds to D6 housing 4 (See the exhibit in my original post)...

    =LOOKUP(2,1/($B$2:$B$13=D6),$A$2:$A$13)

    ===>

    =LOOKUP(2,1/({0;1;5;0;2;2;4;0;3;5;1;0}=4),{500;501;502;503;504;505;506;507;508;509;510;511})

    ===> (after the divison)

    =LOOKUP(2,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},{500;501;502;503;504;505;506;507;508;509;510;511})

    As can be seen, the last numerical value (the last instance of 1) is the 7th item. As it so happens, the last numerical value here is also the only numerical value. Hereafter LOOKUP proceeds to fetch the 7th item from

    {500;501;502;503;504;505;506;507;508;509;510;511}

    which is: 506.

    Hope the foregoing is filling in fairwinds's correct exposition at those places where you wanted to have more info.

  10. #10
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup for a value in Unsorted data

    Aladin!
    Thanks for this excelent explanation.

    Now, next time I only need to post a link.
    "Fair Winds and Following Seas"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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