+ Reply to Thread
Results 1 to 3 of 3

FIND FORMULA SIMILAR TO VLOOKUP FOR NON-ASCENDING VALUES

  1. #1
    Kir
    Guest

    FIND FORMULA SIMILAR TO VLOOKUP FOR NON-ASCENDING VALUES

    I am trying to determine a lookup or find formula that will find data in 1
    column and return the data from the 2nd column but the data in the 1st column
    is not in ascending order and I do not want to sort it to be in ascending
    order.

    Ex A B
    1 Sue 5
    2 Bob 3
    3 Max 4

    I want to find Sue and return value 5 for this above array and I do not want
    to sort the data.

  2. #2
    Bob Phillips
    Guest

    Re: FIND FORMULA SIMILAR TO VLOOKUP FOR NON-ASCENDING VALUES

    =INDEX(B:B,MATCH("Sue",A:A,0)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Kir" <[email protected]> wrote in message
    news:[email protected]...
    > I am trying to determine a lookup or find formula that will find data in 1
    > column and return the data from the 2nd column but the data in the 1st

    column
    > is not in ascending order and I do not want to sort it to be in ascending
    > order.
    >
    > Ex A B
    > 1 Sue 5
    > 2 Bob 3
    > 3 Max 4
    >
    > I want to find Sue and return value 5 for this above array and I do not

    want
    > to sort the data.




  3. #3
    Harlan Grove
    Guest

    Re: FIND FORMULA SIMILAR TO VLOOKUP FOR NON-ASCENDING VALUES

    Bob Phillips wrote...
    >=INDEX(B:B,MATCH("Sue",A:A,0)

    ....

    Uh, why not

    =VLOOKUP("Sue",A:B,2,0)

    ?

    Unlike 123 and possibly Quattro Pro, Excel's VLOOKUP includes and
    optional 4th parameter that defaults (default value TRUE or 1) to
    assuming the first column is sorted in ascending order, and matches the
    largest value in the first col less than or equal to the lookup value.
    When the 4th argument is FALSE or 0, VLOOKUP uses exact matching and
    linear searching, and only finds exact (well, case-insensitive)
    matches. [This is for the OP's benefit, not Bob's.]


+ 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