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

Vlookup with 2 columns

This is a discussion on Vlookup with 2 columns within the Excel Questions forums, part of the Question Forums category; Can anyone help me match 2 columns with a Vlookup formula? I have 2 columns i need to match in ...

  1. #1
    Board Regular
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    133

    Default Vlookup with 2 columns

    Can anyone help me match 2 columns with a Vlookup formula? I have 2 columns i need to match in a 3 column array and i need it to return the 3rd column of the array.
    Chad

  2. #2
    Board Regular santeria's Avatar
    Join Date
    Oct 2003
    Location
    Tallahassee
    Posts
    1,844

    Default Re: Vlookup with 2 columns

    Can you give an example of your formula and/or your data structure ?

    "Don't Ruin an Apology with an Excuse"...

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,863

    Default Re: Vlookup with 2 columns


  4. #4
    Board Regular
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    133

    Default Re: Vlookup with 2 columns

    On one side a sheet i have 3 columns -
    the first 2 columns are manually input by selecting the values from drop down boxes. The 3rd column is where i need the vlookup formula.

    On the other side of the sheet is my data that is about 300 rows down. These columns are hidden from the user.

    In the first section where i need the lookup or match or whatever the user selects the value of the first cell then selects the value of the second cell.
    I need the vlookup to match both of those columns values in the data that is in the hidden cells and return the value of whatever is in the 3rd column from the data section. Doesnt seem to hard but the VLOOKUP seems to only be able to match one value and my data has several of the same values.
    Does this make sense?

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

    Default

    chad1222 - I presume you've noticed the suggestion fom Aladin?

  6. #6
    Board Regular
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    133

    Default Re: Vlookup with 2 columns

    Yes, thank you aladdin i finally got something working now.
    Now my question is how can i integrate either an IF statement or an IF(ISNA)) into this formula
    =INDEX(AF1:AF300,MATCH(B6&CHAR(127)&C6,AD1:AD300&CHAR(127)&AE1:AE300,0),0)

    I am actually using VBA to input these into the proper cells and i am using

    Code:
    Range("D6").FormulaArray = "=INDEX(AF1:AF300,MATCH(B6&CHAR(127)&C6,AD1:AD300&CHAR(127)&AE1:AE300,0),0)"
    any ideas?

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,863

    Default Re: Vlookup with 2 columns

    Quote Originally Posted by chad1222
    Yes, thank you aladdin i finally got something working now.
    Now my question is how can i integrate either an IF statement or an IF(ISNA)) into this formula
    =INDEX(AF1:AF300,MATCH(B6&CHAR(127)&C6,AD1:AD300&CHAR(127)&AE1:AE300,0),0)

    I am actually using VBA to input these into the proper cells and i am using

    Code:
    Range("D6").FormulaArray = "=INDEX(AF1:AF300,MATCH(B6&CHAR(127)&C6,AD1:AD300&CHAR(127)&AE1:AE300,0),0)"
    any ideas?
    Trying to suppress #N/A I suppose. I don't know that much VBA, but can't you set a variable to the result of Index/Match formula and test that variable with IsNa... Something like:

    if IsNa(var)

    then Nothing

    else

    var

  8. #8
    Board Regular
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    133

    Default Re: Vlookup with 2 columns

    The reason i am putting the actual forumla in by VBA is because my users sometimes screw the code up. So i have a toolbar that says "Reset code" when this is pushed it deletes all the formulas and reinputs them in the correct cells just as i intended. This button does not get pushed everytime the workbook is opened much less everytime a cell changes. I do not want to have that much code running as each cell is changed that is why i want the formula in the actual cell. Seeing as you do not know VBA just pretend that we are not using it and i am inputing this formula in a cell and i want to suppress the #N/A how can i edit this one line formula to do that and just leave it blank if it does not find a match.

    Chad

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,863

    Default Re: Vlookup with 2 columns

    Quote Originally Posted by chad1222
    The reason i am putting the actual forumla in by VBA is because my users sometimes screw the code up. So i have a toolbar that says "Reset code" when this is pushed it deletes all the formulas and reinputs them in the correct cells just as i intended. This button does not get pushed everytime the workbook is opened much less everytime a cell changes. I do not want to have that much code running as each cell is changed that is why i want the formula in the actual cell. Seeing as you do not know VBA just pretend that we are not using it and i am inputing this formula in a cell and i want to suppress the #N/A how can i edit this one line formula to do that and just leave it blank if it does not find a match.

    Chad
    Chad,

    One of the best methods that you can set up is a 2-cell approach:

    Y2:

    =MATCH(B6&CHAR(127)&C6,AD1:AD300&CHAR(127)&AE1:AE300,0)

    X2:

    =IF(ISNA(Y2),"",INDEX(AF1:AF300,Y2))

    For other methods, see:

    http://www.mrexcel.com/board2/viewtopic.php?t=62102

    V() might be of interest to you if you'd go with VBA.

  10. #10
    Board Regular
    Join Date
    Aug 2011
    Posts
    102

    Default Re: Vlookup with 2 columns

    Hi,

    I think i have a similar issue.

    Currently i have data in this form:

    Task (which is a drop down) Description (which vlookups from the task selection) Activity (either hi or low) and then proposed time.

    The data is in a table in another tab,

    Basically if you select the task the description populates using a vlookup, then the activity is either high or low, i want to then populate the proposed time by looking at the task and then the activity.

    I could do an if statement but it would take ages to write as there are 20 different tasks and every proposed time is different, is there a way to do it?

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