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.

 

[kevatarvind]

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..

hi the table array how can increment because its lock by $ sign which formula you are using can you post that formula or if still you facing the same problem then pls upload your file any sharing site and paste link here

 

[naveentr]

Spreadsheet Formulas

Cell Formula D2 =IFERROR(VLOOKUP(A2,app,2,0),"") E2 =IFERROR(VLOOKUP(A2,app,3,0),"") <tbody> </tbody>

<tbody>
</tbody>

This worked flawlessly, where app is the Namebox for Col I,J and K combined. Thanks again for all you help. If possible please let me know the equivalent formula for Excel 2003.

 

[kevatarvind]

hi that formula will work also in 2003 but only the IFERROR FUNCTION WILL NOT WORK THAT ONLY FOR IF YOUR FORMULA WILL NOT FOUND ANY MATCH THEN #N/A WILL SHOW SO THATS WHY I USED IFERROR FUNCTION IN VLOOKUP IF ERROR FOUND THEN IT WILL SHOW BLANK CELL

TRY LIKE BELOW IT WILL WORK ALL VERSION BUT WILL GIVE ERROR WHEN VALUE NOT FOUND
=VLOOKUP(A2,app,2,0)

AND DONT WANT ANY ERROR THEN TRY LIKE BELOW IT WILL WORK IN ALL VERSION OF EXCEL

=IF(ISERROR(VLOOKUP(A2,app,2,0)),"",VLOOKUP(A2,app,2,0))

 

[ZAX]

Spreadsheet Formulas

Cell Formula D2 =IFERROR(VLOOKUP(A2,app,2,0),"") E2 =IFERROR(VLOOKUP(A2,app,3,0),"") <tbody> </tbody>

<tbody>
</tbody>

This worked flawlessly, where app is the Namebox for Col I,J and K combined. Thanks again for all you help. If possible please let me know the equivalent formula for Excel 2003.

The answer is in post #9 like

=IF(OR(ISTEXT(VLOOKUP($A2,app,2,0)),ISNUMBER(VLOOKUP($A2,app,2,0))),VLOOKUP($A2,app,2,0),"")

and...

=IF(OR(ISTEXT(VLOOKUP($A2,app,3,0)),ISNUMBER(VLOOKUP($A2,app,3,0))),VLOOKUP($A2,app,3,0),"")

ZAX

 

[markmzz]

This worked flawlessly, where app is the Namebox for Col I,J and K combined. Thanks again for all you help. If possible please let me know the equivalent formula for Excel 2003.

Try this for 2003:

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

Markmzz