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

Thread: Double column VLOOKUP

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

    Default

    I need to do a function like VLOOKUP but two colums of data needt to match, then return
    the data from the third column.

    I have a look up table in this format:
    Last Name|First Name| Org|

    The spreadsheet being loaded with "ORG" info
    has the same format, but there are multiple
    entries of last & first names.

    I cannot use the standard VLOOKUP because I
    have people with the same last names, or the
    same first names. ie: I need to match both
    before the "ORG" is laoded to the spreadsheet.

    Any help would be appreciated.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Check out this web page: http://www.mvps.org/skp/xl00002.htm
    that uses the MATCh function to do this
    elegantly.
    "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

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

    Default

    The way I've gotten aroound this in the past is creating another column that concatinates the Lname and Fname, and then looks up that new column. If Lname and Fname were in Columns B and C, the formula (in Column A) would be +B1&C1 .....OR you could add a Comma Separator with +b1&", "&C1
    Hope this isn't overly simplistic for you.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,604
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-26 10:34, gyoung wrote:
    I need to do a function like VLOOKUP but two colums of data needt to match, then return
    the data from the third column.

    I have a look up table in this format:
    Last Name|First Name| Org|

    The spreadsheet being loaded with "ORG" info
    has the same format, but there are multiple
    entries of last & first names.

    I cannot use the standard VLOOKUP because I
    have people with the same last names, or the
    same first names. ie: I need to match both
    before the "ORG" is laoded to the spreadsheet.

    Any help would be appreciated.
    Two possible solutions:

    1. Create a 4th column in the sheet where everything is. Supposing that A2:C100 houses the data of interest in sheet Data:

    In D2 enter and copy down as far as needed:

    =A2&"-@-"&B2

    where A2 holds a last name and B2 a first name.

    In the sheet,called Main say, where you want to retrieve the associated ORG values from Data, use the following formula:

    =IF(COUNTIF(Data!$D$2:$D$100,A2&"-@-"&B2),INDEX(Data!$C$2:$C$100,MATCH(A2&"-@-"&B2,Data!$D$2:$D$100,0)),"Not found")

    2.

    In Main just use:

    =IF(SUMPRODUCT(ISNUMBER(MATCH(A2&"-@-"&B2,Data!$A$2:$A$100&"-@-"&$Data!B$2:$B$100,0))+0),INDEX(Data!$C$2:$C$100,SUMPRODUCT(MATCH(A2&"-@-"&B2,Data!$A$2:$A$100&"-@-"&$Data!B$2:$B$100,0))),"Not Found")

    Note. Method 1 trades off space against time, therefore faster. Method 2 will incur some performance costs (that's, it will cause recalculation at opening and closing of your WB -- time costs).


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
  •