Questions about autofill and vlookup()

[Sissyfoo]

Hi,

2 questions for you today.

Is it possible to compare 2 columns when using vlookup or something similar? For example, the spreadsheet I'm dealing with has 2 columns and I need to pull data from another worksheet into a third column.

Column 1 has company names with a lot of duplicates and column 2 has employee names (some duplicate names but not as many). The other worksheet has 3 columns. Columns 1 and 2 are identical to the previous worksheet and column 3 has e-mail addresses.

Is it possible to compare and match the data in column 1 + 2 and, if they match up with columns 1 + 2 in worksheet 2, then pull data contained on the same row but in the third column?

That's the first question!

The second question is this:

How can I write a script that will fill a range of cells with a formula (e.g. vlookup)? I tried to use autofill but it messes up the ranges and increments the lookup_value by 1 (which is what I want) but at the same time increments the table_array values by 1 as well which is not what I want. How can I get around this?

Sorry for all the questions. Hope someone can help me out. :D

James

 

[barry houdini]

Just to answer the second question...

You need to make the lookup range "absolute" by using $ signs,e.g.

=VLOOKUP(A1,$B$2:$D$3,3,0)

when you drag this formula down (or autofill) the lookup value will change but the range won't

 

[Sissyfoo]

Wewhoo! Thanks for the speedy reply. Now I just need to get an answer to question 1 and my day *might* start to be less crappy.

 

[barry houdini]

Perhaps the easiest way is to concatenate the first two columns in worksheet 2 and then use an INDEX/MATCH formula to get the corresponding email addresses,

here's an example. I show everything on one worksheet to keep it simple but it will work exactly the same on 2 separate sheets as long as you use the correct references.

Book1
	A	B	C	D	E	F	G
1	company 4	bloggs	joebloggs@co4.com		company 4#bloggs
2	business 6	baker	billy.baker@businees6.co.uk		business 6#baker
3	oil company	houdini	bhoudini@oilco.com		oil company#houdini
4	biscuit maker	white	white@biscuitsRus.com		biscuit maker#white
5
6
7
8
9
10	business 6	baker	billy.baker@businees6.co.uk
11	oil company	black	#N/A
12	oil company	houdini	bhoudini@oilco.com
13
Sheet4

Formula in E1 copied down

=A1&"#"&B1

Formula in C10 copied down

=INDEX($C$1:$C$4,MATCH(A10&"#"&B10,$E$1:$E$4,0))

note that if you insert the concatenated column to the left of the email addresses then you could use a VLOOKUP but you may not want to alter your worksheet in this way

 

[Tazguy37]

I hope I'm grasping your intent correctly. Let's start with the Original sheet, with data in Columns A, B, and C.

MainWorkbook.xls
	A	B	C	D
1	Company Name	Employee Name	Email
2	Company A	Employee1	Employee1@Company A.com
3	Company B	Employee2	Employee2@Company B.com
4	Company A	Employee1	Employee1@Company A.com
5	Company C	Employee3	Employee3@Company C.com
6	Company A	Employee1	Employee1@Company A.com
7	Company A	Employee3	Employee3@Company A.com
8	Company C	Employee2	Employee2@Company C.com
9	Company B	Employee1	Employee1@Company B.com
10	Company A	Employee3	Employee3@Company A.com
11	Company B	Employee2	Employee2@Company B.com
12	Company C	Employee2	Employee2@Company C.com
13	Company A	Employee3	Employee3@Company A.com
14	Company C	Employee1	Employee1@Company C.com
15	Company A	Employee3	Employee3@Company A.com
16	Company A	Employee1	Employee1@Company A.com
17	Company B	Employee2	Employee2@Company B.com
18	Company B	Employee2	Employee2@Company B.com
19	Company C	Employee3	Employee3@Company C.com
20	Company B	Employee1	Employee1@Company B.com
21	Company C	Employee2	Employee2@Company C.com
Original

And then the Copy sheet, which has a formula for Column C:

MainWorkbook.xls
	A	B	C	D
1	Company Name	Employee Name
2	Company A	Employee1	Employee1@Company A.com
3	Company B	Employee1
4	Company A	Employee1	Employee1@Company A.com
5	Company C	Employee3	Employee3@Company C.com
6	Company A	Employee1	Employee1@Company A.com
7	Company A	Employee3	Employee3@Company A.com
8	Company C	Employee2	Employee2@Company C.com
9	Company B	Employee1	Employee1@Company B.com
10	Company A	Employee3	Employee3@Company A.com
11	Company B	Employee2	Employee2@Company B.com
12	Company C	Employee2	Employee2@Company C.com
13	Company A	Employee3	Employee3@Company A.com
14	Company C	Employee1	Employee1@Company C.com
15	Company A	Employee3	Employee3@Company A.com
16	Company A	Employee1	Employee1@Company A.com
17	Company B	Employee2	Employee2@Company B.com
18	Company B	Employee2	Employee2@Company B.com
19	Company C	Employee3	Employee3@Company C.com
20	Company B	Employee1	Employee1@Company B.com
21	Company C	Employee2	Employee2@Company C.com
Copy

Formula in Column C is:

=IF(AND(A2=Original!A2,B2=Original!B2),Original!C2,"")

Is that what you're after? When I read it the first time, it seemed like you wanted something a little more complicated. Let me know if that's not what you're looking for, and I'll look at it again.

 

[Sissyfoo]

I think that Barry may have cracked it but I will need to test it on my own spreadsheet to make sure. Unfortunately I've just been given even more work which has an even *higher* priority so I'll have to post tomorrow whether or not it worked. Thanks for the quickness of your replies though. Both were really helpful.

 

[Sissyfoo]

Barry's solution was perfect! I would never have thought of concatenating the two columns. Thanks!!

Thanks also for your help, Tazguy. Appreciate it.