Vlookup - Combine two columns

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Vlookup - Combine two columns

  1. #1
    Board Regular
    Join Date
    Feb 2009
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup - Combine two columns

    I am trying to do a vlookup by combining two columns in the array to match the lookup value. I know how to this with Index,Match.
    Can this be done with vlookup without using a helper column?
    Sheet1

    A B C
    1 A B 10
    2 B D 20
    3 R T 50
    4
    5 Result
    6 Lookup BD 20


    Excel tables to the web >> Excel Jeanie HTML 4

  2. #2
    Board Regular delaneyjm's Avatar
    Join Date
    Apr 2009
    Location
    44 38' 53" N, 63 34' 20" W
    Posts
    624
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup - Combine two columns

    Have you considered using an INDEX/MATCH array formula?

    Sheet1

    ABC
    1A B 10
    2B D 20
    3R T 50
    4
    5 Result
    6Lookup BD20

    Spreadsheet Formulas
    CellFormula
    C6{=INDEX(C1:C3,MATCH(B6,SUBSTITUTE(A1:A3&B1:B3," ",""),0))}


    Confirm it with Ctrl-Shift-Enter.
    64-bit Windows 7, Excel 2010

  3. #3
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup - Combine two columns

    Maybe:

    Formula in cell C6:

    =INDEX(C1:C3,INDEX(MATCH(B6,A1:A3&B1:B3,0),))

    (Does not require Ctrl + Shift + Enter)

    or

    =INDEX(C1:C3,MATCH(B6,A1:A3&B1:B3,0))

    Requires Ctrl + Shift + Enter.

    AB10
    BD20
    RT50
    Result
    LookupBD20





    Last edited by mgirvin; Feb 27th, 2011 at 01:22 PM.
    Sincerely, Mike Girvin

  4. #4
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup - Combine two columns

    Hey delaneyjm,

    Why are you using the SUBSTITUTE to find a space and replace it with a blank? When I evaluate A1:A3&B1:B3, I see no spaces. Is this a technique that you use just in case the data has spaces - like a safety move?
    Sincerely, Mike Girvin

  5. #5
    Board Regular
    Join Date
    Feb 2009
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup - Combine two columns

    Thanks. So using Index match is my best option.
    So there is no easy way to use vlookup. What I like about vlookup is that I can change the column number very easy if the lookup is on the far left.

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

    Default Re: Vlookup - Combine two columns

    Quote Originally Posted by pto160 View Post
    Thanks. So using Index match is my best option.
    So there is no easy way to use vlookup. What I like about vlookup is that I can change the column number very easy if the lookup is on the far left.
    If you insist on VLOOKUP...

    Control+shift+enter, not just enter:

    =VLOOKUP(B6,CHOOSE({1,2},$A$1:$A$3&$B$1:$B$3,$C$1:$C$3),2,0)
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    Board Regular delaneyjm's Avatar
    Join Date
    Apr 2009
    Location
    44 38' 53" N, 63 34' 20" W
    Posts
    624
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup - Combine two columns

    Hey Mike,

    Seems when I pasted the Excel Jeanie output from the OP, it included some trailing spaces that I didn't pick up on.
    64-bit Windows 7, Excel 2010

  8. #8
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,267
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Vlookup - Combine two columns

    Quote Originally Posted by mgirvin View Post
    =INDEX(C1:C3,INDEX(MATCH(B6,A1:A3&B1:B3,0),))

    (Does not require Ctrl + Shift + Enter)
    There can be such impression that any permutations of INDEX & MATCH functions are working :
    =INDEX(C1:C3,MATCH(B6,INDEX(A1:A3&B1:B3,),0))
    Last edited by ZVI; Feb 27th, 2011 at 06:01 PM.
    Vladimir Zakharov
    Microsoft MVP Excel

  9. #9
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    1,804
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup - Combine two columns

    A question for Aladin.

    that is the second time I have seen you or someone use CHOOSE {1,2}
    in a formula, what does it do please?

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

    Default Re: Vlookup - Combine two columns

    Quote Originally Posted by Dryver14 View Post
    A question for Aladin.

    that is the second time I have seen you or someone use CHOOSE {1,2}
    in a formula, what does it do please?
    CHOOSE collects (the results of) its arguments into an array if the index (below: {1,2}) is an array constant as in:

    CHOOSE({1,2},"a","b")

    ==> {"a","b"}

    However, if the index is a scalar, it will return (the result of) the positionally corresponding argument as in:

    CHOOSE(2,"a","b")

    ==> b
    Assuming too much and qualifying too much are two faces of the same problem.

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
  •  

 

DMCA.com