Solved VLOOKUP for matches on two ws and carrying data

Microsoft Excel 010 - complete package
May 29, 2013 at 15:27:03
Specs: Windows XP
VLOOKUP might not be the right choice here but I'm trying to find corresponding data on two worksheets in the same workbook. Sheet 1 is a report that comes from another department (40,000+ rows) and Sheet 2 is our list of cases (15,000+ rows.)

Currently I'm running a countif to find which of our cases are on Sheet 1 (COUNTIF(Sheet1!E:E,C2)) and if there are any, I need to check who the case is assigned to. Six thousand at last count and I do not want to look these up manually!

I cannot figure out the logic here, how do I pull a specific cell when I do not have a reference? If c2 matches e1203, how do I get q1203 to k2? (Columns C and E are company names, col Q is who it's assigned to company wide and K isassigned per our department.)

Any ideas? I can use the macro wizard and have created some that way but I'm not sure how to do it this time!


See More: VLOOKUP for matches on two ws and carrying data

Report •


#1
May 29, 2013 at 18:29:51
✔ Best Answer
The =VLOOKUP() function should work for you.

The syntax of a =VLOOKUP() is:

=VLOOKUP(ItemToFind , RangeToLookIn , ColumnToPickFrom , Matchtype)

So your Item to Find is C2

This is the part that gets tricky sometimes.
The Range to Look In should be your complete matrix,
so you start at column E
and end at Column Q, since column Q is where the data you want is located.

So your Range to look in should be E:Q

Next is the Column To Pick From, which is column Q,
so we count over, starting at column E, to column Q
which is 13 columns and this gives us our Column To PIck From

Lastly, is Match Type, 1 for Approximate, 0 for Exact
and since we want an exact match we use 0

Put the whole thing together and you get:

=VLOOKUP(C2,Sheet1!E:Q,13,0)

The only thing I would caution against is using the complete column.
It can eat up a lot of time checking 1048576 rows in Excel 2007.

Better to give the formula some limits:

=VLOOKUP(C2,Sheet1!E1:Q40000,13,0)


See how that works.

MIKE

http://www.skeptic.com/


Report •

#2
May 29, 2013 at 18:31:47
If there is only one match, VLOOKUP should work:

=VLOOKUP(C2,E:Q,13,0)

This will look for the value in C2 in the first column of the array E:Q and, if found, return the value in the 13th column of that array. However, I'm not sure that is very efficient to be using the entire range E:Q if your data isn't that long.

If there is more than one match, things get a bit more interesting.

See here:

http://www.get-digital-help.com/200...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
May 30, 2013 at 06:41:19
Thank you both, DerbyDad and Mike, that worked great! And in this case, there will only ever be one match so VLOOKUP(C2,Sheet1!E:Q,13,0) was perfect. I even figured out how to do multiple columns of data using the ctl-shft-entr keys and {13,14}, in place of 13, in the formula.

I appreciate the time, guys! Thanks again.



Report •

Related Solutions

#4
May 30, 2013 at 08:20:48
I haven't tested anytihng, but your array formula doesn't seem to make sense.

Is this what you have?

=VLOOKUP(C2,E:Q,{13,14},0)

There is no "column 14" in the E:Q range so I have no idea what the {13,14} would do for you. As fas as I can tell, it will still only retunr values from Column 13 of the lookup_array, which is Column Q.

What exactly does your formula look like and what does it do?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
May 30, 2013 at 08:28:13
No, I expanded the searc to include S in the array and when it brings back the data it's the Task Owner, Q goes to K, and the Task Owner Manager, R goes to L.

The formula is {=vlookup(c2,Sheet1!E:S,{13,14},0)}


Report •

#6
May 30, 2013 at 09:50:52
I must still be missing something. Keep in mind that I can't see your workbook from where I'm sitting.

First, why do need Column S if you are only pulling data from Q and R?

Second, Do you have the same formula in K as you in L? If you do, I don't see how you are getting the results you say.

Here's what I tried...tell me what you did differently.


Sheet1:

     E  ......  Q       R
5   MyTask    Task1   Owner1

Sheet2:

         C  ....               K                                  L
4       MyTask  {=VLOOKUP(C4,Sheet1!E:S,{13,14},0)} {=VLOOKUP(C4,Sheet1!E:S,{13,14},0)}

Both formulas return Task1 from Sheet1!Q5

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
May 30, 2013 at 10:29:50
First, why do need Column S if you are only pulling data from Q and R?

I included the S because I was toying with the idea of adding more columns. I just didn't remove it when I decided on just R.

Second, Do you have the same formula in K as you in L? If you do, I don't see how you are getting the results you say.

And not exactly. I selected both cells, K2 and L2, then typed in the formula one time and hit ctrl-shift-ebter and the answers popped up in the two cells. It was called an array formula and I found the directions on ozgrid.com. Here's the link. http://www.ozgrid.com/forum/showthr...


Report •

#8
May 30, 2013 at 13:09:43
OK, I now see what you are doing. Basically, the first of the 2 selected cells is using a col_index_num of 13 and the second is using the 14.

Obviously the same thing could be accomplished with 2 separate non-array formulas:

=VLOOKUP(C4,Sheet1!E:S,13,0)
=VLOOKUP(C4,Sheet1!E:S,14,0)

Wait...before you say it - No, you do not have to manually enter the different col_index_num arguments in each cell.

Enter this in Column K and drag it over to Column L:

=VLOOKUP($C$4,Sheet1!$E:$S,COLUMN()+2,0)

Explanation:

The COLUMN() function simply returns the number of the Column in which it resides. Column K is Column 11, so in Column K COLUMN()+2 = 13. Drag that to Column L and you get 14.

The only reason I bring that up is because too many array formulas may slow a workbook down. From the same site where you learned how to create that array formula, you will find this, written by the folks who run the site:

http://www.ozgrid.com/Excel/Arrays.htm

I'm not saying that that one array formula used in 2 cells is going to have any noticeable impact on the efficiency of your workbook. However, if are planning to use that in a long column of cells, you might want to consider a non-array method.

Besides, learning about the COLUMN (and ROW) functions might be useful. It's a great way to increment function arguments just by dragging it from cell to cell.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
May 30, 2013 at 13:18:41
I'm going to have to play with that, DerbyDad, because I might end up using more columns that I originally thought and I was wondering how long I could keep an array like that going! And if I could skip columns, like if I want Q, R, T, V but not S or U, how would I be able to do that. I'll definitely read the link you sent.

Thanks for the information!


Report •

#10
May 30, 2013 at 14:38:23
Once you get into skipping columns in the lookup_array, but want to have your formulas in contiguous cells, there may have to be some manual intervention.

However, the array method will still work. For example, to pull data from Columns Q and S into contiguous cells, e.g. K2:L2, this should work since K2 will use the 13 and L2 will use the 15.

{=VLOOKUP(C4,E:S,{13,15},0)}

I don't mean to scare you away from array formulas. I just want you to be aware of their impact in case you run into issues and are scratching you head about the cause.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
May 30, 2013 at 14:40:13
If you are going to use the ARRAY LOOKUP,
then I would definitely give it parameters.

As it is, with this formula

{=VLOOKUP(C4,Sheet1!E:S,{13,14},0)}

you are searching 2,097,152 cells each time.

and each time you add another column,
you add 1,048,576 cells to the search.

That will slow you down a lot sooner then you think.

I forget how many rows you said you were using
but something like:

{=VLOOKUP(C4,Sheet1!E1:S40000,{13,14},0)}

would be, in my opinion, a lot more desirable.

MIKE

http://www.skeptic.com/


Report •


Ask Question