Vlookup with 2 columns

chad1222

Board Regular
Joined
Jul 14, 2003
Messages
133
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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?
 
Upvote 0
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?
 
Upvote 0
chad1222 said:
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
 
Upvote 0
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
 
Upvote 0
chad1222 said:
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top