Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Vlookup - Combine two columns

This is a discussion on Vlookup - Combine two columns within the Excel Questions forums, part of the Question Forums category; I am trying to do a vlookup by combining two columns in the array to match the lookup value. I ...

  1. #1
    Board Regular
    Join Date
    Feb 2009
    Posts
    276

    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

    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,134

    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 12:22 PM.
    Sincerely, Mike Girvin

  4. #4
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,134

    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
    276

    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
    65,354

    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

    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
    ZVI
    ZVI is offline
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    2,747

    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 05:01 PM.
    Vladimir Zakharov
    Microsoft MVP Excel

  9. #9
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    1,287

    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
    65,354

    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.

Page 1 of 3 123 LastLast

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