Mixture of Vlookup and Concatenate

[hardeep.kanwar]

Hi! Experts

I have Data Like Below, Its a Master Sheet

On Daily Basis i have Received Around 100 Data. In which some Data is Already in Mentioned Below Data

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Call date	Name Of The Student	Phone Number	Course Intersted For	Preferred Campus	Category	Final Remarks	Category Campus	Next Follow Up Date	Next Step	Caller Name	Batch	Zone	Type
2	11/03/09	T S S Pavan	4023832487	Post Graduate Diploma In Business Economics (finance)	Hyderabad	WRM	699, Interested To Visit The Hyderabad Campus. 6/11/09 Not Lifting Up The Call *7/11/09 *he is working will come on 14th nov 17/11/09 will come by next week	CLD	11/24/09	Call Again	Deepthi	Matdata	South	Full Time
3	10/30/09	Ananth Ram Kishore N	4027639908	Post Graduate Diploma In Business Economics(marketing)	Hyderabad	WRM	719.5//not At Home,gv Number, Will Revert. 5/11/09 Not Lifting Up The Call 6/11/09 Not Lifting Up The Call 7/11/09 he is degree final year will come in the month of june next year	CLD	01/06/10	Call Again	Deepthi	MAT	South	Full Time
4	09/23/09	Vijender	4064525172	Post Graduate Diploma In Business Economics	Hyderabad	HOT	He Is Not Aware Call Ain The Evening----info Gvn,int In Pg,will Decide,will Visit Hyderabad Campus 30/9/09 This Is His Direct No *9704554660 Not Lifting The Call 1/10/09 Came For Walkin And Took The Details Done Interested In Bio Tech Felid Took The Details Will Get Back To Us By 5th Oct 5/10/09 He Is Not Feeling Well Asked To Call After One Week	CLD	12/10/09	Call Again	Deepthi	Toll Free	South	Full Time
5	09/01/09	Sunil	4065279179	Post Graduate Diploma In Business Economics	Hyderabad	HOT	Will Be Visditing Campus To Fill A Form ,24/9/09 He Went Out Need To Call Him Later *29/9/09 Not Lifting The Call 1/10/09 Some Of His Cousin Lifted The Call *She Took My Number Will Ask Him To Call Us Back 5/10/09 He Need Some More Time As He Is Waiting For Money When Ever He Can Arrange That He Will Come And Join And He Asked Us To Not Call Again And Again	CLD	12/10/09	Call Again	Deepthi	Toll Free	South	Full Time
6	10/31/09	Anoop Kumar Saurav	4065919598	Post Graduate Diploma In Business Economics	Hyderabad	WRM	640.5/ Madhu- Given Info And He Interested As Well But Wud Go For December Cat First/3/11/09 He Was Not Available His Mother Lifted The Call Said Will Pass On The Message/4/11/09 Number Is Currently Unavailable. 5/11/09 he is out of town, will come back after 12/11/09 Switched Off/13/11/09 He is preparing for CAT after giving CAT exam he will decide and get back to us.	CLD	11/30/09	Call Again	Deepthi	Toll Free	South	Full Time
7	10/22/09	R Nithya	4222590901	Post Graduate Diploma In Business Economics (finance)	Chennai	WRM	Not interested this year//735.5	0	12/01/09	Call Again	Ragini	MAT	South	Full Time
8	10/22/09	Sohan	4425290039	Post Graduate Diploma In Business Economics	Chennai	CLD	Puja : Said That Has Called Only For Information Will Decide After Cat .	0	12/24/09	Call Again	Puja	Toll Free	South	Full Time
9	10/22/09	Arun Kannan	4445554508	Post Graduate Diploma In Business Economics	Chennai	HOT	563.5/given Info,interested For The Course-jaya.30.10.09.ringing No Response// 11.03.09 Doing His Final Year B.tech. Will Be Joining Full Time Course,next Year. Asked To Contact In The Month Of May 2010	NRE	05/03/10	Call Again	K.priadharshani	Toll Free	South	Full Time
10	10/22/09	Sreenath N	4842527866	Post Graduate Diploma In Business Economics	Chennai	WRM	491.5/10/22/09-info gvn/wil get bk 2 us after visiting d website.05.11.09.After writing MAT exam.Will decide may try Jan batch.	FWC	12/15/09	Call Again	Vijayalakshmi	MAT	South	Full Time
South Master Open Cases

Say i have Received this Data

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Call date	Name Of The Student	Phone Number	Course Intersted For	Preferred Campus	Category	Final Remarks	Category Campus	Next Follow Up Date	Next Step	Caller Name	Batch	Zone	Type
2	10/22/09	R Nithya	4222590901	Post Graduate Diploma In Business Economics (finance)	Chennai	WRM	Ringing,Want to Discuss with Parents	HOT	11/20/09	Call Again	Ragini	MAT	South	Full Time
Sheet1

Now i want to Update This Data in my Master Sheet

Right Now, I am Using Vlookup function
=VLOOKUP($C2,'South Master Open Cases'!C2:N380,COLUMN(B:B),FALSE)

It works Great

But the Problem is that When i Use this function, The MASTER SHEET Final Remarks (Column G) is Updated with New "REMARKS"

I want to "CONCATENATE" the New Remarks with Previous Remarks

Result Should Be

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Call date	Name Of The Student	Phone Number	Course Intersted For	Preferred Campus	Category	Final Remarks	Category Campus	Next Follow Up Date	Next Step	Caller Name	Batch	Zone	Type
2	10/22/09	R Nithya	4222590901	Post Graduate Diploma In Business Economics (finance)	Chennai	WRM	Not interested this year//735.5,Ringing,Want to Discuss with Parents	HOT	11/20/09	Call Again	Ragini	MAT	South	Full Time
Sheet1

Thanks in Advance

 

[seenfresh]

when u want to concatenate or combine results and values use the "&" symbol for example:

=VLOOKUP($C2,'South Master Open Cases'!C2:N380,COLUMN(B:B),FALSE)&" = My Results"

Or combine two separate VLOOKUP functions together to combine two different results using VLOOKUP.... & VLOOKUP ....

You may need to create an additional sheet and combine two VLOOKUPS from "South Master Open Cases" with "Sheet1"...

Where does your VLOOKUP formula reside?

 

[sanrv1f]

Hi Hardeep,

you might already know that, we can't keep the existing value in a cell while we enter a formula in it, you would need to use a helper column then copy -> paste special, otherwise VBA would be the solution