Results 1 to 10 of 10

Extracting data with VLOOKUP. How to handle duplicates?

This is a discussion on Extracting data with VLOOKUP. How to handle duplicates? within the Excel Questions forums, part of the Question Forums category; VLOOKUP only allows you to look up the first occurence of data. What if the first one is not the ...

  1. #1
    Board Regular
    Join Date
    Mar 2003
    Posts
    76

    Default Extracting data with VLOOKUP. How to handle duplicates?

    VLOOKUP only allows you to look up the first occurence of data. What if the first one is not the one I want to extract?

    For example:


    Name | Age | IQ |
    --------------------------------
    Bob 15 100
    Mary 20 96
    Bob 7 110


    =VLOOKUP("Bob",A2:C4,3)

    I am using VLOOKUP to find BOB and extract his IQ. But VLOOKUP will find the first instance of BOB and return 100 as the result.

    I need it to show me the BOB #2 IQ because he is less than 10 years old. I'd like the result to be "110"

    Sorry about this lame illustration, does it make sense as to what I am looking for? how would I accomplish this task?

    Jake

  2. #2
    Board Regular
    Join Date
    May 2002
    Posts
    890

    Default

    If possible instead of the age that I used, use a column for last names

    ******** ******************** ************************************************************************>
    Microsoft Excel - double.xls___Running: xl97 : OS = Windows NT 4
    (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
    F
    G
    15
    Bob15130Bob-@-15bob7115
    16
    Mary12119Mary-@-12***
    17
    Bob7115Bob-@-7***
    Sheet1*

    [HtmlMaker 2.20BETA] 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 trick is to create "unique" values to lookup

  3. #3
    Board Regular
    Join Date
    Mar 2003
    Posts
    76

    Default

    What if creating unique data is not an option? All the lookup data will be imported and cannot be changed.

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Then you can use the array formula ...

    {=INDEX($C$1:$C$3,MATCH(E1&F1,$A$1:$A$3&$B$1:$B$3,0))}

    see the following simulation ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book2___Running: xl97 : OS = Windows 98
    (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
    F
    G
    1
    Bob15130*bob7115
    2
    Mary12119****
    3
    Bob7115****
    Sheet5*

    [HtmlMaker 2.20] 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.
    Regards!

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

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Or, with the same data set in the earlier posts, you can use the following SUMPRODUCT formula ...

    =SUMPRODUCT((A1:A3=E1)*(B1:B3=F1)*(C1:C3))
    Regards!

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

  6. #6
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    4,965

    Default

    Hi,

    What if we could tell the LookUp Function what Instance of the LookUp Value to search For in the First Column.Wouldn't that be nice?
    Certainly,this would give you much more control.

    Well,to achieve this, I have created this UDF Called CustomVLookUpwhich takes an Extra Argument : 'Inst' which determins the Instance or Occurrence of the Value to look for in the First Column of the Table.

    The 3 first arguments are the same as the Built in Excel VLookUp Function.

    An additional advantage is that if a Match is not found then the Function returns the message : 'No Match'which is more Descriptive than Excel Error messages.

    Below is the Code:

    Code:
    Public Function CustomVLookUp(vl, Table As Range, Col, Inst)
        On Error GoTo x
        Set SearchCol = Range _
        (Table.Cells(1, 1), Cells(Table.Rows.Count, 1))
        If Abs(Int(Inst)) <> Inst Or Inst > _
        Application.CountIf(SearchCol, vl) Or Inst = 0 Then GoTo x
        Set mtch = SearchCol.Find(vl, LookIn:=xlValues)
        For i = 1 To Inst - 1
            Set mtch = Range(mtch, Cells(Table.Rows.Count, 1)) _
            .Find(vl, LookIn:=xlValues)
        Next
        CustomVLookUp = mtch.Offset(0, Col - 1)
        Exit Function
    x:  CustomVLookUp = "No Match"
    End Function
    Below is a Worksheet Demo with Different Values for the Instance Argument including negative numbers and 0:

    ******** ******************** ************************************************************************>
    Microsoft Excel - turedat.xls___Running: xl2002 XP : OS = Windows XP
    (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
    F
    G
    H
    1
    NameAgeIQ**Name***:BOB*
    2
    BOB3010*****
    3
    MARY2010**InstanceFunction*Result*
    4
    BOB508**-2No*Match*
    5
    BOB104**0No*Match*
    6
    BOB1120**110*
    7
    JOHN102**28*
    8
    BOB6812**34*
    9
    ALI1001**420*
    10
    MORAD6100**8No*Match*
    11
    MARY3560**512*
    12
    BOB4474**0.25No*Match*
    13
    BOB1585**785*
    14
    JOHN8455**100No*Match*
    15
    AMIR4085**674*
    16
    ALI7768**110*
    17
    ********
    18
    ********
    Sheet1*

    [HtmlMaker 2.32] 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.


    Hope this helps.
    Office/Excel 2007 Win XP

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    8

    Default Convert BOB to Bob1, Bob2, Bob3, etc

    Column A Column B Column C Column D col. E

    BOB =COUNTIF($A$1:A1,A1) =A1&B1 36 115
    BOB
    BOB
    BOB
    etc...


    _____ =VLOOKUP(A8,A1:E4,5,)



    Column A has 4 ocurrences of BOB

    Column B will count that ocurrences as 1, 2, 3, 4...

    Column C will concatenate A with B (BOB1, BOB2, BOB3, BOB4)
    This are unique values that VLOOKUP will understand.

    In cell A8 write BOB1, BOB2, BOB3 or BOB4 whichever you are looking for.

    VLOOKUP sees A8, Search in A1:E4, When it finds A8 looks for column 5 in this case column E, A comma after number 5 will calculate EXACT match of A8 but if A8 doesn't exist in A1:E4 will return #N/A. Try it and tell me.

    _________
    From PUERTO RICO with pride

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,922

    Default

    Quote Originally Posted by jakemelon
    What if creating unique data is not an option? All the lookup data will be imported and cannot be changed.
    Creating an additional column (using a concatenation formula) does not change the imported data at all. You can even create the additional column in a different worksheet and use it for multi-key/conditional retrieval. I might as well add that this method (see the maxflia post) is in performance terms the cheapest.

  9. #9
    Board Regular
    Join Date
    Jan 2003
    Location
    Boise, ID
    Posts
    94

    Default Re: Extracting data with VLOOKUP. How to handle duplicates?

    The concept behind this UDF is great. Alas, I can not get it to work. Has anyone else tried it?

  10. #10
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default Re: Extracting data with VLOOKUP. How to handle duplicates?

    see the arbitrary lookup example here:

    http://www.cpearson.com/excel/lookups.htm

    and an example of using the method to get the n'th match here;

    http://www.mrexcel.com/board2/viewto...=lookup#221768

    paddy

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
  •  


DMCA.com