Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Vlookup with two columns?

This is a discussion on Vlookup with two columns? within the Excel Questions forums, part of the Question Forums category; Possible to do a v-lookup using "and" to look up both first and last names (currently in two columns)? Alternatively, ...

  1. #1
    Board Regular
    Join Date
    Apr 2009
    Posts
    169

    Default Vlookup with two columns?

    Possible to do a v-lookup using "and" to look up both first and last names (currently in two columns)? Alternatively, suppose I could combine first and last... Other ideas?

  2. #2
    Board Regular
    Join Date
    Oct 2011
    Location
    London
    Posts
    78

    Default Re: Vlookup with two columns?

    Hello,

    try to use as the first vlookup argument cells(a,b)&" "&cells(x,y) and it should work

    Regards,

    Alex

  3. #3
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,217

    Default Re: Vlookup with two columns?

    Maybe:
    =VLOOKUP(E1&F1,$A$1:$B$21,2,0)
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,616

    Default Re: Vlookup with two columns?

    Quote Originally Posted by gambils View Post
    Possible to do a v-lookup using "and" to look up both first and last names (currently in two columns)? Alternatively, suppose I could combine first and last... Other ideas?
    Control+shif+enter, not just enter:

    =INDEX(ResultRange,MATCH(1,IF(FirstNameRange=FirstName,IF(LastNameRange=LastName,1)),0))

    Replace the placeholders with the relevant ranges and cells.

    If this is not what you need, try to post the table you have.
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,584

    Default Re: Vlookup with two columns?

    Without the need for Ctrl+Shift+Enter:

    =INDEX(C$1:C$5,MATCH(1,INDEX((A$1:A$5=D1)*(B$1:B$5=E1),),FALSE))
    Microsoft MVP - Excel

  6. #6
    Board Regular cyrilbrd's Avatar
    Join Date
    Feb 2012
    Posts
    2,968

    Default Re: Vlookup with two columns?

    Andrew,

    =INDEX(C$1:C$5,MATCH(1,INDEX((A$1:A$5=D1)*(B$1:B$5=E1),),FALSE))

    the second index isn't going to return anything right? The above mentioned formula will return C$1:C$5 if D1 =A$1:A$5 and E1=B$1:B$5 right?
    V14.4.1
    V14.0.7116.5

  7. #7
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,584

    Default Re: Vlookup with two columns?

    The second INDEX is just to avoid having to confirm with Ctrl+Shift+Enter.
    Microsoft MVP - Excel

  8. #8
    Board Regular cyrilbrd's Avatar
    Join Date
    Feb 2012
    Posts
    2,968

    Default Re: Vlookup with two columns?

    noted.

    in:
    INDEX(ResultRange,MATCH(1,IF(CdtRange1=cdt1,IF(cdtRange2=cdt2,1)),0))

    The ResultRange here is contained in a column.

    Is it possible to extend said INDEX to several columns eg A2:I6 and add to the present MATCH one more condition CdtRange3?

    CdtRange1 being in a Column (B2:B6)
    CdtRange2 being in a Column (A2:A6)
    CdtRange3 being in a Row (C1:I1)

    Thanks to share your opinion.
    V14.4.1
    V14.0.7116.5

  9. #9
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,584

    Default Re: Vlookup with two columns?

    You have 7 columns there, but only 5 rows. Maybe it would help if you posted some sample data with the expected result.
    Microsoft MVP - Excel

  10. #10
    Board Regular cyrilbrd's Avatar
    Join Date
    Feb 2012
    Posts
    2,968

    Default Re: Vlookup with two columns?

    considering a raw data not limited to this range:
    HTML Code:
    genus	Month	conchio1	conchio2	conchio3	conchio4	conchio5	conchio6	conchio7
    maxima	Jan-12	12	13	14	23	34	34	23
    margtfr	Feb-12	11	11	11	12	14	14	12
    fragls	Mar-12	44	56	68	76	8	8	76
    gigas	Apr-12	-6	5	2	2	5	6	8
    crocea	Feb-12	7	9	5	8	9	2	3
    (We do have more occurrence of Genus per month and level of conchio per reading.)

    the output would look like:
    HTML Code:
    level	genus	Jan-12	Feb-12	Mar-12	Apr-12
    conchio1	maxima	12			
    conchio1	margtfr		11		
    conchio1	fragls			44	
    conchio1	gigas				-6
    conchio1	crocea		7		
    conchio2	maxima	13			
    conchio2	margtfr		11		
    conchio2	fragls			56	
    conchio2	gigas				5
    conchio2	crocea		9
    This goes down to conchio7 returning the corresponding values from table 1 if conditions are met.

    Presently using this:

    =IFERROR(IF($A17="a",INDEX($C$2:$C$6,MATCH(1,IF(($B$2:$B$6=C$16),IF(($A$2:$A$6=$B17),1)),0)),
    IF($A17="b",INDEX($D$2:$D$6,MATCH(1,IF(($B$2:$B$6=C$16),IF(($A$2:$A$6=$B17),1)),0)),
    IF($A17="c",INDEX($E$2:$E$6,MATCH(1,IF(($B$2:$B$6=C$16),IF(($A$2:$A$6=$B17),1)),0)),
    IF($A17="d",INDEX($F$2:$F$6,MATCH(1,IF(($B$2:$B$6=C$16),IF(($A$2:$A$6=$B17),1)),0)),
    IF($A17="e",INDEX($G$2:$G$6,MATCH(1,IF(($B$2:$B$6=C$16),IF(($A$2:$A$6=$B17),1)),0)),
    IF($A17="f",INDEX($H$2:$H$6,MATCH(1,IF(($B$2:$B$6=C$16),IF(($A$2:$A$6=$B17),1)),0)),
    IF($A17="g",INDEX($I$2:$I$6,MATCH(1,IF(($B$2:$B$6=C$16),IF(($A$2:$A$6=$B17),1)),0))))))))),"")

    This works but it is also absolutely cumbersome.
    V14.4.1
    V14.0.7116.5

Page 1 of 2 12 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