Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: how to use vlookup with dublicate values

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Sabir Mamedov
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    in my worksheet i have 2 columns with data:
    column A (a2:a83)--- customer names;
    column B (b2:b83)--- number of boxes (coffee)they received

    in some cells in column B there are the same numbers
    (i.e. B13,B28,B32,B45,B68 = 750)
    i want vlookup 750 in column B and return all the company
    names which are in column A -- in cells D2:D6. i used
    vlookup function but without any success, because vlookup
    returns only first value.

    Thank you.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Louisville, KY
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You are right that vlookup will not bring back all occurrances.

    If you are willing to sort the list, there are two options:

    1. is the obvious sort on the number of boxes and all customers will group by the number of boxes.

    2. If you have a specific number that you are looking for, you could add in column C
    =IF(A2="750","Hit","")
    and copy the formula down to all records.

    This would display the word "Hit" for all customers with 750 boxes. Sort on column C and all the "Hit"s will group together.
    The 750 in the formula could be a reference to a cell where you enter the number you are seeking.

    If the order of the list is important, add a column which numbers the rows as you now have them and you can always sort on this column to return to the original order.


  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Sabir Mamedov
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thank you Don,
    is there a shorter way i.e. you write the formula in d2, fill down and OK?

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Abbas and Don:
    Why not use Data|Filter technique to filter records with the desired value, in this case 750 in column B. Let us say I have some entries in cells A1 through A9, and numbers like 750 and more in cells B1 through B9, the example below shows original data and then the filtered data

    original data
    CloA ColB
    A2 120
    A3 750
    A4 950
    A5 750
    A6 315
    A7 317
    A8 750
    A9 215

    filtered data
    CloA ColB
    A3 750
    A5 750
    A8 750

    HTH


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    Sabir Mamedov
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    many thanks Yogi!

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Louisville, KY
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Yes, you'd only have to key the formula once and then fill down. If you reference a cell where the value of interest is keyed, be sure that the formula uses "$c$r" to make the reference absolute.

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
  •