How to sarch a text in a particular cell with the list of cells

[naveentr]

All,
Stuck in excel, which I am not finding solution. I need to search a text in a cell with a column and if matches, the value in which the adjacent cells should be copied to other cells.

I am attaching two sheets, which I have the data as, and the second one is the data I am in need of.. If the match is not found no changes should be made. Please let me know how can this be done. If this can be done with just a formula instead of VB, that will be helpful. Thanks a lot in advance for your help. Sorry for my poor english...

[TABLE="width: 727"]
<tbody>[TR]
[TD="colspan: 11"]Sheet1[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]City[/TD]
[TD]PIN[/TD]
[TD]Address[/TD]
[TD]Contact #[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Address[/TD]
[TD]Contact #[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD]ABC[/TD]
[TD]34758[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jim[/TD]
[TD]FHDKH[/TD]
[TD]60249[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]EWIRY[/TD]
[TD]21324[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 705"]
<tbody>[TR]
[TD="colspan: 11"]Sheet2[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]City[/TD]
[TD]PIN[/TD]
[TD]Address[/TD]
[TD]Contact #[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Address[/TD]
[TD]Contact #[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD][/TD]
[TD][/TD]
[TD]FHDKH[/TD]
[TD]60249[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD]ABC[/TD]
[TD]34758[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD][/TD]
[TD][/TD]
[TD]FHDKH[/TD]
[TD]60249[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jim[/TD]
[TD]FHDKH[/TD]
[TD]60249[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD][/TD]
[TD][/TD]
[TD]FHDKH[/TD]
[TD]60249[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]EWIRY[/TD]
[TD]21324[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD][/TD]
[TD][/TD]
[TD]FHDKH[/TD]
[TD]60249[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD][/TD]
[TD][/TD]
[TD]EWIRY[/TD]
[TD]21324[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD][/TD]
[TD][/TD]
[TD]EWIRY[/TD]
[TD]21324[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC[/TD]
[TD]34758[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC[/TD]
[TD]34758[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Please help me on this, this saves days of time. Thanks again.

 

[ZAX]

Assuming the Name to search for is in column F:
In Sheet1 where the Address results should be (G2), enter:

=VLOOKUP($F2,Sheet2!$A$2:$E$13,4)

and where the Contact# results should be(H2), enter:

=VLOOKUP($F2,Sheet2!$A$2:$E$13,5)

ZAX

 

[naveentr]

Assuming the Name to search for is in column F:
In Sheet1 where the Address results should be (G2), enter:

=VLOOKUP($F2,Sheet2!$A$2:$E$13,4)

and where the Contact# results should be(H2), enter:

=VLOOKUP($F2,Sheet2!$A$2:$E$13,5)

ZAX

Hi Zak,

Could you explain that in bit detail please? and sheet1 and sheet2 are not just the exactly sheets, its just a name I have give for the initial and desired copy. All the data in the example stays in a single sheet, just are spread across different columns. To be specific image 1 is the data I have now and image 2 is the data in which format I am looking for. Please help!

 

[kevatarvind]

hi something like below Drag Donw D2 And E2 Formula below as you need if not then pls upload ur file any sharing site and paste link here

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Name	City	PIN	Address	Contact #	*	*	*	Name	Address	Contact #
2	Jim	*	*	FHDKH	60249	*	*	*	Bob	ABC	34758
3	Jim	*	*	FHDKH	60249	*	*	*	Jim	FHDKH	60249
4	Jim	*	*	FHDKH	60249	*	*	*	Jack	EWIRY	21324
5	Jim	*	*	FHDKH	60249	*	*	*	*	*	*
6	Jack	*	*	EWIRY	21324	*	*	*	*	*	*
7	Jack	*	*	EWIRY	21324	*	*	*	*	*	*
8	Jane	*	*	*	*	*	*	*	*	*	*
9	Jane	*	*	*	*	*	*	*	*	*	*
10	Jane	*	*	*	*	*	*	*	*	*	*
11	Jane	*	*	*	*	*	*	*	*	*	*
12	Bob	*	*	ABC	34758	*	*	*	*	*	*
13	Bob	*	*	ABC	34758	*	*	*	*	*	*
Sheet1

 

[naveentr]

Thanks, Zax exactly the same I was looking for, only error is, when I drag or copy the formula, even the vlookup table_array is getting incremented. The table_array should always be the same.

 

[kevatarvind]

did you check my post #4 ?

 

[naveentr]

did you check my post #4 ?

Hi Kevatarvind, I did check the post #4.. all works fine except the table_array is getting incremented when I drag and drop the formula. Just making it without incrementing would fix the issue. Please help..

 

[kevatarvind]

A link to the post will be lot useful..

sorry but i have posted in your thread only check below in your thread post #4

 

[ZAX]

Thanks, Zax exactly the same I was looking for, only error is, when I drag or copy the formula, even the vlookup table_array is getting incremented. The table_array should always be the same.

How is that??
I put an apsolute address (Using the $ sign) and shouldn't change at all!
hope you check it again...
And the formula on post #4 won't work if you're working on Excel 2003 then use this in the example in post #4...!

=IF(OR(ISTEXT(VLOOKUP($A2,$I:$K,2,0)),ISNUMBER(VLOOKUP($A2,$I:$K,2,0))),VLOOKUP($A2,$I:$K,2,0),"")

and..

=IF(OR(ISTEXT(VLOOKUP($A2,$I:$K,3,0)),ISNUMBER(VLOOKUP($A2,$I:$K,3,0))),VLOOKUP($A2,$I:$K,3,0),"")

ZAX

 

[markmzz]

Another way:

=IFERROR(INDEX($J$2:$K$4,MATCH($A2,$I$2:$I$4,0),MATCH(B$1,$J$1:$K$1,0)),"")

Markmzz