Vlookup with 2 columns

Thanks:  0
Likes:  0

# Thread: Vlookup with 2 columns

1. ## 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. ## Re: Vlookup with 2 columns

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

4. ## 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. chad1222 - I presume you've noticed the suggestion fom Aladin?

6. ## 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. ## Re: Vlookup with 2 columns

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. ## 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. ## Re: Vlookup with 2 columns

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. ## 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?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•